Isolation Levels - What Each Level Permits

Day 24 taught snapshots. Today: the anomaly menu. READ COMMITTED permits non-repeatable reads; REPEATABLE READ blocks them but still permits write skew; SERIALIZABLE aborts the dangerous structure.

Two transactions touch the same rows. What may each one see, and what may they do? The SQL standard names the anomalies; PostgreSQL names the levels. An isolation level is a contract: which anomalies are possible while your transaction runs. Choose too weak and invariants break silently; choose too strong and you pay with serialization failures your app must retry.

The anomaly menu

• Non-repeatable read — you read a row; a concurrent commit changes it; you read it again in the same transaction and get a different value.
• Phantom — you re-run a range/predicate query; new matching rows appear (or disappear) mid-transaction.
• Write skew — two transactions read overlapping data that satisfies a cross-row invariant, then each writes a different row so the invariant breaks after both commit.

Dirty reads (seeing uncommitted data) never appear in PostgreSQL at any level — READ UNCOMMITTED is implemented as READ COMMITTED. The interview axis that matters is RC → RR → SERIALIZABLE (SSI).

Snapshot scope (Day 24 → today)

Day 24’s MVCC answer is how readers avoid waiting: each statement (or transaction) sees a snapshot of committed row versions. Isolation levels set the scope of that snapshot:

• READ COMMITTED — new snapshot per statement. Commits from others become visible between your statements.
• REPEATABLE READ — one snapshot for the whole transaction. Your reads are stable; concurrent commits are invisible until you end.
• SERIALIZABLE — RR snapshot plus SSI dependency tracking. Dangerous rw-dependency cycles abort one side with SQLSTATE 40001.

Anomaly permission matrix

Focused grid for the two anomalies we measured in the lab (phantom behaves like non-repeatable under PostgreSQL RR/SSI). “Permitted” means the anomaly can complete silently; “blocked” means the level prevents it; SSI blocks write skew by aborting.

Level	Non-repeatable read	Write skew	Lab outcome (30 runs)
READ COMMITTED (default)	permitted	permitted	NRR anomaly 30/30
REPEATABLE READ	blocked	permitted	NRR 0/30; skew 30/30
SERIALIZABLE (SSI)	blocked	blocked (abort)	skew 0/30; failures 30/30

Full standard menu (phantom included) is the same story: RC permits more; RR freezes the snapshot; SSI adds cycle detection.

Scenario 1 — non-repeatable read

-- T1                              -- T2
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM account WHERE id=1;  -- 100
                                    UPDATE account SET balance=balance+50 WHERE id=1;
                                    COMMIT;
SELECT balance FROM account WHERE id=1;  -- 150  (same txn!)

Under READ COMMITTED every statement sees a new snapshot of committed data — so the second read returns 150. Under REPEATABLE READ the whole transaction keeps one snapshot: the second read still returns 100. Lab: RC anomaly 30/30; RR anomaly 0/30.

Scenario 2 — write skew (worked sketch)

Invariant: combined balance must stay at least 100. Either 100 withdrawal is safe when the observed sum is 200, because it would leave 100. Setup: rows (1,100), (2,100); sum = 200. Both sessions use REPEATABLE READ, read the sum, then each withdraws 100 from a different account.

-- invariant: sum(balance) stays >= 100
-- T1                               -- T2
BEGIN ISOLATION LEVEL REPEATABLE READ;  BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM account;       SELECT sum(balance) FROM account;
-- 200 - 100 ≥ 100 → OK               -- 200 - 100 ≥ 100 → OK  (same snapshot)
UPDATE account SET balance=0 WHERE id=1; UPDATE account SET balance=0 WHERE id=2;
COMMIT;                                 COMMIT;
-- final: 0 + 0 = 0 < 100 → both thought they were safe; invariant broken

Why RR does not save you: neither session wrote the row the other wrote. There is no row-level lock conflict and no “stale version” on the write target — only a semantic conflict across the pair. Day 36’s deadlocks need opposite lock order on the same resources; write skew is the cross-row cousin where nobody waits and both commit.

Under SERIALIZABLE, SSI tracks that each txn’s write depends on a read of the other’s row set. It detects the dangerous structure and aborts one side with ERROR: could not serialize access due to read/write dependencies among transactions / SQLSTATE 40001. Lab: RR skew 30/30; SSI skew 0/30 with serialization failures 30/30.

