9 / 9 ACID, Isolation Levels & MVCC

No Serial Order Exists

mytab starts with four rows:

class value
1 10
1 20
2 100
2 200

Both transactions run at SERIALIZABLE.

time Transaction A Transaction B
t1 SELECT sum(value) FROM mytab WHERE class = 1; returns 30
t2 SELECT sum(value) FROM mytab WHERE class = 2; returns 300
t3 INSERT INTO mytab VALUES (2, 30);
t4 INSERT INTO mytab VALUES (1, 300);
t5 COMMIT;
t6 COMMIT;

What happens at t5 and t6, and would REPEATABLE READ have caught the same problem?

A

Both are rolled back. A read/write dependency cycle gives the engine no basis for preferring either transaction, so every participant aborts.

B

One commits, the other rolls back with could not serialize access due to read/write dependencies among transactions. REPEATABLE READ would have allowed both.

C

Both commit. Each read a disjoint set of rows and each inserted a brand new row, so there is no conflict for the engine to detect at any isolation level.

D

A commits and B is rolled back with could not serialize access due to concurrent update, which is the same error REPEATABLE READ raises for this pattern.