Deadlocks
A deadlock is the lock queue's dead end: A waits for B while B waits for A. Neither can ever proceed, so PostgreSQL breaks the tie by force — it detects the cycle and kills one of the transactions with SQLSTATE 40P01 (deadlock_detected).
Two transfers, opposite directions
The setup is two money transfers going opposite ways. A grabs alice's row, B grabs bob's row, and then each reaches for the row the other is holding. That reach is the cycle.
A> SET deadlock_timeout = '10s'; -- In production the victim is effectively arbitrary — whichever waiter's deadlock_timeout (default 1s) fires first runs the check and aborts itself. We pin it here so the transcript is reproducible: B checks first.
SET
B> SET deadlock_timeout = '50ms';
SETA transfers 10 from alice to bob; B transfers 25 from bob to alice.
A> BEGIN;
BEGIN
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- A locks alice
UPDATE 1
B> BEGIN;
BEGIN
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2; -- B locks bob
UPDATE 1A now needs bob's row (B has it) — it waits.
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…B now needs alice's row (A has it). A waits for B, B waits for A: a cycle.
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1; -- deadlock_detected — B is aborted…
ERROR: 40P01: deadlock detectedThe victim's abort frees bob's row, so A's stuck UPDATE completes.
⏵ A resumes:
UPDATE 1
A> COMMIT;
COMMIT
B> ROLLBACK;
ROLLBACK
A> SELECT owner, balance FROM accounts ORDER BY id; -- A's transfer survived; B's evaporated — retry it
owner | balance
-------+---------
alice | 90
bob | 110
(2 rows)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
How the detection works: a backend that has been waiting for deadlock_timeout (default 1 s) checks whether its wait is part of a cycle, and if so, aborts itself. Two consequences are worth internalizing. The first is that deadlocks cost latency before they cost errors — every one burns at least deadlock_timeout of pure waiting before anything is aborted. The second is that the victim is effectively arbitrary: whoever's timer fires first while the cycle exists is the one that dies. The transcript above is only reproducible because we pinned the timers; the manual itself says which transaction gets aborted is "difficult to predict and should not be relied upon". Write code that survives either transaction being the victim.
The cure: lock in a consistent order
Deadlocks need a cycle, and a cycle needs disagreement about order. Remove the disagreement and the deadlock isn't "less likely" — it's gone entirely:
Both transfers grab all their row locks up front, ordered by id.
A> BEGIN;
BEGIN
A> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
id
----
1
2
(2 rows)
B> BEGIN;
BEGIN
B> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
⏳ B is waiting for a lock…No cycle is possible: B parks at the first row and holds nothing A needs.
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE 1
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
UPDATE 1
A> COMMIT;
COMMIT
⏵ B resumes:
id
----
1
2
(2 rows)
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
UPDATE 1
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
UPDATE 1
B> COMMIT;
COMMIT
A> SELECT owner, balance FROM accounts ORDER BY id; -- both transfers landed — same workload, zero deadlocks
owner | balance
-------+---------
alice | 115
bob | 85
(2 rows)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
40P01 is retryable, exactly like the 40001 you met under serializable conflicts: roll back and retry the whole transaction. The other transaction finished fine, so your data is consistent — nothing to clean up; run yours again.
The real fix, though, isn't retrying faster; it's never forming the cycle. That means consistent lock ordering, in the manual's own words: "the best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order". Sort by primary key before FOR UPDATE, always update account pairs in id order, take the "parent" lock before the "child" — one convention, zero deadlocks. Locking everything up front with SELECT ... WHERE id IN (…) ORDER BY id FOR UPDATE turns a potential deadlock into a plain queue wait.
One last thing to watch for: because deadlock_timeout is a full second, deadlock-prone code shows up as latency spikes long before you notice the errors. Frequent 40P01 in the logs is a design smell, not bad luck — the monitoring lesson shows how to catch the wait before the timer fires.