Isolation Levels and Anomalies¶

The problem isolation solves¶

Two transactions running at the same time interleave their reads and writes. Some interleavings produce results that no serial order of the two transactions could have produced — anomalies. The strongest guarantee, serializability, forbids all of them. Weaker isolation levels allow some anomalies in exchange for less blocking and fewer aborts.

Every anomaly on this page was reproduced on PostgreSQL 16.2 with two sessions (A and B) whose statements were interleaved in the order shown.

The anomalies¶

Dirty read¶

T1 reads data written by T2 that T2 has not committed. If T2 aborts, T1 acted on data that never existed. PostgreSQL never allows dirty reads, even at READ UNCOMMITTED (it is treated as Read Committed); MySQL/InnoDB and SQL Server do allow them at Read Uncommitted.

Non-repeatable read¶

T1 reads a row twice; between the reads, T2 commits a change to it.

A: SELECT balance FROM accounts WHERE id = 1;     -- 100
B: UPDATE accounts SET balance = 50 WHERE id = 1; COMMIT;
A: SELECT balance FROM accounts WHERE id = 1;     -- ?

Level	A's second read (measured)
Read Committed	50 — each statement sees the latest committed data
Repeatable Read	100 — the transaction keeps its first snapshot

A report that sums several tables in separate statements under Read Committed can therefore combine numbers from different moments and produce totals that never existed.

Phantom¶

