Transactions & Concurrency
Isolation Levels and Anomalies
Isolation levels are contracts over histories, not a simple speed ladder. Standards name selected phenomena, research exposes additional anomalies, and vendor implementations may prevent more—or different—behaviors than the same label suggests.
- Dirty write and dirty read are distinct.
- Nonrepeatable reads concern one row; phantoms concern a predicate.
- Lost update is a read–modify–write failure.
- Read skew and write skew cross rows.
- Names do not fully specify implementations.
| Anomaly | Minimal history | RC | RR / snapshot-style | Serializable |
|---|---|---|---|---|
| Dirty write | w1(X), w2(X) before c1 | ✓ | ✓ | ✓ |
| Dirty read | w1(X), r2(X) before c1 | ✓ | ✓ | ✓ |
| Nonrepeatable/fuzzy read | r1(X), w2(X)c2, r1(X) | — | usually ✓ | ✓ |
| Phantom | predicate r1(P), insert2(P)c2, r1(P) | — | varies | ✓ |
| Lost update | r1(X=0), r2(X=0), w1(X=1)c1, w2(X=1)c2 | varies | varies | ✓/abort |
| Read skew | r1(X), w2(X,Y)c2, r1(Y) | — | snapshot ✓ | ✓ |
| Write skew | r1(P), r2(P), disjoint w1/w2 | — | — under SI | ✓/abort |
| Serialization anomaly | cycle in dependency graph | — | — | ✓/abort |
Start: inventory.on_hand = 10; invariant on_hand equals prior stock minus all committed sales.
T1 reads 10; T2 reads 10; T1 writes 9 and commits; T2 writes 8 and commits.
Final = 8, but three units were sold, so expected 7: T1’s decrement was lost.The SQL standard’s classic dirty/nonrepeatable/phantom framing is not a complete taxonomy. Berenson et al. show why lock- and history-based definitions matter; modern MVCC systems add snapshot and dependency behavior that cannot be inferred from a label.