Database Transactions

Appearance

Serializable ​

You got here from REPEATABLE READ, where your snapshot never shifts mid-transaction, so a shared invariant should be safe. It isn't. Two doctors are on call. Two transactions each check that at least one is still on call, each see two, and each take their own doctor off. Different rows, so nothing conflicts; both commit; the ward is now empty. No single transaction did anything wrong. That's write skew, and nothing weaker than SERIALIZABLE stops it.

SERIALIZABLE is the strongest level, and the one with a mental model you can actually hold: if every transaction runs SERIALIZABLE and commits, the result is guaranteed to equal some one-at-a-time execution of them. You reason about each transaction on its own and let the database guarantee the combination.

PostgreSQL implements this as Serializable Snapshot Isolation (SSI): REPEATABLE READ snapshots plus tracking of the read/write dependencies between concurrent transactions. When a pattern appears that could produce a non-serializable outcome, it aborts one transaction with SQLSTATE 40001 — keep it, retry it, done.

Why REPEATABLE READ isn't enough: write skew ​

Here's that on-call invariant as a live run at REPEATABLE READ. Two transactions, two different rows, both commit — and the rule they both checked is broken, with no error raised to warn you:

A
B
SELECT on_call→ 2 ← "safe for me to leave"
SELECT on_call→ 2 ← "safe for me to leave"
UPDATE alice off-call
UPDATE bob off-call (a different row)
COMMIT
COMMIT← both succeed, no conflict to detect
SELECT on_call→ 0 ← invariant broken

Hospital rule: at least one doctor must stay on call. Alice and Bob both want the night off.

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

B> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
 on_call 
---------
       2 
(1 row)

B> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- "two of us — safe for me to leave"
 on_call 
---------
       2 
(1 row)

Each updates a DIFFERENT row, so there is no write-write conflict to detect.

A> UPDATE doctors SET on_call = false WHERE name = 'alice';
UPDATE 1

B> UPDATE doctors SET on_call = false WHERE name = 'bob';
UPDATE 1

A> COMMIT;
COMMIT

B> COMMIT; -- both succeed!
COMMIT

A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- nobody is on call — the invariant is broken
 on_call 
---------
       0 
(1 row)

Each transaction was internally consistent; together they broke the rule. Only SERIALIZABLE catches this.

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The same interleaving, SERIALIZABLE ​

A
B
on-call count→ 2
on-call count→ 2
alice off call
bob off call
COMMIT — first committer wins
COMMIT← 40001 could not serialize access due to read/write dependencies among transactions

Same story, same statements, same order — only the isolation level differs.

A> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN

B> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN

A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call;
 on_call 
---------
       2 
(1 row)

B> SELECT count(*)::int AS on_call FROM doctors WHERE on_call;
 on_call 
---------
       2 
(1 row)

A> UPDATE doctors SET on_call = false WHERE name = 'alice';
UPDATE 1

B> UPDATE doctors SET on_call = false WHERE name = 'bob';
UPDATE 1

The first committer wins. The second cannot be serialized against it and is aborted.

A> COMMIT;
COMMIT

B> COMMIT; -- serialization_failure
ERROR:  40001: could not serialize access due to read/write dependencies among transactions

A> SELECT count(*)::int AS on_call FROM doctors WHERE on_call; -- the invariant survived
 on_call 
---------
       1 
(1 row)

B's job is to retry. On retry it would see only one doctor on call — and refuse the night off.

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

It even protects read-only transactions ​

The strangest anomaly in this chapter: at REPEATABLE READ, a read-only report can observe a state that no serial ordering of the transactions could ever produce — the numbers it printed become retroactively wrong. Under SERIALIZABLE, PostgreSQL aborts the writer that would invalidate the already-committed report:

Cashier
Closer
Report
SELECT deposit_no→ 1 (current batch)
INSERT receipt 3 into batch 1 (slow, uncommitted)
UPDATE deposit_no = 2 (batch 1 closed)
COMMIT
SELECT deposit_no→ 2 ← batch 1 is closed
SELECT batch 1→ receipts 1, 2 (total 300) ← report published
COMMIT
COMMIT← receipt 3 lands in the closed batch 1
re-read batch 1→ 3 receipts ← the published report was wrong

