Transaction Isolation Levels
Name the transaction isolation levels and the anomaly each one permits.
Answer out loud in under a minute: a definition, one concrete example, and one pitfall or trade-off.
Hints
Hint 1
For each level, describe one interleaving of two transactions that produces a wrong result.
Solution
Show the approach
The short version
- Read uncommitted permits dirty reads of uncommitted data.
- Read committed sees only committed data, but two reads in one transaction may differ (non-repeatable reads).
- Repeatable read fixes a row once read, but new rows matching a predicate can appear (phantoms).
- Snapshot isolation reads a consistent snapshot; it permits write skew, where two transactions each read what the other writes.
- Serializable is equivalent to some serial order and permits none of these, at the cost of more aborts or locking.
Example
Two doctors each check that at least one other doctor is on call and then go off call. Under snapshot isolation both reads succeed and both leave — write skew. Serializable aborts one.
Pitfall
Databases name levels differently from the standard — some call snapshot isolation "repeatable read" — so ask what the engine actually guarantees rather than what the keyword says.