Hi Kyle, On Mon, Jan 1, 2024 at 10:02 PM Kyle Kingsbury via discuss <[email protected]> wrote: > I do want to emphasize that doing this in the primary-key workloads I've > discussed so far violates both Snapshot Isolation *and* Repeatable Read. It's > not how these consistency models work in abstract, or how other major > databases work in practice. Whether MySQL users *want* this weird behavior > is, well... I guess that's up to y'all, haha. ;-) > > 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 > > >From a usability point of view, what should the result of transaction T2 be, > assuming "REPEATABLE READ" mode? > > If you interpret MySQL REPEATABLE READ as SI, T2 must observe (2, 2). Its > snapshot must have been taken before T3 committed, since it performed a read > before T3 started. Its update affects nothing, it selects (2,2), and commits > successfully.
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. This example reminds me of the "semi-consistent read" that we implemented a long time ago in order to fix https://bugs.mysql.com/bug.php?id=3300. If your example behaves as you claimed, it would seem to be inconsistent with the intention of that fix. (If we defer the COMMIT of T3 until after the UPDATE in T2, then I guess the UPDATE should do nothing.) I will have to dig deeper into this. Thank you for the simple example. With secondary indexes, it gets trickier, because InnoDB does not have a concept of "table row locks". Instead, it has a concept of "index record locks", and therefore some locking behaviour may depend on the choice of indexes that are being accessed by locking reads. Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
