Transaction Isolation Levels

Same schema, same statements, different outcomes — walking a real Postgres up the isolation ladder until an anomaly only SERIALIZABLE can catch.

Concept

Two transactions running at the same time can see each other's changes to varying degrees, controlled entirely by the isolation level. This exercise walks up the ladder (READ COMMITTED → REPEATABLE READ → SERIALIZABLE) and, for each step, produces the exact anomaly the next level exists to prevent, ending with a case — write skew — that only SERIALIZABLE catches.

Provenance

PostgreSQL 16 documentation, Chapter 13 “Concurrency Control”, §13.2 “Transaction Isolation”, and its three subsections. The claim under test, in one line: the same interleaving of the same statements is legal at REPEATABLE READ and illegal at SERIALIZABLE.

§13.2 intro — “In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed.” (Part 1: there are no dirty reads to produce.)

§13.2.1 Read Committed — “In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run.” (Part 2, first half: the snapshot is per-statement, so two identical SELECTs in one transaction can disagree.)

§13.2.2 Repeatable Read — “a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.” And: a repeatable read transaction “cannot modify or lock rows changed by other transactions after the repeatable read transaction began”, so it “will be rolled back with the message” ERROR: could not serialize access due to concurrent update. (Part 2, second half, and the same-row edge case.)

§13.2.3 Serializable — “This level emulates serial transaction execution for all committed transactions; as if transactions had been executed one after another, serially, rather than concurrently… this isolation level works exactly the same as Repeatable Read except that it also monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.” (Part 3.)

The doctors-on-call scenario in Part 3 is not the docs' own example — §13.2.3 uses a mytab(class, value) table where each transaction sums one class and inserts into the other. The doctors framing comes from Kleppmann, Designing Data-Intensive Applications, 2nd ed., Ch. 8. Both are the same shape.

Prerequisites

1. MVCC snapshot visibility — carries most of Parts 1 and 2. A row version is visible to you if the transaction that created it committed before your snapshot was taken and no committed-before-your-snapshot transaction deleted it. “Isolation level” in Postgres is almost entirely a question of when the snapshot is taken (per statement vs. per transaction).
2. Serializable Snapshot Isolation (SSI) — carries Part 3. Postgres's SERIALIZABLE is not two-phase locking; it runs snapshot isolation and additionally tracks read/write dependencies between concurrent transactions, aborting the transaction it identifies as the “pivot” of a dependency cycle no serial order could have produced. This is why the failure surfaces as an error you must retry, not as a block.
3. Write skew — the anomaly Part 3 produces. Two transactions read an overlapping set of rows, then each writes a different row from that set. Neither wrote anything the other read and then re-read, so no per-row check can see the conflict.

Mental model: which isolation level do you actually need?

Setup

Two terminals, side by side. Everything below assumes you have Docker.

docker run --name pg-iso --rm -e POSTGRES_PASSWORD=postgres -d postgres:16

Terminal A:

docker exec -it pg-iso psql -U postgres

Terminal B (a second, independent connection):

docker exec -it pg-iso psql -U postgres

In either session, create the schema once:

CREATE TABLE accounts (id int PRIMARY KEY, balance int NOT NULL);
INSERT INTO accounts VALUES (1, 500), (2, 500);

CREATE TABLE doctors (name text PRIMARY KEY, is_on_call boolean NOT NULL);
INSERT INTO doctors VALUES ('alice', true), ('bob', true);

From here, "A" and "B" refer to the two psql sessions. Watch for the prompt change from postgres=# to postgres=*# — that * means you're inside an open transaction.