T1 runs a query with a condition (WHERE status = 'pending'); T2 inserts or deletes rows matching it; T1 re-runs the query and sees a different set of rows. Under PostgreSQL's Repeatable Read, the snapshot hides new rows, so phantoms do not appear on re-reads. In the ANSI standard's definition, Repeatable Read only promises stable existing rows, and lock-based databases need predicate or range locks (InnoDB's gap and next-key locks) to prevent phantoms.

Lost update¶

Two transactions read the same value, compute a new value in the application, and both write.

A: SELECT balance → 100             B: SELECT balance → 100
A: UPDATE ... SET balance = 110
A: COMMIT
                                    B: UPDATE ... SET balance = 110
                                    B: COMMIT

Measured, expecting 100 + 10 + 10 = 120:

Level	Outcome
Read Committed	Both commit; final balance 110. One update silently lost
Repeatable Read	B's `UPDATE` fails: `ERROR 40001 could not serialize access due to concurrent update`; B must retry

Three correct ways to write it at Read Committed:

-- 1. Let the database do the arithmetic: the UPDATE re-reads the latest committed row.
UPDATE accounts SET balance = balance + 10 WHERE id = 1;

-- 2. Lock the row when reading it (pessimistic).
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = :new WHERE id = 1;

-- 3. Compare-and-set with a version column (optimistic).
UPDATE accounts SET balance = :new, version = version + 1
WHERE id = 1 AND version = :version_read;      -- 0 rows updated → retry

Measured for form 1: when A had updated the row and not committed, B's UPDATE ... balance + 10 blocked on A's row lock; after A committed, B re-read the row and applied its increment: final 120.

Write skew¶

Two transactions read an overlapping set of rows, then each writes a different row based on what it read. No row is written by both, so no write conflict is detected, but the combination breaks an invariant.

sequenceDiagram participant A as A (alice) participant DB participant B as B (bob) A->>DB: SELECT count(*) WHERE on_call → 2 B->>DB: SELECT count(*) WHERE on_call → 2 Note over A,B: each sees "2 on call: safe for me to leave" A->>DB: UPDATE doctors SET on_call=false WHERE name='alice' B->>DB: UPDATE doctors SET on_call=false WHERE name='bob' A->>DB: COMMIT B->>DB: COMMIT

Measured, with the invariant "at least one doctor on call":

Level	Outcome
Repeatable Read	Both commit. Doctors on call: 0. Invariant broken
Serializable	A commits; B's commit fails with a serialization failure. Doctors on call: 1

Write skew is the anomaly that separates snapshot isolation from serializability. Other real cases: two users registering the same username when uniqueness is checked by a SELECT (without a unique constraint), double-booking a meeting room, overspending across two accounts that share a limit.

Read skew and other anomalies¶

Read skew: T1 reads X, then T2 updates X and Y and commits, then T1 reads Y — seeing old X with new Y (a non-repeatable read across two rows). Snapshot-based levels prevent it. The full taxonomy, including anomalies the ANSI SQL standard failed to name, is in Berenson et al., "A Critique of ANSI SQL Isolation Levels" (1995) and Adya's thesis (1999).

The levels, as PostgreSQL implements them¶

Level	How it works	Prevents	Allows
Read Committed (default)	New snapshot for every statement; `UPDATE`/`DELETE` wait for row locks and re-check the latest committed version	Dirty reads	Non-repeatable reads, phantoms, lost updates (read-then-write in app), write skew
Repeatable Read	One snapshot for the whole transaction (snapshot isolation); updating a row changed by a concurrent committed transaction fails with `40001`	+ non-repeatable reads, phantoms, lost updates	Write skew
Serializable	Snapshot isolation + SSI: tracks read/write dependencies and aborts a transaction when they form a dangerous structure	All anomalies	— (but transactions can fail and must be retried)

How Serializable Snapshot Isolation (SSI) works¶

SSI (Cahill et al., 2008; in PostgreSQL since 9.1) observes that every non-serializable execution under snapshot isolation contains a pattern of two consecutive read-write anti-dependencies (rw-conflicts): T1 reads something T2 later writes, and T2 reads something T3 writes, with the transactions overlapping in time. PostgreSQL records what each serializable transaction reads using SIRead locks — which never block anyone, they are just markers — and on a dangerous pattern aborts one participant.

In the doctors example: A read the rows B wrote (rw-conflict A → B), and B read the rows A wrote (B → A); the cycle is detected and B is aborted at commit. Because it tracks reads at page or relation granularity when row-level tracking gets too large, SSI can produce false positives — aborts that weren't strictly necessary — so serializable applications must always be prepared to retry.

MySQL/InnoDB is different¶

InnoDB's default is Repeatable Read, but it behaves differently from PostgreSQL's: plain SELECTs read from a snapshot, while UPDATE, DELETE, and locking reads (SELECT ... FOR UPDATE) operate on the latest committed data and take next-key locks (row + the gap before it) to block phantoms. A transaction can therefore see one version of a row in a SELECT and update a different one. Its Serializable level converts all plain SELECTs into locking reads: pessimistic, more blocking, no SSI-style aborts.

Choosing a level¶

Workload	Level	Why
Typical web request: a few statements, single-row updates written as `SET x = x + ...`	Read Committed	Cheap; the database's own row locking handles single-row updates correctly
Reports and exports that must be internally consistent	Repeatable Read (read-only)	One snapshot for all statements; read-only transactions never fail
Read-modify-write logic in the application	Repeatable Read, or `SELECT ... FOR UPDATE` under Read Committed	Prevents lost updates
Invariants across multiple rows (limits, uniqueness without constraints, scheduling)	Serializable with retries, or explicit locks / constraints	Only these prevent write skew

Serializable in PostgreSQL usually costs much less than people expect in throughput — the cost is mostly the retries under contention, which must be implemented: catch SQLSTATE 40001 (serialization failure) and 40P01 (deadlock), and re-run the whole transaction from the start.

Common pitfall¶

Assuming Repeatable Read prevents write skew because it prevents non-repeatable reads and phantoms. Write skew involves transactions that read the same rows but write different ones, so none of Repeatable Read's checks fire — measured above, both doctors' commits succeeded and the invariant broke. Tests rarely catch it, because the two transactions must overlap within milliseconds; production traffic eventually provides that overlap. Any invariant spanning multiple rows needs Serializable, an explicit lock on what was read (SELECT ... FOR UPDATE on the rows that decide the outcome), or a constraint that the database can enforce itself.