What transactional isolation forbids
Why is the isolation-level taxonomy broken?
Why the taxonomy is broken, precisely
Berenson et al. (1995) is worth reading in full. The load-bearing failures of the ANSI scale are:
- The levels are defined by prohibited phenomena, stated so ambiguously that the literal reading does not exclude what was intended.
- Dirty write is omitted entirely, although it must be excluded at every level.
- Snapshot isolation is not expressible on the scale at all. It prevents all three ANSI anomalies and is still not serializable.
- The levels stop being totally ordered the moment SI exists.
The structural cause is a general lesson: the standard was written against one mechanism family (locks) and then had to name the products of another (multiversioning). Vendors attached SI to whichever existing name was least embarrassing. PostgreSQL chose Repeatable Read. Oracle chose Serializable. The confusion in the field comes from that one decision, not from anybody's misunderstanding.
The directive is therefore:
- Use Adya's phenomena, never the ANSI ones.
- Read the vendor's own documentation for what is actually prevented.
- Treat a level name in a configuration file as the starting point for a three-transaction test, not as a guarantee.
Synthesis of this article
What this article forbids
- A level that prevents write skew without tracking reads. SI's ceiling is structural, not an implementation gap: write-set validation cannot see rw edges.
- A total order over the standard level names.
- Phantom prevention without either a snapshot or predicate/range locks.
- Noticing an anomaly from inside the transaction that suffers it. That transaction sees only legal values.
- Lost-update prevention at RC without commutativity, a lock or a version check.
- Recency as a consequence of serializability.
- A level name meaning the same thing in two engines.
Escapes and what each costs
| Escape | Buys | Pays |
|---|---|---|
| Commute the operation | Level-independence | Only exists where an algebra does |
| Single-row constraint | Free enforcement at any level | The invariant must fit in one row |
| Shrink the read-to-write window | up to 10⁴× (typically 10³×) fewer lost updates | Logic must move into the engine |
Cursor stability / FOR UPDATE | Lost-update immunity | Blocking, and deadlock risk |
| Version check (CAS) | Lost-update immunity, no blocking | A retry loop, and something must own it |
| Snapshot isolation | Non-blocking readers, no read tracking | Write skew, and an analysis burden |
| Materialize the conflict | Write-skew immunity under SI | False conflicts, and it silently rots |
| Serializable (SSI) | Compositional correctness | False aborts, read-tracking memory |
| Serializable (2PL) | Compositional correctness | Blocking, W grows, and conflict grows as W² |
(The last row: with 2PL, a longer wait makes transactions hold locks longer, which raises the chance that others collide with them. Conflict grows roughly with the square of the hold time.)