Part 1 — dirty reads (there aren't any)

What this part tests: whether one transaction can see another's uncommitted writes at all. Postgres's answer is always no, at every isolation level — there's no real READ UNCOMMITTED in Postgres; it's silently upgraded to READ COMMITTED. This is the floor every level builds on.

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;

SELECT balance FROM accounts WHERE id = 1;

COMMIT;

SELECT balance FROM accounts WHERE id = 1;

Part 2 — non-repeatable reads

What this part tests: whether a committed write from another transaction can change the answer to a query you already ran, within the same still-open transaction. Under READ COMMITTED it can (each statement re-checks "currently committed"); under REPEATABLE READ it can't (one frozen snapshot for the whole transaction).

Reset first — Session A: UPDATE accounts SET balance = 500 WHERE id = 1;

BEGIN; SELECT balance FROM accounts WHERE id = 1;

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;

Now the same scenario at REPEATABLE READ. Reset — Session A: UPDATE accounts SET balance = 500 WHERE id = 1;

BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 1;

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;

Part 3 — write skew (the one REPEATABLE READ misses)

What this part tests: an invariant spanning two different rows, where neither transaction ever writes the row the other one reads or writes. The same-row protection above can't help — there's no single row conflict for Postgres to detect. This is the same shape as a real double-booking bug: two users each check "is this room booked in this slot?" (reading a different, nonexistent row each time), both see "no," and both proceed to insert their own booking row — no row was ever contested, yet the outcome is wrong.

The invariant here: at least one doctor must always be on call. Confirm the reset state first with SELECT * FROM doctors ORDER BY name;:

BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM doctors WHERE is_on_call;

BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM doctors WHERE is_on_call;

UPDATE doctors SET is_on_call = false WHERE name = 'alice'; COMMIT;

UPDATE doctors SET is_on_call = false WHERE name = 'bob'; COMMIT;

SELECT * FROM doctors ORDER BY name;

Reset: UPDATE doctors SET is_on_call = true; (prints UPDATE 2). Now repeat Step 5 exactly, with BEGIN ISOLATION LEVEL SERIALIZABLE in place of BEGIN ISOLATION LEVEL REPEATABLE READ in both sessions.

UPDATE doctors SET is_on_call = false WHERE name = 'bob'; COMMIT;

SELECT * FROM doctors ORDER BY name;

What you should see

A progression where each isolation level silently permits exactly the anomaly the next level up is designed to prevent — dirty reads never happen in Postgres at all, non-repeatable reads are stopped by REPEATABLE READ, same-row lost updates are also stopped by REPEATABLE READ (could not serialize access due to concurrent update), and write skew is the one anomaly that survives all the way up to REPEATABLE READ — precisely because it never touches a shared row — and is only caught by SERIALIZABLE, via an explicit serialization-failure error rather than silent corruption.

Where each Part goes deeper

This exercise is the ladder — one pass over every rung, so you can see the progression. Each rung has a ddia/ch08 exercise that stops there and takes it apart, reached from Kleppmann's framing rather than the manual's:

And one rung above the top of this ladder: serializable-not-linearizable. SERIALIZABLE ends the progression here, which makes it easy to read as "the level where nothing can go wrong." It isn't. A SERIALIZABLE transaction will happily return a stale value and commit clean, with no 40001 — because serializability constrains the outcome to match some serial order, not when each operation takes effect. That second property is linearizability, and this ladder has no rung for it.

Why

READ COMMITTED re-reads the "currently committed" view on every statement. REPEATABLE READ fixes a single snapshot at the start of the transaction (Postgres implements it via MVCC snapshot isolation, not literal locking) and additionally refuses to let you update a row that's moved underneath you. But snapshot isolation is fundamentally a per-row guarantee: it protects rows you've read or written from changing underneath you, and says nothing about a decision you made based on a read of one row being invalidated by a write to a completely different row. That's write skew, and it's a structural gap in snapshot isolation, not a bug — see the mental model table above for why SERIALIZABLE (or a targeted alternative like a unique constraint or SELECT ... FOR UPDATE) is the right tool specifically when an invariant spans rows that snapshot isolation has no reason to connect.

The mechanism that closes the gap: under SERIALIZABLE, Postgres records a predicate lock (SIREAD) for everything a transaction reads — here, the rows scanned by SELECT count(*) FROM doctors WHERE is_on_call. When another transaction writes a row covered by such a lock, an edge is added to a dependency graph. A transaction with both an incoming and an outgoing "dangerous" edge is a pivot, and a pivot is exactly the signature of a cycle with no serial equivalent; Postgres cancels it. Note what makes this possible: SIREAD locks cover rows read, not just rows written — precisely the information snapshot isolation throws away.

The boundary condition where the effect vanishes: make the two transactions contend on the same row and REPEATABLE READ already catches it (Part 2's edge case), so SERIALIZABLE buys you nothing extra. Equally, run them so they don't overlap in time (A commits before B's BEGIN) and there is no anomaly at any level, because the interleaving with no serial equivalent never happens. The anomaly needs both concurrency and disjoint write sets over an overlapping read set.

Go deeper

• Try Part 3 with three doctors and different commit orderings — does it matter who commits first?
• Reorder Part 3's serializable run: have B issue its UPDATE before A commits. B's UPDATE now returns UPDATE 1 and the failure moves to B's COMMIT, with DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt. Same cycle, same victim, different moment of detection — proof that "commit-time abort" is a property of the interleaving, not of SERIALIZABLE.
• Model the booking-system scenario directly: create a bookings(room_id int, slot int, PRIMARY KEY (room_id, slot)) table (the primary key doubles as the uniqueness constraint), have two sessions both check for an existing booking in a slot, then both INSERT a new one for the same (room_id, slot) under plain READ COMMITTED. Compare: does the constraint alone prevent double-booking without needing SERIALIZABLE at all?
• While a transaction is open, inspect pg_stat_activity and pg_locks from a third session to see the open transaction and (in Part 1) the row lock A holds. Under SERIALIZABLE, pg_locks also shows SIReadLock entries — the predicate locks described above.
• Turn on \set VERBOSITY verbose in psql before reproducing either error; it prints the SQLSTATE and the C source location that raised it (ExecUpdate, nodeModifyTable.c vs. OnConflict_CheckForSerializationFailure, predicate.c).

Sources: PostgreSQL 16 Docs §13.2: Transaction Isolation (§13.2.3 carries a different write-skew example, mytab(class, value), worth reproducing alongside this one) · Kleppmann, Designing Data-Intensive Applications, 2nd ed., Ch. 8 ("Write Skew and Phantoms"), whose doctors-on-call scenario this is — the exercises mined from that chapter are mapped rung-by-rung under "Where each Part goes deeper" above

Cleanup

The container was started with --rm, so stopping it also removes it; no volumes or networks were created.

docker stop pg-iso
docker ps -a --filter name=pg-iso   # should print only the header row