Inside serializable

A bag holds two marbles, one black and one white. Transaction A paints every black marble white. Transaction B paints every white marble black. Each is a single UPDATE.

Run them one after the other. If A goes first, both marbles become white, and then B paints them both black. If B goes first you get the opposite.

Either way, both marbles end up the same color.

SERIALIZABLE guarantees that the result matches some serial order. It doesn’t say which one, and it doesn’t say every transaction will succeed.

The deal is this: you write each transaction as if it ran alone, and in return you handle SQLSTATE 40001 by running the transaction again.

Postgres does this without adding any locks that block. Readers still don’t wait for writers. Transactions run exactly as they would under REPEATABLE READ, and the database keeps notes on the side.

When the notes show that no serial order is possible, it aborts one transaction. The technique is called Serializable Snapshot Isolation, or SSI.

You can stop here and use SERIALIZABLE correctly. The next six steps are what Postgres tracks, how it decides, and what it costs.

The notes are SIRead locks. Each time a SERIALIZABLE transaction reads, Postgres records what it read: a row, an index page, or a whole table. They never block anyone.

They’re kept even after the transaction commits, for as long as any transaction that overlapped with it is still running.

When one transaction writes something that a concurrent transaction read without seeing that write, Postgres draws an arrow: the reader must come before the writer.

B looked for white marbles and didn’t see A turn m1 white, so B → A. A looked for black marbles and didn’t see B turn m2 black, so A → B. Two arrows pointing at each other.

Finding every cycle in a busy database would be too slow. Postgres looks for a simpler shape: a transaction with an arrow coming in and an arrow going out, where the transaction at the far end committed first. Every real anomaly contains that shape.

Not every such shape is a real anomaly, so SSI sometimes aborts a transaction that would have been fine. Those false positives are the price of not waiting.

The error can come at a read, at a write, or at COMMIT, and Postgres tries to pick a transaction that will succeed if you retry it straight away. The diagram here only checks at commit.

Read-only transactions can also be part of an anomaly. SERIALIZABLE READ ONLY DEFERRABLE waits until it can take a snapshot that’s guaranteed safe, then runs without ever being aborted. It’s the right mode for long reports.

Memory: predicate locks live in shared memory, sized by max_pred_locks_per_transaction. When one transaction holds many row locks on a page, Postgres merges them into a page lock, and then a table lock. Coarser locks mean more false conflicts.

Retries: under contention, a real share of transactions fail and run again. And mixing: SSI only tracks SERIALIZABLE transactions. One session writing under READ COMMITTED isn’t watched, and it can break the guarantee for everyone else.

Every SERIALIZABLE codebase needs the same small loop. Run the whole transaction; if it fails with 40001, run it again from BEGIN, a few times at most, with a short random pause between attempts.

The whole transaction, not just the last statement, because everything it read may have changed. And anything outside the database, like sending an email, belongs after the commit.

40P01, deadlock detected, is worth retrying the same way.