Hi Kyle, On Mon, Jan 1, 2024 at 10:02 PM Kyle Kingsbury via discuss <discuss@lists.mariadb.org> 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