Isolation levels & the anomalies they permit

The MVCC lesson ended on a loaded pair of transcripts: the same two sessions, the same interleaving, and two different answers depending on a single line. Under plain BEGIN — Postgres's default — the second read saw the freshly committed value; under Repeatable Read it kept seeing the old one, because the snapshot lived for the whole transaction instead of one statement. Neither behavior is a bug. They are two settings of a dial called isolation, and this lesson is about that dial.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE looks like the kind of config knob you cargo-cult and forget. It is not. Isolation levels are defined entirely by which concurrency anomalies they allow through — so the only way to actually understand them is to understand the anomalies first. Every one of them is a real bug that has corrupted real balances, and once you can name them, the isolation levels become exactly what they are: a menu of "which of these bugs am I willing to tolerate for more concurrency?" We'll do the anomalies first, each as two transactions on money-shaped rows, then map them onto what Postgres genuinely gives you (which is not quite what the SQL standard says).

Throughout, "transaction" means the BEGIN … COMMIT unit, and the enemy is always the same: two of them running concurrently, interleaving in a way that produces a result no serial (one-then-the-other) order ever would.

The anomalies, worst to subtlest

Dirty read — reading uncommitted garbage

A dirty read is seeing another transaction's writes before it commits — writes that might be rolled back and never have "really" happened.

-- T1                                  -- T2
BEGIN;                                 BEGIN;
UPDATE accounts SET balance = 0
  WHERE id = 'a';   -- not committed
                                       SELECT balance FROM accounts
                                         WHERE id = 'a';
                                       -- reads 0  ← DIRTY: T1 hasn't committed
ROLLBACK;  -- balance was never 0!     -- T2 acted on a number that never existed

T2 made a decision on a balance of 0 that got rolled back. This is the worst anomaly and the easiest to forbid. Postgres never allows dirty reads at any isolation level — even its weakest level is stronger than the SQL standard's floor. So you can mostly forget this one exists on Postgres; it's here because it defines the bottom of the standard's ladder.

Non-repeatable read — the same row changes under you

Read a row, read it again in the same transaction, get a different value because someone committed a change in between.

-- T1                                  -- T2
BEGIN;
SELECT balance FROM accounts
  WHERE id = 'a';   -- reads 100
                                       BEGIN;
                                       UPDATE accounts SET balance = 40
                                         WHERE id = 'a';
                                       COMMIT;
SELECT balance FROM accounts
  WHERE id = 'a';   -- reads 40  ← changed under T1!
COMMIT;

T1 saw two different balances within one transaction — this is exactly the first transcript from the MVCC lesson. If it read the balance, ran some logic, then re-read and acted on the assumption the value was stable, its logic is now built on sand. The fix is a stable snapshot for the whole transaction — a transaction-level (not statement-level) snapshot, which is precisely what that lesson's second transcript showed.

Phantom read — the set of rows changes

Like a non-repeatable read, but about which rows match a query rather than one row's value. Run the same WHERE twice, get a different set of rows because someone INSERTed (or deleted) a matching one.

-- T1                                  -- T2
BEGIN;
SELECT count(*) FROM expenses
  WHERE group_id = 'g';   -- 5 rows
                                       BEGIN;
                                       INSERT INTO expenses (group_id, amount)
                                         VALUES ('g', 200);
                                       COMMIT;
SELECT count(*) FROM expenses
  WHERE group_id = 'g';   -- 6 rows  ← a phantom appeared
COMMIT;

T1's second count disagrees with its first. If T1 was, say, summing a group's expenses to check a spending cap, a phantom row means its check and its sum disagree.

Lost update — two read-modify-writes, one silently erased

Two transactions both read a value, both compute a new value from it, both write — and the second write clobbers the first as if it never happened.

-- T1 (add 50)                         -- T2 (add 30)
BEGIN;                                 BEGIN;
SELECT balance FROM accounts
  WHERE id = 'a';   -- reads 100
                                       SELECT balance FROM accounts
                                         WHERE id = 'a';   -- also reads 100
UPDATE accounts SET balance = 150      -- (100 + 50)
  WHERE id = 'a';
COMMIT;
                                       UPDATE accounts SET balance = 130  -- (100 + 30)
                                         WHERE id = 'a';
                                       COMMIT;
-- final balance: 130.  T1's +50 is gone. Should have been 180.

Both read 100, both wrote back their own computed total, and T1's update vanished. This is the anomaly behind countless "the numbers don't add up" bugs — a decrement-the-inventory, increment-the-counter, add-to-the-balance race. Note it requires a read then write of the same value — that's what distinguishes it from a plain non-repeatable read.

Write skew — the subtle one that survives snapshots

