Serializable

For a prediction task, open the staffing practice before reading the explanations below.

Two REPEATABLE READ transactions can both read two on-call doctors, then update different rows and commit, leaving none on call. That is the write-skew problem. Stable snapshots alone do not protect this rule.

The PostgreSQL 18 Serializable manual documents serial-equivalent execution for committed SERIALIZABLE transactions. A business rule is protected by this contract only when each transaction preserves it in serial execution and all relevant writers participate. Constraints or suitable explicit locking can protect some rules at weaker levels too.

PostgreSQL uses Serializable Snapshot Isolation: snapshot reads plus monitoring of dependencies that can make the execution non-serializable. Detection can cause 40001 at a statement or COMMIT. The contract does not specify a universal winning session or promise that the first committer always wins.

Why REPEATABLE READ isn't enough: write skew

Both displayed transactions commit and the final asserted on-call count is zero.

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)

Both transactions checked their own snapshots, but the final count is zero. SERIALIZABLE rejects this schedule; coordinating all writers with suitable explicit locks is another approach.

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The same interleaving, SERIALIZABLE

In this schedule A commits, B's COMMIT fails with 40001, and one doctor remains. B's fresh transaction then asserts one on-call doctor and leaves that doctor on call.

A

B

on-call count→ 2

on-call count→ 2

alice off call

bob off call

COMMIT succeeds in this schedule

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

In this schedule A commits and B is rejected. Other schedules can fail at a statement or select a different victim.

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 now starts a fresh transaction, checks the count again, and keeps the remaining doctor on call.

B> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN

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

B> COMMIT;
COMMIT

A> SELECT name, on_call FROM doctors ORDER BY name;
 name  | on_call 
-------+---------
 alice | f       
 bob   | t       
(2 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

Logical dependencies

Reader-to-writer logical ordering in the on-call schedule. Dashed open arrows are serialization dependencies, not blocked statements. This is a teaching model, not PostgreSQL's complete SSI detection algorithm or a captured internal graph.

Text equivalent: A's read of Bob's old on-call status requires A before B's removal in a serial explanation. B's read of Alice's old status requires B before A's removal. The cycle leaves no such serial ordering for both completed removals. The asserted REPEATABLE READ run commits both; the SERIALIZABLE run rejects B with 40001 at COMMIT. The edges explain those SQL observations; the Scenario does not inspect SSI tracking structures.

Documented contract: the PostgreSQL 18 manual says predicate locks “do not cause any blocking”. A logical dependency therefore need not mean a lock wait. Ordinary row/table lock conflicts still apply. Compare the separate InnoDB lock-wait picture: its solid arrows point from waiter to holder, rather than from reader to logical writer. Neither picture replaces the generated session timelines.

For the business-rule assumptions and conflict policy, use the on-call worked decision.

It even protects read-only transactions

In the REPEATABLE READ schedule, the report reads the closed-batch marker and two receipts before a third receipt commits into that batch. No serial ordering explains all three transactions' reads and writes: the cashier's old-marker read requires it before the closer, while the report's new marker puts it after the closer and its missing receipt puts it before the cashier. With all three SERIALIZABLE, this schedule rejects the cashier at COMMIT. No cashier retry is executed in this Scenario.

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

The report is modeled by SELECT results; no printing, external publication, or cashier retry is executed.

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 has two receipts; no cashier retry is executed here
 receipt_no | amount 
------------+--------
          1 |    100 
          2 |    200 
(2 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

Living with SERIALIZABLE

Handle 40001 explicitly

An aborted attempt has not completed the requested work. Either retry the whole transaction with fresh reads and decisions or return a controlled failure. Do not report the operation as committed. Retries need a policy for repeated failures.

The Serializable manual documents that even a read-only transaction can encounter serialization failure. Do not use its results as valid until it commits. Predicate-lock tracking can be conservative; combining locks due to resource limits can increase failures. These Scenarios do not measure that overhead or establish a performance tuning rule.

The SET TRANSACTION manual documents SERIALIZABLE READ ONLY DEFERRABLE: acquisition of a safe snapshot can wait, after which serialization-failure cancellation is avoided. That is not immunity from timeouts, connection loss, or other errors, and is not executed here.

SSI monitoring adds no blocking beyond REPEATABLE READ, according to the isolation manual. Normal row and table locks still apply. Retry bodies must account for effects outside the database transaction; rollback cannot undo an email or HTTP effect.

Further reading

• PostgreSQL 18: Serializable
• PostgreSQL SSI examples
• The same lesson on MySQL