Deadlocks
A lock deadlock is a cycle of conflicting requests: A waits for B while B waits for A. PostgreSQL 18 detects such cycles and aborts one transaction, reporting 40P01. Do not depend on which transaction becomes the victim.
Two transfers, opposite directions
A locks alice's row and B locks bob's. Each then updates the other's row. This schedule sets A's deadlock_timeout to 10 seconds and B's to 50 milliseconds to make B the observed victim:
A> SET deadlock_timeout = '10s'; -- General victim choice must not be relied on. These unequal detection timeouts make B the observed victim in this controlled schedule; no universal minimum cycle duration is asserted.
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 committed; B's database changes did not commit
owner | balance
-------+---------
alice | 90
bob | 110
(2 rows)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
deadlock_timeout controls how long a backend waits on a lock before checking for a deadlock, not a guaranteed minimum duration for every cycle. Its default is one second; the demonstration overrides it. Detection and scheduling latency are not benchmarked here.
The cure: lock in a consistent order
In this second schedule both transfers lock rows 1 and 2 in id order before changing either balance. B waits for A and then both transfers commit:
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 committed in this schedule
owner | balance
-------+---------
alice | 115
bob | 85
(2 rows)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
† For this two-row workload, if every participant takes its strongest required row locks in the same order and holds no other conflicting resources, the opposite-order cycle cannot form: a waiter on row 1 cannot already hold row 2 against the holder of row 1. This is an Entailed guarantee from the locking contract, not a proof of all possible schedules. Other tables, triggers, foreign-key checks, or lock upgrades can introduce other cycles.
After 40P01, roll back and, where the application permits it, retry the whole transaction with fresh decisions. The demonstrated victim's database changes do not commit. External effects and eventual retry success are not established. The monitoring lesson shows how to inspect waits.