The trickiest, and the one that catches people who think "just use a consistent snapshot" is enough. In write skew, two transactions read an overlapping set of rows, each checks a condition that's currently true, and each writes a different row based on that check — and their combined writes violate an invariant that each one alone would have preserved.

The canonical money version: a rule says a group's combined outstanding settlements must not exceed some limit — say, at most one large settlement may be pending at a time. Two members initiate settlements concurrently:

-- T1 (settle 500)                     -- T2 (settle 500)
BEGIN;                                 BEGIN;
-- "is it safe? are there 0 pending    -- same check, same snapshot
--  large settlements right now?"       
SELECT count(*) FROM settlements
  WHERE group_id='g' AND pending;      SELECT count(*) FROM settlements
  -- 0 → safe to proceed                 WHERE group_id='g' AND pending;
                                         -- 0 → also safe to proceed
INSERT INTO settlements                
  (group_id, amount, pending)          INSERT INTO settlements
  VALUES ('g', 500, true);               (group_id, amount, pending)
COMMIT;                                  VALUES ('g', 500, true);
                                       COMMIT;
-- now TWO pending settlements exist. Each check was valid; the pair broke the rule.

Both read 0, both concluded "safe," both inserted — and the invariant "at most one pending" is now violated, even though neither transaction did anything wrong on its own. The killer detail: a consistent snapshot does not prevent this. Each transaction read a perfectly consistent view, made a locally-correct decision, and wrote a different row than the one it checked, so they never directly conflicted. This is why write skew needs something stronger than snapshots, and it's exactly the boundary between Postgres's two upper isolation levels.

What the standard says vs what Postgres does

The SQL standard defines four levels by which anomalies each forbids. But the standard was written around a lock-based implementation, and Postgres implements isolation with MVCC snapshots instead — so what Postgres actually delivers at each level is stronger than the standard's minimum, and differs in ways that matter:

Three things on this table earn their keep:

Read Committed (the default) takes a fresh snapshot for every statement. This is why two identical SELECTs in one transaction can disagree — each got its own snapshot (non-repeatable reads and phantoms are both possible). It's the loosest useful level, and it's the default because it maximizes concurrency and is good enough for most single-statement operations.

Repeatable Read is snapshot isolation — and this is the fact everyone gets wrong. Postgres's Repeatable Read takes one snapshot at the start of the transaction and holds it for the whole transaction, so non-repeatable reads and phantoms both disappear (stronger than the standard, which only requires it to stop non-repeatable reads). For lost updates it does something specific: if two transactions try to update the same row, the second to commit is aborted with ERROR: could not serialize access due to concurrent update — first-updater-wins, so lost updates are prevented too (that's the asterisk in the table). But write skew still gets through, because the two transactions in the write-skew example never update the same row — they insert different rows after reading an overlapping set. Snapshot isolation only detects direct write-write conflicts on the same row, and write skew has none. Repeatable Read is not "serializable minus phantoms"; it is snapshot isolation, and write skew is precisely the gap it leaves open.

Serializable closes that gap with SSI. Postgres Serializable adds Serializable Snapshot Isolation — it monitors the read/write dependencies between concurrent transactions and detects the "dangerous structures" that indicate a non-serializable interleaving (write skew among them). When it spots one, it aborts a transaction with ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001). The guarantee it buys is the strongest possible: any set of successfully-committed serializable transactions produces a result identical to having run them one at a time in some order — no anomalies of any kind, including write skew. The price is that you must be prepared to retry: a transaction can fail at commit through no fault of its own, and your application code has to catch 40001 and run it again.

The two escape hatches

You rarely need to make a whole transaction Serializable to fix one race. There are two standard tools, and choosing between them is a real design decision:

SELECT … FOR UPDATE — explicit row locks. Add FOR UPDATE to a SELECT and Postgres takes a write lock on every row it returns, so any other transaction that tries to SELECT … FOR UPDATE (or update) those rows waits until you commit. This turns the concurrent read-modify-write into a serial one for those specific rows — it defeats lost update directly, and it defeats write skew if the thing you need to lock is a row you can name and read. It's pessimistic (you take the lock up front, others block) and surgical (only the rows you point at). The catch: it can only lock rows that exist — it does nothing about a phantom row that isn't there yet, which is exactly the settlement-cap case where the danger is a row about to be inserted.

Serializable + retry — optimistic. Wrap the logic in a Serializable transaction and let SSI detect the conflict, aborting one party with 40001; your retry loop runs it again against fresh data. It's optimistic (nobody blocks; you find out at commit), it catches every anomaly including write skew and phantom-based ones that FOR UPDATE can't, and its cost is the retry machinery plus wasted work when conflicts are frequent. Rule of thumb: FOR UPDATE when the contended thing is a specific existing row you can lock (decrementing one account's balance); Serializable when the invariant spans rows or involves rows that don't exist yet (the "at most one pending settlement" cap).