The idea: snapshot isolation plus a conflict checker
Under REPEATABLE READ, PostgreSQL gives each transaction a snapshot: it sees the database as it was when its first statement ran. That stops dirty reads, non-repeatable reads and phantoms, but not write skew. Two transactions each read the same data, each decide it is safe to change a different row, and together they break a rule that each one checked.
SERIALIZABLE in PostgreSQL (since 9.1) is the same snapshot isolation plus a checker called Serializable Snapshot Isolation (SSI). Reads still never block and writes never wait for readers. SSI only watches who read what, and it aborts a transaction when the pattern of reads and writes could produce a result that no serial order would.
The page uses the classic example: a table doctors(name, on_call) where Alice and Bob are on call. The hospital's rule is that at least one doctor stays on call. Each doctor's transaction runs SELECT count(*) FROM doctors WHERE on_call, sees 2 and goes off call. Demo: write skew, REPEATABLE READ shows both commit and nobody is on call. Demo: write skew, SERIALIZABLE shows SSI stopping it.
SIREAD locks: a record of what was read
Every read by a SERIALIZABLE transaction takes a SIREAD lock (shown as SIReadLock in pg_locks). It is not a real lock: it blocks nobody and waits for nobody. It is a note in the predicate lock table that says "this transaction read this". It comes at three sizes:
| Granularity | When | On the page |
|---|---|---|
| tuple | an index scan reads one heap tuple | doctors tuple (0,2) Bob |
| page | more than max_pred_locks_per_page (default 2) tuple locks on one page are promoted to one page lock | doctors page 0 |
| relation | a sequential scan, or more than max_pred_locks_per_relation page and tuple locks in one relation | rota (whole relation) |
Promotion keeps the lock table small (its size is set by max_pred_locks_per_transaction), at the cost of more false conflicts: a page lock also "covers" rows the transaction never read. SIREAD locks outlive COMMIT: a committed transaction's reads can still conflict with a write by a transaction that overlapped it, so they stay until every overlapping transaction has finished. Demo: lock granularity shows a promotion, a relation lock and locks kept after COMMIT.
The page assumes the queries on doctors use an index, so reads lock single tuples. On a three-row table the planner would really choose a sequential scan and lock the whole relation, which gives the same conflicts here.
rw-antidependencies: who must come first
An rw-antidependency (rw-conflict) from R to W means R read something that W then changed, and R did not see the change. In any equivalent serial order, R must come before W. SSI records one in two ways:
- A write hits a SIREAD lock. W writes a tuple, and a concurrent serializable R holds a SIREAD lock that covers it. The page turns the lock red and draws a purple arrow R → W.
- A read skips a newer version. R reads a row whose newer version, written by a concurrent W, is not in R's snapshot. That is the same "R did not see W's change", found from the reader's side.
One edge on its own is harmless. In Demo: one rw edge is harmless, T1 reads the draft rota, T2 publishes the rota and commits first, then T1 goes off call and commits. Both succeed: the result equals running T1 and then T2, even though T2 committed first. Commit order is not the serial order.
The dangerous structure
A real anomaly needs a cycle in the dependency graph, and Fekete and others proved that every cycle under snapshot isolation contains two consecutive rw edges T_in → pivot → T_out where T_out commits first. Checking for the whole cycle would be expensive, so PostgreSQL looks only for this dangerous structure. When it appears, one transaction (normally the pivot, so that a retry makes progress) fails with:
ERROR: could not serialize access due to read/write dependencies among transactions DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt. HINT: The transaction might succeed if retried.
The SQLSTATE is 40001 (serialization_failure). In write skew the cycle is just T1 → T2 → T1, so T1 is both T_in and T_out. Nothing fails while both are running. When T1 commits, its COMMIT finds the structure with itself committing first and marks T2 doomed. T2 learns it at its next statement, here its COMMIT.
PostgreSQL decides who fails like this: if the transaction that would close the structure is the one running the statement, that statement fails at once. If the victim is another transaction that is still running, it is marked doomed and fails at its next statement. A transaction that has already committed can never be aborted, so then the one running the statement fails instead.
Errors and retry
A 40001 is not a bug: it is SSI's way of saying "run this again". Applications that use SERIALIZABLE must catch 40001 and retry the whole transaction, from BEGIN. On the retry T2 gets a new snapshot that includes T1's commit, counts only one doctor on call, and Bob stays on call. Because the pivot is the one that is aborted and T_out has already committed, the retry cannot hit the same structure again.
Read-only transactions
A read-only transaction can be the T_in of a dangerous structure. In Demo: read-only report, T1 reads the draft rota and T2 publishes it (T1 → T2), T2 commits, then T1 sets Alice off call. A READ ONLY report T3 starts after T2's commit. It sees the published rota but still sees Alice on call, because T1 has not committed: T3 → T1 → T2. If T1 then committed, the published rota would have shown a doctor who was not on call. SSI marks the pivot T1 doomed, the report commits, and T1's COMMIT fails. Its retry sees the rota is published and gives up.
PostgreSQL knows that a read-only T_in is only dangerous when T_out committed before T_in took its snapshot. If T3 had started before T2's commit, the same edges would be harmless. A read-only transaction that sees no such risk is running on a safe snapshot and drops its SIREAD locks. BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE waits until it can get a safe snapshot, then runs with no SSI overhead and no risk of 40001: a good fit for long reports and backups.
SERIALIZABLE vs REPEATABLE READ
| REPEATABLE READ | SERIALIZABLE (SSI) | |
|---|---|---|
| Snapshot | one per transaction | one per transaction |
| Reads block writes? | no | no (SIREAD locks block nobody) |
| Same row updated by two | first updater wins, 40001 | first updater wins, 40001 |
| Write skew | allowed ✗ | prevented ✓ (40001) |
| Extra cost | none | lock table memory, conflict checks, some false-positive aborts |
SSI has false positives: it aborts on the dangerous structure without checking that a cycle really closes, and page or relation locks cover more than was read. It never has false negatives. Short transactions, index scans (tuple locks instead of relation locks) and READ ONLY where possible keep the abort rate low. Two-phase locking would also prevent write skew, but by making readers block writers; see Two-Phase Locking and Optimistic vs Pessimistic Locking.
See also MVCC and Isolation Levels for all four isolation levels and their anomalies, How PostgreSQL Runs a Query for row versions, xmin/xmax and VACUUM, the PostgreSQL lock manager for the heavyweight locks that SIREAD locks are not, and commit status and hint bits for how a reader knows a writer committed.
MySQL's InnoDB takes the other approach: SERIALIZABLE turns SELECT into FOR SHARE (see InnoDB Isolation: Snapshot Reads vs Current Reads).
What the page leaves out
Real PostgreSQL also takes SIREAD locks on index pages (B-tree leaf pages, GiST, GIN and hash), which is how it catches phantoms: an insert into a range another transaction scanned. It assigns an xid only at the first write, keeps finished transactions in a summarized form when memory runs low (pg_serial), lets an UPDATE drop its own tuple lock, and has extra rules for prepared (two-phase) transactions and for parallel query. Its checks can fire at a read, a write or a commit, and it may abort a transaction that the page's simpler rules would let through, or the other way round in corner cases. Same-row write conflicts that would wait for a row lock are refused here instead of blocking.