Two transactions lock the same two rows in opposite order — A takes row 1 then wants row 2; B takes row 2 then wants row 1 — so each waits on the other. Predict they hang forever. Instead, after deadlock_timeout (default ~1 s) Postgres detects the cycle and aborts one victim with SQLSTATE 40P01 deadlock detected; the survivor commits.
Two transactions each lock the same two rows in the opposite order: A takes row 1 then reaches for row 2; B takes row 2 then reaches for row 1. Each now holds a lock the other needs, so each waits on the other — a cycle. The naive prediction is that they wait forever (or until someone kills a session). They don't. Under two-phase locking Postgres holds each row's write-lock until commit, so the wait is real; but after deadlock_timeout (default ~1 s) Postgres's deadlock detector notices the cycle in its wait-for graph and aborts one victim with SQLSTATE 40P01 deadlock detected. The survivor's blocked statement immediately proceeds and it commits. You will type this into two real psql sessions, watch one session block, then watch the database pick a victim, print the exact 40P01 error (with its DETAIL about the two-process lock cycle), and let the other transaction win. The victim must retry at the app level.
Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 8 — Transactions, §"Implementation of two-phase locking":
Deadlocks can occur [...] The database automatically detects deadlocks between transactions and aborts one of them so that the others can make progress. The aborted transaction needs to be retried by the application.
The book names the mechanism (automatic detection + abort one) and the app's obligation (retry). Here you trigger the deadlock on demand and read the real error the database returns.
The surprise is about a cycle two lock-orders create, and how the database breaks it. These help; each line notes where it shows up.
40P01 DETAIL naming both processes waiting on each other.deadlock_timeout — the check isn't continuous; a transaction that blocks waits this long (default 1s) before Postgres runs the cycle-detection pass. So detection costs ~1 s of latency, paid only by transactions that actually block. It shows up as the ~1 s pause before the victim is shot.deadlock_timeout (#4): the PostgreSQL manual entry for deadlock_timeout (Server Configuration → Lock Management). Lock ordering as the fix (#2): DDIA §"Deadlocks" and the standard application rule "acquire locks in a consistent global order." If only one is new, make it #3 — a deadlock is a cycle in the wait-for graph, and everything else is how the database finds and breaks it.
psql sessions against one server; the capture harness code/deadlock_detection.py drives the same two transactions from two threads, via psycopg[binary] on Python 3.12 (uv). deadlock_timeout is the default 1s. Which transaction becomes the victim can vary run to run — the DETECTION is deterministic, the victim identity is not. The transcripts below caught Session B as the victim; you may catch A.
A deadlock is a cycle in the wait-for graph. Each transaction is a node; draw an arrow from a transaction to the one it is waiting on to release a lock. When A → B (A waits on B's row) and B → A (B waits on A's row), the arrows form a loop, and no one in the loop can ever advance on its own — each is waiting for the other to move first.
Postgres does not spin forever. Any transaction that blocks on a lock sets a timer; if it's still blocked after deadlock_timeout (1 s), the backend runs a deadlock-detection pass that walks the wait-for graph looking for a cycle. If it finds one, it picks a victim, aborts it (freeing its locks), and the remaining transactions proceed. The check is periodic and lazy — paid only when something is actually stuck — which is why detection costs ~1 s of latency but zero overhead on transactions that never block.
The capture harness code/deadlock_detection.py runs exactly this from two threads so the transcript is real; the Steps below have you type it by hand, which is the point.
# a Postgres 16 server (this exercise's is already running as ddia-ch08-pg):
docker run -d --name ddia-ch08-pg \
-e POSTGRES_HOST_AUTH_METHOD=trust -e POSTGRES_DB=study \
-p 5432:5432 postgres:16
# your own database, seeded with two rows at v=0:
docker exec ddia-ch08-pg psql -U postgres -c "CREATE DATABASE ex_ch08_deadlock;"
docker exec ddia-ch08-pg psql -U postgres -d ex_ch08_deadlock -c \
"CREATE TABLE rows (id int PRIMARY KEY, v int); INSERT INTO rows VALUES (1,0),(2,0);"
# open TWO psql sessions in two terminals -- Session A and Session B:
docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_deadlock # Session A
docker exec -it ddia-ch08-pg psql -U postgres -d ex_ch08_deadlock # Session Bcode/deadlock_detection.py is the capture harness — it drives the same two transactions from two threads and prints the verbatim 40P01. Run the Steps by hand first, then check against it. Do not read ahead — make each prediction first.
Predict. In Session A, BEGIN and update row 1. In Session B, BEGIN and update row 2. They touch different rows. Does either block, or do both return immediately?
-- Session A:
BEGIN;
UPDATE rows SET v=v+1 WHERE id=1;
-- Session B:
BEGIN;
UPDATE rows SET v=v+1 WHERE id=2;Both return UPDATE 1 instantly. A holds the write-lock on row 1, B holds the write-lock on row 2 — no conflict yet, because 2PL only blocks you when you want a lock someone else already holds.
Predict. Still inside its transaction, Session A now updates row 2 — the row B is holding. What does A's psql do: return UPDATE 1, error, or something else?
-- Session A:
UPDATE rows SET v=v+1 WHERE id=2;A blocks. Under 2PL, B holds row 2's write-lock until it commits, so A's UPDATE can only wait. Nothing is wrong yet — this is an ordinary lock wait, and if B committed now, A would immediately proceed. Leave A hanging and go to B.
Predict. Session B, still open, now updates row 1 — the row A is holding. Now A waits for B and B waits for A. Do both sessions hang forever until someone kills them?
-- Session B:
UPDATE rows SET v=v+1 WHERE id=1;Not forever — ~1 second. The moment B asked for row 1, the wait-for graph gained its second edge and closed the cycle. After deadlock_timeout (1 s) Postgres ran its detection pass, saw the cycle — the DETAIL spells it out: process 474 waits on 473, process 473 waits on 474 — and aborted B as the victim. The instant B's locks were released, A's blocked UPDATE completed. Now commit the survivor:
Both rows show v=1 — entirely from A, the survivor (its two +1s). Every change B made was rolled back with it. In a real app, B would now catch 40P01 and retry its transaction.
Predict. Re-run the whole thing, but have both sessions lock row 1 before row 2 (a consistent global order). Session A: update 1, then 2. Session B: update 1, then 2. Can a deadlock still form?
-- reset: UPDATE rows SET v=0; (either session, its own txn)
-- Session A: -- Session B:
BEGIN; BEGIN;
UPDATE rows SET v=v+1 WHERE id=1; UPDATE rows SET v=v+1 WHERE id=1; -- wants row 1
UPDATE rows SET v=v+1 WHERE id=2;
COMMIT;
UPDATE rows SET v=v+1 WHERE id=2;
COMMIT;No deadlock — B just waits, then wins. Because both sessions grab row 1 first, B blocks on row 1 at the start and can't get ahead of A to grab row 2. There is never a moment where A holds something B has and B holds something A has, so the wait-for graph stays acyclic — a plain queue, not a loop. Both transactions commit; each row ends at v=2.
UPDATEs return UPDATE 1 — no conflict on distinct rows.ERROR: deadlock detected (SQLSTATE 40P01) with a DETAIL naming the two processes in the cycle; the other's blocked statement completes and it commits. Final rows come entirely from the survivor: (1,1),(2,1). Which session is the victim varies.(1,2),(2,2).Postgres uses locks to enforce serializability of conflicting writes: a transaction that updates a row takes that row's write-lock and, under two-phase locking, holds it until commit. That "hold until commit" is what makes the guarantee sound — no other transaction can slip in and change the row underneath you — but it is also exactly what lets a cycle form. When A holds row 1 and wants row 2 while B holds row 2 and wants row 1, each is waiting for a lock the other won't release until it finishes, and neither can finish. Draw it as a wait-for graph — a node per transaction, an edge to whatever it's blocked on — and the two edges close a loop. A loop in that graph is the definition of a deadlock: a set of transactions each waiting on the next, all the way around.
A database has two options: prevent cycles, or detect and break them. Postgres detects. Rather than pay to keep the wait-for graph acyclic on every lock acquisition, it lets waits happen and, whenever a transaction has been blocked for deadlock_timeout (default 1 s), runs a pass that walks the graph from the blocked transaction looking for a cycle back to itself. Finding one, it must sacrifice a member to break it — there is no way to satisfy everyone in a cycle — so it picks a victim, aborts it (40P01), and releases its locks. That single abort collapses the cycle: the survivor's edge now points at a transaction that no longer holds the lock, so it proceeds. This is why the failure is ~1 s, not infinite, and why it surfaces as an error the loser must handle, not a hang. The victim is chosen by the backend and isn't stable across runs; your job as the application is simply to catch 40P01 and retry.
SET deadlock_timeout = '5s'; before you BEGIN, then re-run Steps 1–3. Predict how long the victim now takes to be shot (it's the waiting transaction's timeout that governs the pass), and confirm detection latency is a tunable, not a constant.Sources: DDIA 2e, Ch. 8, §"Two-phase locking (2PL)", §"Implementation of two-phase locking", §"Deadlocks" · PostgreSQL manual, §"Explicit Locking → Deadlocks" and deadlock_timeout.