A bank tracks receipts per deposit batch. The cashier files a receipt into the current batch (1) — slowly.

Cashier> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Cashier> SELECT deposit_no FROM control;
 deposit_no 
------------
          1 
(1 row)

Cashier> INSERT INTO receipts VALUES (3, 1, 400);
INSERT 0 1

Meanwhile the closer moves the bank to batch 2 — from now on, batch 1 is supposedly complete.

Closer> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Closer> UPDATE control SET deposit_no = 2;
UPDATE 1

Closer> COMMIT;
COMMIT

An auditor prints the report for the closed batch 1.

Report> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

Report> SELECT deposit_no FROM control; -- batch 1 is closed…
 deposit_no 
------------
          2 
(1 row)

Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no; -- …and contains receipts 1 and 2, total 300. The report is published.
 receipt_no | amount 
------------+--------
          1 |    100 
          2 |    200 
(2 rows)

Report> COMMIT;
COMMIT

Now the cashier's receipt lands — in batch 1, which the report just declared final.

Cashier> COMMIT;
COMMIT

Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no; -- the published report was wrong: no serial order of these three transactions produces it
 receipt_no | amount 
------------+--------
          1 |    100 
          2 |    200 
          3 |    400 
(3 rows)

Rewind and replay the exact same interleaving — all three transactions SERIALIZABLE.

Report> DELETE FROM receipts WHERE receipt_no = 3;
DELETE 1

Report> UPDATE control SET deposit_no = 1;
UPDATE 1

Cashier> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN

Cashier> SELECT deposit_no FROM control;
 deposit_no 
------------
          1 
(1 row)

Cashier> INSERT INTO receipts VALUES (3, 1, 400);
INSERT 0 1

Closer> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN

Closer> UPDATE control SET deposit_no = 2;
UPDATE 1

Closer> COMMIT;
COMMIT

Report> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN

Report> SELECT deposit_no FROM control;
 deposit_no 
------------
          2 
(1 row)

Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no;
 receipt_no | amount 
------------+--------
          1 |    100 
          2 |    200 
(2 rows)

Report> COMMIT; -- the report itself commits fine —
COMMIT

— because SERIALIZABLE protects it by aborting the transaction that would invalidate it: the cashier.

Cashier> COMMIT;
ERROR:  40001: could not serialize access due to read/write dependencies among transactions

Report> SELECT receipt_no, amount FROM receipts WHERE deposit_no = 1 ORDER BY receipt_no; -- batch 1 still matches the published report; the cashier retries into batch 2
 receipt_no | amount 
------------+--------
          1 |    100 
          2 |    200 
(2 rows)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Living with SERIALIZABLE ​

A swallowed 40001 is a lost write

Logging a serialization failure and moving on means the transaction never happened. 40001 is not an error to report — it's an instruction to retry.

Retries aren't optional at this level. Any serializable transaction, even a read-only one, can be aborted with 40001, so your application has to retry, and a small wrapper makes that painless. Some of those aborts are false positives: SSI's dependency tracking is deliberately conservative and will occasionally cancel a transaction that would have been fine. The manual notes that when memory pressure forces page-level predicate locks to combine into relation-level ones, "an increase in the rate of serialization failures may occur". A false positive costs you a retry, never correctness.

Keep transactions short and small for the same reason. Dependency tracking is bounded by max_pred_locks_per_transaction and its _per_relation and _per_page siblings, and long transactions and sequential scans widen the conflict surface. For read-only work that must never be aborted or drag others down, BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE may block while it acquires a safe snapshot, then runs "without any risk of contributing to or being canceled by a serialization failure" — the manual calls it "well suited for long-running reports or backups".

The trade in one breath: SERIALIZABLE is REPEATABLE READ plus dependency monitoring rather than locks, so readers still never block writers, and it's the only level that stops write skew and the read-only anomaly on its own. Invariants that span multiple rows are safe here, or with the explicit locking of the next chapter, and nowhere weaker. Design for 40001 from the first day: retry loops, idempotent transaction bodies, and no side effects like emails or HTTP calls inside the transaction.

Further reading ​

MIT Licensed · Every transcript on this site was generated by a real database run against MySQL 8.4.10 and PostgreSQL 18.4 at bd6f201, and re-proven through psycopg and PyMySQL.

© 2026 Leonid Svyatov