Isolation Levels for Real

Postgres accepts all four standard isolation levels and implements three of them. Choosing between those three is an engineering decision with measurable costs on both sides: a level that is too low loses writes that go unnoticed for months, and a level that is too high turns a contention spike into user-visible errors. Neither failure shows up in single-threaded testing.

This is where the strawberries get settled. One box, two confirmations, one apology on Monday: the March incident has three correct fixes in Postgres, and they cost three very different things. All three appear here, applied to Cartwheel's actual checkout, and one of them ships.

Read Committed

The default takes a fresh snapshot for every statement. Each query sees data committed before that query began, dirty reads are impossible, and an ordinary UPDATE never fails with a serialization error — it waits for the transaction ahead of it and then re-checks its WHERE clause against the row version that transaction left behind. For the vast majority of short OLTP work that is exactly right, and it is the level under which Cartwheel's 3,000 checkouts a minute should keep running.

What it does not offer is a stable view across statements. Read the same row twice and it may have changed; count matching rows twice and the count may move. That is precisely the property the strawberries relied on. It is also what makes the level cheap: no dependency tracking, no retries, no snapshot held longer than one statement needs it.

Repeatable Read

One snapshot is taken at the transaction's first statement and used until it ends. Repeated reads agree, and Postgres goes further than the standard requires: phantom reads do not occur either, so a query re-run inside the transaction cannot pick up rows another transaction inserted. What the level does not prevent is a serialization anomaly across transactions, and it enforces its consistency on writes by refusing them.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT on_hand FROM inventory
 WHERE product_id = 4471 AND warehouse_id = 2;   -- 1

-- another session decrements this row and commits

UPDATE inventory SET on_hand = on_hand - 1
 WHERE product_id = 4471 AND warehouse_id = 2;
-- ERROR:  could not serialize access due to concurrent update
ROLLBACK;

The transaction is not partially applied and it is not silently corrected. It is aborted, and the application's only correct response is to replay the whole thing from the beginning against a fresh snapshot. The trade the level exists to make is exactly that replacement: an error you have to handle instead of a wrong number you will not notice. Under Read Committed the same pair of statements would have succeeded and written a value computed from a stale read.

Serializable

Serializable is Repeatable Read plus monitoring. Postgres tracks the read/write dependencies between concurrent Serializable transactions and aborts one when the set of them could not have been produced by running them one at a time in any order. The technique is called serializable snapshot isolation, and the tracking structures show up in pg_locks with a mode of SIReadLock. Those predicate locks block nothing and cannot take part in a deadlock. The cost is bookkeeping and aborted transactions, not waiting.

Scope is the first limit on where the level fits: the guarantee holds across a set of Serializable transactions, so a concurrent Read Committed session is not part of the set and is not held to it. Raising one transaction to Serializable protects nothing if the transaction it races against was left at the default. The second limit is the cost of coarse tracking. Predicate locks start fine-grained and are combined into coarser ones when the tracking table runs short of memory, and a sequential scan always takes a relation-level predicate lock — either of which raises the abort rate sharply. A Serializable transaction that seq-scans orders is a Serializable transaction that conflicts with every writer on the table.

There is one more honest caveat in the documentation: the level does not guarantee that no error other than a serialization failure occurs. Overlapping Serializable transactions can still produce a unique-constraint violation, even when each of them checked for the key first. A handler that only knows about 40001 will meet 23505 anyway.

Retry Is Part of the Contract

At Repeatable Read and Serializable, 40001 is not an error condition to log and move past. It is the level working as designed, and the application owes it a bounded retry of the entire transaction, at the outermost layer where every statement can be replayed. Every serialization failure carries that same SQLSTATE regardless of which of the two messages it prints, which makes the handler easy to write and easy to forget.

for attempt in 1..5:
    try:
        BEGIN ISOLATION LEVEL SERIALIZABLE;
          -- every statement of the business transaction, replayed
        COMMIT;
        break
    except SQLSTATE '40001' or '40P01':
        ROLLBACK;
        sleep(random(0, 50ms * 2 ** attempt))   -- backoff with jitter
raise -- five failures is a contention problem, not a retry problem

That sketch has three load-bearing details. The retry wraps the transaction rather than the failing statement, because a partially executed transaction is gone and its reads cannot be trusted. The backoff carries jitter, because synchronized retries reproduce the contention that caused the abort. And the attempt count is bounded, because an unbounded retry loop turns a hot row into an outage that looks like a hung application. Deadlocks, which arrive as 40P01, belong in the same handler for the same reason.

