On 1/2/24 00:07, Marko Mäkelä wrote:
Consider a
simple example like this, where two transactions T2 and T3 update the same
row in parallel:
T1: BEGIN
T1: INSERT INTO t1 VALUES (1,1), (2,2), (3,3)
T1: COMMIT
T2: BEGIN
T2: SELECT * FROM t1 # S1: (2,2) ?
T3: BEGIN
T3: UPDATE t1 SET b=12 where a=2
T3: COMMIT
T2: SELECT * FROM t1 WHERE a=2 # S2: (2,2) ? (2,12) ?
T2: UPDATE t1 SET b=-1 WHERE b>=10 # U1: update the row?
T2: SELECT * FROM t1 WHERE a=2 # S3: (2,2) ? (2,-1) ?
T2: COMMIT
>
If the WHERE clause in T2 had been b=2 instead of b>=10, what is the
expected outcome? Does MySQL or MariaDB even have an error code for
that? I think that this would be a perfect use of https://dbfiddle.uk.
Under SI, T2 always operates on the snapshot it took before T3 began,
and observes none of T3's effects. T2 updates row 2 to (2, -1), and
aborts. Why? First-committer-wins identifies that a new version of row 2
was committed in the interval between T2's snapshot and commit
timestamp. T2 must abort to prevent lost update.
Under RR, I thiiiink there's the same freedom of choice--T2 is executing
a predicate write, and those are (broadly speaking) unconstrained in RR.
There might be something here about predicate wr/ww dependencies vs
predicate rw anti-dependencies; I'd have to stare at this one for a
while. Predicates are *weird*.
Just to take predicate confusion out of the mix, here's the same thing
with primary-key access (assuming a is the primary key).
T1: BEGIN
T1: INSERT INTO t1 VALUES (1,1), (2,2), (3,3)
T1: COMMIT
T2: BEGIN
T2: SELECT * FROM t1 where a=2; # S1: (2,2)
T3: BEGIN
T3: UPDATE t1 SET b=12 where a=2
T3: COMMIT
T2: SELECT * FROM t1 WHERE a=2 # S2: (2,2)
T2: UPDATE t1 SET b=-1 WHERE a=2 # U1: Either update or abort
T2: SELECT * FROM t1 WHERE a=2 # S3: (2,-1)
T2: COMMIT # Must abort, if it hasn't already
This is what would happen under both snapshot isolation and repeatable
read. Under RR, you have a choice of aborting as soon as T2 updates, or
at commit time, but ultimately T2 *must not commit*. Doing so would
cause lost update.
Here's what MySQL does:
T2: SELECT * FROM t1 WHERE a=2 # S2: (2,2)
T2: UPDATE t1 SET b=-1 WHERE a=2 # U1: Updates row to (2, -1)
T2: SELECT * FROM t1 WHERE a=2 # S3: (2,-1)
T2: COMMIT # Commits
This is the canonical definition of lost update--it's forbidden both by
RR and SI, but RR lets it happen!
--Kyle
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
