Same schema, same statements, different outcomes — walking a real Postgres up the isolation ladder until an anomaly only SERIALIZABLE can catch.
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.
| Level | What it actually does | Reach for it when… |
|---|---|---|
| READ COMMITTED (Postgres default) |
Every statement sees whatever's currently committed. No snapshot spans the whole transaction — two SELECTs five seconds apart in the same transaction can see different data if something else committed in between. |
Most ordinary request handling. Cheapest option, fewest surprises to code around — though the most surprises to reason about, since "the same query might return different things" is easy to forget. |
| REPEATABLE READ | A single snapshot is taken at the transaction's first statement and held for its entire duration. Postgres also detects and rejects any attempt to UPDATE/DELETE a row that a concurrent transaction already committed a change to — a serialization error on that statement, not a silent overwrite. |
Anything needing a consistent multi-statement view: a report/export that must reflect one instant despite running several queries, a total computed across tables that mustn't mix pre/post-update state. Does not protect an invariant spanning two different rows — see write skew below. |
| SERIALIZABLE | Everything above, plus: Postgres tracks read/write dependencies between concurrent transactions and aborts one side of any cycle that couldn't have arisen from some one-at-a-time execution order — the actual definition of serializability. | A workflow that reads a fact, then writes elsewhere based on it, where the two transactions never share a single row for Postgres to lock. Room-booking (two users each check "is this slot free?", both insert), inventory checks, the doctors-on-call case below. Costs more (retry logic on failure) — only worth it when the invariant can't be pinned to one lockable row. |
SELECT ... FOR UPDATE locks exactly those rows and avoids retry logic entirely. For booking systems specifically, a UNIQUE (or EXCLUDE, for overlapping ranges) constraint on (room_id, slot) is usually the more robust fix — it turns the race into a database-enforced uniqueness violation regardless of isolation level. SERIALIZABLE earns its cost when no single row or constraint captures the invariant, which is exactly the doctors case: "at least one doctor on call" isn't expressible as a uniqueness constraint on one row.
Two terminals, side by side. Everything below assumes you have Docker.
docker run --name pg-iso --rm -e POSTGRES_PASSWORD=postgres -p 5432:5432 -d postgres:16Terminal A:
docker exec -it pg-iso psql -U postgresTerminal B (a second, independent connection):
docker exec -it pg-iso psql -U postgresIn 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.
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;Don't commit yet.
accounts right now? Click to check.SELECT balance FROM accounts WHERE id = 1;500, not 400 — A's write is invisible to B until committed.
COMMIT;SELECT balance FROM accounts WHERE id = 1;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;Leave this transaction open.
Session A — a full, independent, already-committed transaction:UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;SELECT balance FROM accounts WHERE id = 1;400. Two identical reads, same transaction, different answers — a non-repeatable read.
Session B COMMIT;
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;UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;SELECT balance FROM accounts WHERE id = 1;Still 500 — B's snapshot was frozen at its first statement; A's commit is invisible to it no matter how many times B looks.
Session B COMMIT; then SELECT again → now 400, in a fresh transaction.
REPEATABLE READ isn't purely passive about concurrent writes. If two REPEATABLE READ transactions try to update the same row, the second one fails outright rather than silently overwriting the first.
UPDATE accounts SET balance = balance - 50 WHERE id = 1;B's UPDATE itself errors immediately, before B even attempts to commit. Keep this in mind for Part 3: REPEATABLE READ does catch same-row conflicts. It just has no way to catch a conflict that spans two different rows — which is exactly what write skew is.
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: SELECT * FROM doctors; should show both alice and bob as t.
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;Both transactions have now seen "2 on call" and, per the invariant, each independently believes it's safe to go off call.
Session AUPDATE doctors SET is_on_call = false WHERE name = 'alice'; COMMIT;UPDATE doctors SET is_on_call = false WHERE name = 'bob'; COMMIT;It succeeds — unlike the same-row case, because this is a different row, so there's nothing for REPEATABLE READ's conflict detection to catch.
SELECT * FROM doctors ORDER BY name;Zero doctors on call. Invariant broken, no error raised anywhere.
Reset: UPDATE doctors SET is_on_call = true; Now repeat with SERIALIZABLE instead of REPEATABLE READ in the BEGIN statements above.
Same steps, same order as above, just BEGIN ISOLATION LEVEL SERIALIZABLE instead.
UPDATE doctors SET is_on_call = false WHERE name = 'bob'; COMMIT;Postgres's serializable mode detects the read/write dependency cycle between the two transactions — A read data B later wrote, and B read data A later wrote, with no way to order them serially — and aborts one side. Your application is expected to catch this and retry.
SELECT * FROM doctors ORDER BY name;Invariant holds: exactly one doctor went off call, the other's conflicting update was rejected.
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, 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.
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.
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?pg_stat_activity and pg_locks from a third session to see the open transaction and (in Part 1) the row lock A holds.Sources: PostgreSQL Docs: Transaction Isolation (Part 3 is their canonical write-skew example) · Kleppmann, Designing Data-Intensive Applications, ch. 7 ("Write Skew and Phantoms")
docker stop pg-iso