The dials between speed and safety. Four levels, three named anomalies — and a matrix, computed live, of which level blocks which. Each level is a lock discipline; feed it the schedule that would exhibit an anomaly and watch the anomaly either slip through or hit a wall. Down the center the schedule goes in, the engine runs it, the verdict comes out. The blue team builds and defends; the red team attacks.
source Berenson, Bernstein, Gray, Melton, O'Neil & O'Neil, A Critique of ANSI SQL Isolation Levels, ACM SIGMOD 1995, pp.1–10 — doi.org/10.1145/223784.223785. Rendered, not quoted.
A level is not a promise; it is a lock discipline, and the matrix falls out of it. Write-locks are long (held to end-of-transaction) at every level. What changes is the duration of read-locks and predicate-locks:
| level | read-lock | predicate-lock |
|---|---|---|
| READ UNCOMMITTED | none | none |
| READ COMMITTED | short | short |
| REPEATABLE READ | long | short |
| SERIALIZABLE | long | long |
A short lock is released the instant the read finishes; a long lock is held until commit, so it blocks any writer for the whole transaction. Each extra long lock kills exactly one more anomaly. Nothing here is asserted — the panel runs the schedules under these rules.
Below this sphere sits the-snapshot-isolation — a multiversion guarantee that blocks all three ANSI anomalies without read-locks at all. AMBER yet it is not on this linear ladder: it permits write skew, an anomaly none of these three name.
Above sits full the-serializability — the result equivalent to some serial order, the top of the tradeoff every application chooses: correctness against concurrency. Each sphere is the next one's premise.
The blue team's live check: re-run every schedule at every level (12 cells) and confirm the ANSI claim matches what the simulator actually does. If red over-states a level, the mismatched cell shows here and the badge turns red.
Three histories, each hand-built to exhibit one anomaly. w=write, r=read, c=commit, a=abort; subscripts are transactions.
| anomaly | history |
|---|---|
| dirty read | w1[x] r2[x] a1 |
| non-repeatable | r1[x] w2[x] c2 r1[x] |
| phantom | r1[P] insert2[y∈P] c2 r1[P] |
The dirty read reads a value T1 later aborts. The non-repeatable read reads x twice across T2's committed update. The phantom re-runs a predicate P after T2 inserts a row that matches it. Feed one into the engine below.
Every step is executed by a lock manager. A read wanting a lock that conflicts with a held one blocks — and a blocked interleaving means the anomaly cannot happen at that level.
The full matrix, recomputed from these runs on every change:
What the machine proves: which level blocks (green) and which allows (dim) each anomaly — every cell a live schedule run, not a lookup. Counts blocked: RU 0, RC 1, RR 2, SER 3 — a strict chain.
The blue team's witness (left) confirms these cells against the ANSI claim; the red team (right) tries to make the claim lie.
Worse for any tidy ladder: snapshot isolation blocks all three anomalies yet is not serializable — it permits write skew (two txns each read a constraint, each writes, together they break it). So the four levels are a useful spine, not a total order over all real systems.
"SERIALIZABLE means transactions run one at a time." Cut. It means the outcome is equivalent to some serial order — concurrency is preserved; only conflicting overlaps are blocked.
"REPEATABLE READ prevents phantoms." Cut. ANSI RR still allows phantoms; only SERIALIZABLE blocks them — computed live in the machine (RR/phantom = allowed).
"The four levels are a complete taxonomy of isolation." Kept, corrected. They are a spine; snapshot isolation and cursor stability sit between and beside them, as this very paper shows.
The red team's move: over-state READ COMMITTED by one level — claim it prevents non-repeatable reads. The witness (window 7) still runs the non-repeatable schedule at RC, sees it allowed, and catches the lie.
Claim RC blocks non-repeatable reads and the RC/non-repeatable cell of the claim no longer matches the schedule — which allows it. The witness recomputes, disagrees, and turns red. Nothing is faked; the attack is real and it is caught.