# Postgres: transaction isolation levels

## Concept

Two transactions running at the same time can see each other's changes to
varying degrees, controlled entirely by the isolation level — same schema,
same statements, different outcomes. 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:

- **§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 tests this: 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 `SELECT`s in one
  transaction can disagree.)*
- **§13.2.2 Repeatable Read** — "This level is different from Read Committed in
  that 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 write-skew interleaving has no serial
  equivalent, and Postgres refuses it with
  `ERROR: could not serialize access due to read/write dependencies among transactions`.)*

The claim under test, in one line: **the same interleaving of the same
statements is legal at `REPEATABLE READ` and illegal at `SERIALIZABLE`.**

Note that 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

- **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). Free:
  [PostgreSQL docs §13.4, "Data Consistency Checks at the Application
  Level"](https://www.postgresql.org/docs/16/applevel-consistency.html) and the
  internals write-up in [PostgreSQL 14 Internals, ch. 4
  "Snapshots"](https://postgrespro.com/blog/pgsql/5967899).
- **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 a transaction it identifies as the "pivot" of a dependency cycle that
  no serial order could have produced. This is why the failure surfaces as an
  error you must retry, not as a block. Free: [Ports & Grittner, "Serializable
  Snapshot Isolation in
  PostgreSQL"](https://drkp.net/papers/ssi-vldb12.pdf) (VLDB 2012) — §3 is
  enough.
- **Write skew** — the anomaly Part 3 produces. Two transactions read an
  overlapping set of rows, then each writes a *different* row from that set.
  Nothing they wrote was read by the other, so no per-row check can see the
  conflict. Free: [Wikipedia, "Snapshot isolation § Write
  skew"](https://en.wikipedia.org/wiki/Snapshot_isolation).

## Environment

- **Host:** macOS 26.5.2 (build 25F84), Apple Silicon (arm64)
- **Docker:** Docker Desktop, Engine 29.6.2 — Linux VM kernel `6.12.76-linuxkit`, 16 GiB allocated to the VM
- **Postgres image:** `postgres:16` — PostgreSQL 16.14 (Debian 16.14-1.pgdg13+1), digest `sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20`
- **Captured:** 2026-07-26

## Mental model: which isolation level do you actually need?

| 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 `SELECT`s five seconds apart in the same transaction can see different data if something else committed in between. | Most ordinary request handling: a web request that reads then writes doesn't usually care that another statement mid-request could see a slightly newer world. 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 — every read inside that transaction sees the same frozen world, no matter what else commits meanwhile. Postgres additionally detects and rejects any attempt to `UPDATE`/`DELETE` a row that a concurrent transaction already committed a change to — you get a serialization error on that statement, not a silent overwrite. | Anything that needs a *consistent multi-statement view*: generating a report or export that must reflect one instant even though it runs several queries, computing a total across multiple tables that must not mix pre- and post-update state, a long batch job that reads a lot and must not see a moving target. It does **not** protect an invariant that spans *two different rows* — see write skew below. |
| `SERIALIZABLE` | Everything `REPEATABLE READ` gives you, 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 mathematical definition of serializability, not just "consistent snapshot." | Any workflow that reads some fact, then performs a write *elsewhere* whose safety depends on that fact staying true — and where the two transactions' reads/writes don't share a single row for Postgres to lock. Classic examples: a room-booking system where two users each check "is this slot free?" (finding no matching row) and then both `INSERT` a new booking row for the same slot; an inventory system where two orders each check "is stock still > 0?" then decrement it via separate rows/paths; the doctors-on-call invariant below. Costs more (retry logic required on serialization failure) and only pays off when the invariant genuinely can't be pinned to a row you can lock directly. |

A narrower, often-cheaper alternative worth knowing before reaching for
`SERIALIZABLE`: if the conflicting rows are known upfront (e.g. "these two
bank accounts"), `SELECT ... FOR UPDATE` locks exactly those rows and
avoids the retry dance. For the booking-system case specifically, a
`UNIQUE` (or `EXCLUDE`, for overlapping time ranges) constraint on
`(room_id, slot)` is usually the most robust fix of all: it turns the race
into a database-enforced uniqueness violation regardless of isolation
level, since Postgres always guarantees constraints even under
`READ COMMITTED`. `SERIALIZABLE` is the general-purpose tool for when no
single row or constraint captures the invariant — the doctors example
below is exactly that case, since "at least one doctor on call" isn't
expressible as a uniqueness constraint on a single row.

## Setup

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

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

Terminal A:

```bash
docker exec -it pg-iso psql -U postgres
```

Terminal B (a second, independent connection):

```bash
docker exec -it pg-iso psql -U postgres
```

In **either** session, create the schema once:

```sql
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.

## Steps

### 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, so it's worth confirming before anything else.

1. **A**: `BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;`
   Don't commit yet. A prints `BEGIN` then `UPDATE 1`.
2. **Predict**: what does `B` see if it queries `accounts` right now?
3. **B**: `SELECT balance FROM accounts WHERE id = 1;`
4. **Observe**:

   ```
    balance
   ---------
        500
   (1 row)
   ```

   `500`, not `400` — A's write is invisible to B until committed. Note B
   is not blocked either: readers never wait for writers.
5. **A**: `COMMIT;`
6. **B**: `SELECT balance FROM accounts WHERE id = 1;` → now:

   ```
    balance
   ---------
        400
   (1 row)
   ```

### 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: **A**: `UPDATE accounts SET balance = 500 WHERE id = 1;`

7. **B**: `BEGIN; SELECT balance FROM accounts WHERE id = 1;` → `500`.
   Leave this transaction open.
8. **A**: `BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;`
   — a full, independent, already-committed transaction. (The explicit
   `BEGIN` matters only cosmetically: without it psql's autocommit already
   commits the `UPDATE`, and the bare `COMMIT` then prints
   `WARNING: there is no transaction in progress`.)
9. **Predict**: `B` runs the *exact same select* again, inside the *same*
   still-open transaction from step 7. Same result as before, or different?
10. **B**: `SELECT balance FROM accounts WHERE id = 1;`
11. **Observe**:

    ```
     balance
    ---------
         400
    (1 row)
    ```

    Two identical reads, same transaction, different answers — a
    **non-repeatable read**.
12. **B**: `COMMIT;`

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

13. **B**: `BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 1;` → `500`.
14. **A**: `BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;`
15. **Predict**, then **B**: `SELECT balance FROM accounts WHERE id = 1;` again.
16. **Observe**:

    ```
     balance
    ---------
         500
    (1 row)
    ```

    Still `500` — B's snapshot was frozen at step 13, A's commit is
    invisible to it no matter how many times B looks.
17. **B**: `COMMIT;` then `SELECT` again → now `400`, in a fresh transaction.

An important edge case before moving on: `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. Try it: **A** and **B** both
`BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 1;`
(both see `500`), then **A** runs
`UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;`, then
**B** tries `UPDATE accounts SET balance = balance - 50 WHERE id = 1;` —
B's `UPDATE` itself errors immediately, before B even attempts to commit:

```
ERROR:  could not serialize access due to concurrent update
```

(`SQLSTATE 40001`, raised from `ExecUpdate` in `nodeModifyTable.c`.) Roll B
back before continuing. 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.

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

What this part tests: an invariant that spans *two different rows*, where
neither transaction ever writes the row the other one reads or writes. The
same-row protection from Part 2 can't help here — there's no single row
conflict for Postgres to detect at `REPEATABLE READ`. 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 ORDER BY name;` should show

```
 name  | is_on_call
-------+------------
 alice | t
 bob   | t
(2 rows)
```

18. **A**: `BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM doctors WHERE is_on_call;` → `2`.
19. **B**: `BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM doctors WHERE is_on_call;` → `2`.
    Both transactions have now seen "2 on call" and, per the invariant,
    each independently believes it's safe to go off call.
20. **A**: `UPDATE doctors SET is_on_call = false WHERE name = 'alice'; COMMIT;`
21. **Predict**: does B's analogous update succeed, fail, or block? (Recall
    Part 2's same-row behavior — does it apply here?)
22. **B**: `UPDATE doctors SET is_on_call = false WHERE name = 'bob'; COMMIT;`
23. **Observe**: it **succeeds** —

    ```
    UPDATE 1
    COMMIT
    ```

    unlike Part 2's same-row case, because this *is* a different row, so
    there's nothing for `REPEATABLE READ`'s conflict detection to catch.
    Check: `SELECT * FROM doctors ORDER BY name;`

    ```
     name  | is_on_call
    -------+------------
     alice | f
     bob   | f
    (2 rows)
    ```

    Zero doctors on call, invariant broken, no error raised anywhere.

Reset: `UPDATE doctors SET is_on_call = true;` (prints `UPDATE 2`). Now
repeat steps 18–22 with `SERIALIZABLE` instead of `REPEATABLE READ` in the
two `BEGIN` statements (`BEGIN ISOLATION LEVEL SERIALIZABLE; ...`).

24. **Predict**: same steps, same order — which of B's statements fails, and
    does it fail at the `UPDATE` or at the `COMMIT`?
25. **Observe**: B's **`UPDATE`** fails — not its `COMMIT`:

    ```
    ERROR:  could not serialize access due to read/write dependencies among transactions
    DETAIL:  Reason code: Canceled on identification as a pivot, during write.
    HINT:  The transaction might succeed if retried.
    ROLLBACK
    ```

    (`SQLSTATE 40001`; that trailing `ROLLBACK` is the *next* statement,
    B's `COMMIT`, reporting that an already-aborted transaction was rolled
    back instead.) Postgres's serializable mode detects the read/write
    dependency cycle between the two transactions — A read data B is now
    writing, and B read data A already wrote, with no way to order them
    serially — and cancels B as the pivot of that cycle, at the moment the
    write arrives rather than at commit time. Your application is expected
    to catch this and retry.
26. Confirm the invariant survived: `SELECT * FROM doctors ORDER BY name;`

    ```
     name  | is_on_call
    -------+------------
     alice | f
     bob   | t
    (2 rows)
    ```

## 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 (Part 3) 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.

Both failures share `SQLSTATE 40001` (`serialization_failure`), which is
the code your retry logic should key on. They differ in *when* they fire:
the `REPEATABLE READ` one is a row-level check inside `ExecUpdate`, while
the `SERIALIZABLE` one comes from SSI's dependency graph — and, in this
interleaving, it also fires at the `UPDATE` rather than at `COMMIT`,
because by then B is already identifiable as the pivot of the cycle. Don't
generalize that timing: SSI *can* also cancel a transaction at commit time
(reason code "Canceled on identification as a pivot, during commit
attempt") when the pivot only becomes identifiable later.

### 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:

| Rung here | Where it goes deeper |
| --- | --- |
| Part 1 — no dirty reads | [dirty-read-impossible](../ddia/ch08/dirty-read-impossible.md) asks for `READ UNCOMMITTED` *by name*, gets it (`SHOW transaction_isolation` even answers `read uncommitted`), and still can't produce the anomaly the level is named for. |
| Part 2 — non-repeatable reads | [read-skew](../ddia/ch08/read-skew.md) makes $100 vanish across two accounts, then proves the snapshot is per-*statement* rather than inferring it. |
| Part 2's edge case — same-row `40001` | [lost-update](../ddia/ch08/lost-update.md) reaches the same abort as *one of three* fixes, and prices it against the atomic `UPDATE … SET v = v + 1` and `SELECT … FOR UPDATE`. |
| Part 3 — write skew | [write-skew-doctors](../ddia/ch08/write-skew-doctors.md) runs these same two doctors and adds the fix this exercise doesn't: `SELECT … FOR UPDATE` at `READ COMMITTED`. [phantom-write-skew](../ddia/ch08/phantom-write-skew.md) then shows the case where that fix *stops working* — a double-booked room, where the conflicting row doesn't exist yet for `FOR UPDATE` to grab. |

And one rung above the top of this ladder:
[serializable-not-linearizable](../ddia/ch10/serializable-not-linearizable.md).
`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, which is
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) — `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
that has 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`).
- Read Postgres's own isolation docs:
  https://www.postgresql.org/docs/16/transaction-iso.html — §13.2.3 has 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") gives the general theory behind why snapshot
  isolation can't catch this class of anomaly — the doctors-on-call scenario
  is his. The four exercises mined from that chapter are mapped rung-by-rung
  against this one 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.

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