Fixing the Strawberries, Three Ways

The failing checkout reads on_hand, decides in Python, and writes a constant. The constraint Chapter 3 put on that column is not one of the answers: CHECK (on_hand >= 0) refuses a negative number, and both of these checkouts wrote zero. Three fixes remove the gap itself, and they are not interchangeable — each buys correctness with a different currency.

UPDATE inventory
   SET on_hand = on_hand - 1
 WHERE product_id = 4471
   AND warehouse_id = 2
   AND on_hand > 0;

-- UPDATE 1  -> stock was there, the sale is confirmed
-- UPDATE 0  -> no stock, refuse the sale and tell the customer

This runs at Read Committed and needs nothing else. The previous topic supplied the mechanism — one statement, one snapshot, and a loser that waits and then re-evaluates its guard against the version the winner committed. What is new here is what the application is left holding. It gets a row count of zero rather than an exception, so the out-of-stock path is a test on the affected-row count instead of a handler, and there is no retry loop to write. The business rule moved from application memory into the WHERE clause, where the engine can enforce it, and the whole change is one predicate plus that test.

BEGIN;
SELECT on_hand FROM inventory
 WHERE product_id = 4471 AND warehouse_id = 2
   FOR UPDATE;          -- a second session blocks right here

-- the application decides, over the network, with the lock held

UPDATE inventory SET on_hand = on_hand - 1
 WHERE product_id = 4471 AND warehouse_id = 2;
COMMIT;                 -- the row lock is released here, not before

This is explicit and easy to reason about: the contenders serialize at the SELECT, and the second session reads the value the first one left. It is also the expensive option, because the lock is held for the entire application round trip. At 3,000 orders a minute, 40 ms of Python and one network hop per session turn the most popular product's row into a queue that every other checkout for that product stands in. Choose it when the decision genuinely cannot be expressed as arithmetic, a rule that consults a pricing service being the usual example, and keep everything between the lock and the commit inside the database.

BEGIN ISOLATION LEVEL SERIALIZABLE;

-- how many orders are already booked into this delivery window,
-- and how many couriers are on shift to carry them
SELECT count(*) FROM orders
 WHERE placed_at >= '2026-08-15 18:00+02'
   AND placed_at <  '2026-08-15 20:00+02';
SELECT count(*) FROM courier_shifts
 WHERE shift @> '2026-08-15 19:00+02'::timestamptz;

INSERT INTO orders (customer_id, placed_at, status, total)
VALUES (88213, '2026-08-15 19:12+02', 'pending', 41.90);
COMMIT;
-- ERROR:  could not serialize access due to read/write
--         dependencies among transactions      (SQLSTATE 40001)

Nothing in that transaction can be collapsed into one statement, and there is no single row to lock: the invariant is a count over a set of rows that other transactions are inserting into at the same time. Serializable is the general answer to that shape. Both sessions read the same window, both insert into it, and one of them is aborted because no serial order of the two produces the result they both assumed. The retry replays it, the second attempt reads the committed count, and the capacity rule holds without a single explicit lock.

Cartwheel ships fix (a). Checkout stays at Read Committed, the decrement carries its own guard, and the application reads the affected-row count instead of a stock level. Fix (b) was rejected on throughput at Saturday peak, and fix (c) was kept for the delivery-window capacity rule, where it earns its retries. The March incident is now a row count of zero and an out-of-stock message on the second customer's screen, and the two-line diff that produced that outcome is the smallest change in this book with the largest number of confirmations behind it.

Choosing per Transaction

The level is a property of a transaction, not of a database. BEGIN ISOLATION LEVEL REPEATABLE READ sets it for one transaction, SET TRANSACTION ISOLATION LEVEL does the same before the first query, and ALTER ROLE cartwheel_analytics SET default_transaction_isolation = 'repeatable read' makes a whole role's transactions consistent by default. A mixed setting is not a smell — checkout at Read Committed, the nightly reconciliation at Serializable, the dashboard's multi-query report at Repeatable Read is three correct decisions, not one indecisive one.

The mix needs two habits to stay safe. Write down which transactions participate in each invariant, because raising one side of a race to Serializable while the other stays at the default leaves the race where it was and charges for the tracking anyway. And declare a transaction READ ONLY when it writes nothing: a read-only Serializable transaction can often establish that it cannot cause an anomaly and stop tracking dependencies entirely.