On 12/22/23 10:53, Kristian Nielsen wrote:
I don't think this is based on any specific source. Rather, it's starting from how InnoDB takes read/write locks and thinking through what this means in terms of behaviour:
READ COMMITTED - no read locks, only write locks, new snapshot each statement.
REPEATABLE READ - no read locks, but keep the snapshot (for SELECTs) over the transaction.
SERIALIZABLE - both read and write locks.
Ah, this is a good intuition, and roughly how I understood things too. The thing is that RR's behavior isn't consistent with *any* kind of snapshot behavior I'm familiar with. Like the point of a snapshot is generally that it's not affected by other transactions' writes, but RR "snapshots" *are* modified by other transactions' writes. Even worse, you can wind up with "impossible" snapshots like this: T1: w(x, 1), w(y, 2) T2: ... r(y, 2), r(x, 1) Here, T2's "snapshot" was clearly taken after T1's write of y=2, but did *not* reflect T1's write of x=1. Violates both atomicity and monotonicity! --Kyle