The Read Phenomena Ladder and What Each Isolation Level Still Permits

The SQL standard defines isolation not by listing what a transaction can rely on but by listing what the implementation is allowed to let happen. Read uncommitted permits three named anomalies, read committed permits two, repeatable read permits one, and serializable permits none. This negative framing is deliberate, since a positive list would have to enumerate every guarantee an application might want, but it produces a specification that is both weaker than most implementations and weaker than most readers assume.

The three phenomena in the standard

A dirty read is a read of data written by a transaction that has not committed and may still abort. Formally, T1 writes x, T2 reads x, T1 aborts. T2 has now observed a value that never existed in any committed state, and if T2 acts on it the action is unrecoverable in the sense that no compensating logic can know what the correct value was.

A non-repeatable read is a read of the same item twice within one transaction with a committed write in between. T1 reads x, T2 writes x and commits, T1 reads x again and sees a different value. The transaction’s own view is not stable, so any reasoning that assumes a value stays fixed for the duration of the transaction is invalid.

A phantom is a re-execution of the same predicate with a committed insert in between. T1 reads the set of rows matching WHERE dept = 7, T2 inserts a row with dept = 7 and commits, T1 re-reads and sees one more row. The distinction from a non-repeatable read is that no individual item changed; the set of items qualifying for the predicate changed. This matters because a lock taken on the rows that existed at the first read does not prevent the insertion of a new row.

The mapping from phenomena to levels is a table in the standard: read uncommitted forbids none, read committed forbids dirty reads, repeatable read forbids dirty and non-repeatable reads, serializable forbids all three.

Why “forbids” is not “guarantees”

Reading the table as a guarantee produces a false picture of read committed. The standard’s read committed forbids dirty reads and permits everything else, which technically includes the lost update and write skew. An implementation that satisfies the letter of read committed could permit a transaction to read the same row twice and see two different values.

In practice read committed is usually implemented as statement-level snapshot: each statement sees a snapshot taken at statement start, so a single statement is internally consistent but two statements in the same transaction may see different states. This is stronger than the standard requires and stronger than repeatable read’s prohibition, because it eliminates non-repeatable reads within a statement but not across statements. The gap between the specification and the common implementation is the first reason the ladder is a poor guide.

The second reason is that the standard’s phenomena are defined in terms of single items and simple predicates, and they do not cover anomalies that require reasoning across two items or across two transactions’ write sets.

The anomalies that fall through

Berenson and colleagues documented the cases the standard misses. The most consequential is the lost update: two transactions read the same value and both write a modification based on it. The final value reflects one transaction’s work and discards the other’s. This schedule is not conflict serializable — its precedence graph has a cycle — yet none of the standard’s three phenomena is present, because each item is read once by each transaction and the writes are ordered. A system that forbids exactly the three named phenomena can therefore permit it.

Read skew is the multi-item version of the non-repeatable read: T1 reads account A, T2 transfers from A to B and commits, T1 reads B. Each individual read is repeatable and neither item was read twice, so no named phenomenon occurs, yet the pair of values never coexisted. This is the anomaly that motivates snapshot isolation and is the reason a system can claim repeatable read and still return an inconsistent pair of balances.

Write skew is read skew with writes: two transactions each read a set that the other modifies, and each writes based on what it read. The classic constraint example is a table of doctors on call, with a rule that at least two must be on call at any time. Two transactions each see two doctors on call, each sets one to off call, and the constraint holds at every point either transaction observes yet is violated at commit. Neither transaction wrote what the other read, so no write-write conflict exists and a first-committer-wins validator has nothing to reject.

The pattern behind all three is the same: the standard’s phenomena are defined over items and predicates that a single transaction touches more than once, and these anomalies involve a transaction touching several items once each. Detecting them requires knowing the read set and the write set, not just the access counts.

Snapshot isolation sits between the levels

A system using multi-version concurrency control with snapshot isolation gives every transaction a consistent snapshot of committed data as of its start, plus first-committer-wins on write conflicts. This eliminates dirty reads, non-repeatable reads, and phantoms, which by the standard’s table would place it at serializable. It does not eliminate write skew, so it is not serializable.

Every major implementation that offers a level above read committed offers something in this region. PostgreSQL’s repeatable read is snapshot isolation, and its documentation says so explicitly, noting that the level provides a stricter guarantee than the standard requires while still permitting serialization anomalies. Engines built on undo rather than heap versions expose a serializable level whose conflict detection is finer-grained than a plain write-write check, so the anomaly set differs again. MySQL’s InnoDB repeatable read is snapshot-based for plain reads but takes next-key locks for locking reads, which changes the anomaly set a third time — the mechanism is described in gap locks and next-key locking.

The practical consequence is that a level name does not determine behaviour. Two systems both claiming repeatable read can differ on whether phantoms occur, whether write skew is possible, and whether a read sees the latest committed version or an older one. Applications that depend on isolation must be written against documented behaviour, not against the level name.

-- The write-skew shape: both transactions see two on-call doctors,
-- both retire one, and the invariant fails without any write conflict.
-- T1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call WHERE shift = 'night';  -- 2
UPDATE on_call SET active = false WHERE doctor = 'alice';
COMMIT;
-- T2, concurrently, same snapshot
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM on_call WHERE shift = 'night';  -- 2
UPDATE on_call SET active = false WHERE doctor = 'bob';
COMMIT;

Under snapshot isolation both updates touch different rows, so first-committer-wins finds no conflict, and the invariant that motivated the read is violated after both commit.

Levels as scheduler descriptions

The more useful way to read the ladder is as a description of the scheduler rather than of the application’s guarantees. Read uncommitted implies no read locks are taken. Read committed implies short read locks released at statement end, or a statement-level snapshot. Repeatable read implies long read locks held to commit, or a transaction-level snapshot. Serializable implies either two-phase locking on all reads including predicate ranges, or a snapshot scheme with additional conflict detection.

Each of these is a statement about what the engine does, and each maps to a cost. Long read locks block writers, which is why systems that offer repeatable read via locking lose concurrency. Transaction-level snapshots do not block writers but retain old row versions, which is why they produce the storage pressure described in vacuum horizons and bloat. Serializable via predicate locking adds lock manager entries for ranges that may contain no rows at all.

That mapping is the reason the level names survive despite the definitional problems. A user choosing a level is choosing a scheduler, and the scheduler’s cost profile is what actually constrains the choice. The anomaly list is a rough index into that choice, not a contract.

Choosing against the contract

Three rules follow from the gap between names and behaviour. First, when the application relies on a specific anomaly being absent, test for that anomaly rather than trusting the level name, because the name is a lower bound. Second, when the application performs read-modify-write, prefer an explicit atomic operation or a row lock over reliance on any isolation level below serializable, because the lost update survives even some serializable-looking implementations. Third, when the application maintains an invariant across several rows, either declare it as a database constraint so the engine can enforce it, or use a truly serializable level, since the intermediate levels provide no protection against write skew.

The last rule is the one most often violated in practice, and the violation is invisible until a constraint check reads two rows and the two reads come from different states. The mechanisms that make it visible are the subject of the next several articles.