Isolation Levels & Read Anomalies

Concrete Example

All three classic read anomalies share one shape: a reader sees something wrong because a concurrent writer is in flight. The difference is what goes wrong.

Dirty read — reading uncommitted data. The reader sees a value the writer might still throw away:

T1: UPDATE accounts SET balance = 0 WHERE id = 1;   -- not committed
T2: SELECT balance FROM accounts WHERE id = 1;       -- reads 0  ← DIRTY
T1: ROLLBACK;                                        -- that 0 never existed

Non-repeatable read — the same row changes value if re-read. Here the other transaction committed, so the data is genuine; the problem is that one transaction got two answers to one question:

T1: SELECT balance FROM accounts WHERE id = 1;       -- reads 100
T2: UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1;       -- reads 50  ← NON-REPEATABLE

Phantom read — the same range query returns a different set of rows, because a concurrent transaction INSERTed (or DELETEd) rows matching the predicate:

T1: SELECT count(*) FROM accounts WHERE balance > 100;   -- returns 3
T2: INSERT INTO accounts (balance) VALUES (500); COMMIT;
T1: SELECT count(*) FROM accounts WHERE balance > 100;   -- returns 4  ← PHANTOM

The precise distinction matters because the two require different defenses. A non-repeatable read is an existing row you already read changing (UPDATE/DELETE of a specific row); locking rows you've read is cheap row-level locking. A phantom is the set membership changing (INSERT/DELETE); preventing it needs predicate / range / gap locking — you must lock a condition, because you cannot lock a row that does not exist yet.

The fourth, subtlest anomaly is the serialization anomaly, of which write skew is the textbook example. Two transactions read an overlapping set, each acts safely on its own snapshot, both commit, and the combination breaks an invariant neither broke alone:

-- Invariant: COUNT(on_call) >= 1.   Alice & Bob both on call.
T1 (Alice): SELECT count(*) FROM doctors WHERE on_call;  -- sees 2, "safe to leave"
T2 (Bob):   SELECT count(*) FROM doctors WHERE on_call;  -- sees 2, "safe to leave"
T1: UPDATE doctors SET on_call = false WHERE name = 'Alice'; COMMIT;
T2: UPDATE doctors SET on_call = false WHERE name = 'Bob';   COMMIT;
-- Zero doctors on call. Each transaction was individually fine.

Crucially the two transactions write different rows, so simple write-write conflict detection misses it entirely. No dirty, non-repeatable, or phantom read occurred — yet the result corresponds to no serial ordering.

Visual Model

Picture a photographer handling a moving crowd. Read Uncommitted watches the live scene — people half-out-of-frame, some who will walk away (rolled back). Read Committed takes a new photo per shot: each is of settled people, but two photos seconds apart differ. Repeatable Read takes one photo at the start and works from that print — people never move or multiply, but the real room changed, so filing a change from a stale print may be rejected. Serializable uses that single photo plus a referee who checks, after everyone files, whether all the edits could have happened in some sequential order; if two people assumed incompatible worlds, one filing is torn up.

The whole topic is one table: anomalies across the top, levels down the side, and each level forbids one more column than the last. The heatmap below shows it twice over — green where an anomaly is prevented, red where it is allowed — with annotations where Postgres is stronger than the ANSI standard requires (the standard is a floor, not an exact spec).

Two reads of the same table: the ANSI standard stops at the first three columns (the staircase), while Postgres adds the fourth — the serialization anomaly — and exceeds the spec by also killing phantoms at Repeatable Read. Postgres also has no real Read Uncommitted: there is no machinery that could ever show uncommitted data, so that row collapses into Read Committed.

Underneath the table, the mechanical difference between the two most-used levels is purely when the MVCC snapshot is taken. Step through two SELECTs issued inside one transaction, once under Read Committed and once under Repeatable Read, and watch a concurrent commit slip through one but not the other.