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

## 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 -p 5432:5432 -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.
2. **Predict**: what does `B` see if it queries `accounts` right now?
3. **B**: `SELECT balance FROM accounts WHERE id = 1;`
4. **Observe**: `500`, not `400` — A's write is invisible to B until
   committed.
5. **A**: `COMMIT;`
6. **B**: `SELECT balance FROM accounts WHERE id = 1;` → now `400`.

### 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**: `UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;`
   — a full, independent, already-committed transaction.
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**: `400`. 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**: `UPDATE accounts SET balance = balance - 100 WHERE id = 1; COMMIT;`
15. **Predict**, then **B**: `SELECT balance FROM accounts WHERE id = 1;` again.
16. **Observe**: 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;`,
then **A** updates that row and commits, then **B** tries
`UPDATE accounts SET balance = balance - 50 WHERE id = 1;` — B's `UPDATE`
itself errors immediately with
`could not serialize access due to concurrent update`, 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.

### 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;` should show both `alice`
and `bob` as `t`.

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** — 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;` — both
    `alice` and `bob` are now `f`. 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 steps 18-19 (`BEGIN ISOLATION LEVEL SERIALIZABLE; ...`).

24. **Predict**: same steps, same order — does B's commit succeed this time?
25. **Observe**: `B`'s `COMMIT` fails:
    `ERROR: could not serialize access due to read/write dependencies among transactions`
    (`SQLSTATE 40001`). 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.

## 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`, 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.

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

## Go deeper

- Try Part 3 with three doctors and different commit orderings — does it
  matter who commits first?
- 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.
- Read Postgres's own isolation docs:
  https://www.postgresql.org/docs/current/transaction-iso.html — Part 3
  is their canonical write-skew example.
- Kleppmann, *Designing Data-Intensive Applications*, ch. 7 ("Write Skew
  and Phantoms") gives the general theory behind why snapshot isolation
  can't catch this class of anomaly.

## Cleanup

```bash
docker stop pg-iso
```