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