Retry on 40001 (compose with Day 37)

SERIALIZABLE aborts are not bugs — they are the protection. The application must treat 40001 like a transient error: roll back, sleep with full jitter, and re-run the whole unit of work so the retry re-reads the new committed state.

// Sketch: SERIALIZABLE unit of work with 40001 retry
async function withSerializable(fn, { attempts = 8, base = 20, cap = 500 } = {}) {
  for (let i = 0; i < attempts; i++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await fn(client);
      await client.query('COMMIT');
      return result;
    } catch (err) {
      await client.query('ROLLBACK').catch(() => {});
      if (err.code !== '40001' || i === attempts - 1) throw err;
      const exp = Math.min(cap, base * 2 ** i);
      await sleep(Math.floor(Math.random() * (exp + 1))); // full jitter
    } finally {
      client.release();
    }
  }
}

Without the loop, SSI “protects” you by turning concurrent invariants into user-visible outages. Prefer schema invariants (unique / EXCLUDE / CHECK) when they encode the rule cheaper than SSI (Days 29–31, 34).

Benchmark — Podman PG 18 anomaly lab

Two concurrent sessions per scenario; 30 runs each; balances reset between runs. Table: iso_account(id, balance) rows (1,100), (2,100). The benchmark script and standalone write-up are not included in this repository.

Scenario	READ COMMITTED	REPEATABLE READ	SERIALIZABLE
Non-repeatable read anomalies	30 / 30	0 / 30	—
Write skew outcomes	—	30 / 30 skewed	0 / 30 skewed
Serialization failures	—	—	30 / 30 aborts

Headline: the anomalies are not hypothetical — RC showed a non-repeatable read in every run; RR skewed every run; SERIALIZABLE aborted every run. Stronger isolation trades silent corruption for explicit retry.

What breaks? — Anti-patterns

1. “We use transactions, so we’re safe.” A transaction is a boundary, not a guarantee — the level decides what anomalies survive inside it. Fix: name the invariant, then pick the level that protects it.

2. Read-check-write invariants at READ COMMITTED. “If no row exists, insert it” races under RC. Fix: unique constraint / upsert (Day 30) or RR/SERIALIZABLE where needed.

3. SERIALIZABLE without a retry loop. 40001 aborts are the mechanism working — swallowing them turns protection into outages. Fix: bounded retry with full jitter (Day 37) on serialization failures.

4. Assuming RR = serial. Repeatable Read still permits write skew — “I never saw stale data” is not “my invariant held.” Fix: test the skew path explicitly (like today’s lab).

How it connects

• Day 10 (ACID): “I” in ACID is a dial, not a boolean — today names the positions (RC / RR / SSI) and what each permits.
• Day 24 (MVCC): snapshots are the mechanism; isolation levels are the snapshot scope (per-statement vs per-transaction) plus SSI’s cycle check.
• Day 36 (deadlocks): opposite lock order on the same rows waits and cycles; write skew is the silent cross-row cousin — no wait, both commit, invariant dies.
• Day 37 (retries): SERIALIZABLE’s 40001 is a first-class retry reason — backoff + full jitter, then re-read.
• Day 38 (SKIP LOCKED): job claiming sidesteps cross-row checks by partitioning work at the row level instead of raising isolation.

Transfer questions

What you should be able to do

• Define non-repeatable read, phantom, and write skew in one sentence each.
• State which anomalies RC / RR / SERIALIZABLE prevent in PostgreSQL (permission matrix).
• Explain why SERIALIZABLE aborts are protection, not failure — and sketch a 40001 retry loop.
• Cite the lab: RC 30/30 non-repeatable, RR 0/30 NRR + 30/30 skew, SSI 0 skew + 30/30 aborts.
• Name the scope difference: per-statement snapshot (RC) vs per-transaction snapshot (RR), and contrast skew with Day 36 deadlocks.

Quiz

Your teach step

Close this lesson. Write the “Explain like I’m 10” and the “60-second LinkedIn version” from memory. Focus on: anomaly menu, snapshot scope (statement vs transaction), write skew vs deadlock, 40001 retry, and the 30/30 lab grid. Post it, and paste the link.

Questions? Ask the agent — SSI internals, predicate locks, or when a unique/EXCLUDE constraint beats SERIALIZABLE.