3 / 9 ACID, Isolation Levels & MVCC

Both Doctors Walked

An on-call table must always have at least one doctor flagged on_call. Two doctors click off-call at the same moment. Each transaction runs at PostgreSQL REPEATABLE READ, reads SELECT count(*) FROM doctors WHERE on_call = true and sees 2, then updates its own row to false. Both commit with no error, and the shift ends with zero doctors on call. What went wrong?

A

A non-repeatable read: the count moved underneath each transaction because repeatable read stabilises single rows but not aggregates

B

Write skew: each wrote a different row, so snapshot isolation saw no write conflict despite each invalidating the other's read

C

A dirty read: each transaction observed the other's uncommitted update to the on_call column and treated it as committed

D

A lost update: the second commit overwrote the first row version, so only one of the two off-call changes actually survived