Deadlocks

Anatomy of a deadlock

When you see ERROR: deadlock detected in production logs, the instinct is to tune deadlock_timeout or add retries and move on. Resist that. Every deadlock is pointing at an ordering inversion that a one-line fix can eliminate permanently.

A deadlock needs exactly one ingredient: two transactions that acquire the same locks in opposite order. Each grabs its first lock, then asks for the second — which the other already holds — and neither will release until it gets what it’s blocked on. That’s a cycle in the wait-for graph, and no amount of waiting resolves it.

-- Transaction 1                          -- Transaction 2
BEGIN;                                     BEGIN;
UPDATE accounts SET balance = balance - 100
  WHERE id = 1;       -- locks row 1
                                           UPDATE accounts SET balance = balance - 50
                                             WHERE id = 2;     -- locks row 2
UPDATE accounts SET balance = balance + 100
  WHERE id = 2;       -- WAITS for row 2 (held by Tx2)
                                           UPDATE accounts SET balance = balance + 50
                                             WHERE id = 1;     -- WAITS for row 1 (held by Tx1)
-- now both wait on each other forever -> deadlock

How Postgres detects and resolves it

Postgres does not check for deadlocks on every lock wait — that would be expensive. Instead, when a transaction blocks on a lock, it waits deadlock_timeout (default 1 second) and only then runs the deadlock detector, which walks the wait-for graph looking for a cycle. If it finds one, it picks a victim and aborts it with:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321.
         Process 4321 waits for ShareLock on transaction 8765; blocked by process 1234.
HINT:    See server log for query details.
SQLSTATE: 40P01

The victim’s transaction is rolled back entirely; the survivor proceeds. The 1-second deadlock_timeout means a genuine deadlock costs at least ~1s of latency before resolution, so frequent deadlocks are both a correctness smell and a latency problem. Lowering the timeout makes detection faster but adds CPU on every ordinary lock wait — don’t tune it before fixing the ordering.

Prevention beats detection

Detection is a safety net, not a strategy. The cure is consistent lock ordering: if every transaction that touches multiple rows always locks them in the same global order, a cycle is impossible.

-- Deadlock-proof transfer: lock both rows in one statement, lowest id first.
BEGIN;
SELECT id FROM accounts
WHERE id IN (:from_id, :to_id)
ORDER BY id           -- the magic: deterministic order, both directions
FOR UPDATE;
UPDATE accounts SET balance = balance - :amt WHERE id = :from_id;
UPDATE accounts SET balance = balance + :amt WHERE id = :to_id;
COMMIT;

Because transfer(1, 2) and transfer(2, 1) now both lock id 1 before id 2, one fully acquires before the other starts — no cycle. The other levers, in priority order: keep transactions short (less time holding locks = smaller collision window), touch rows in a stable order app-wide, and only then consider NOWAIT to fail fast instead of waiting out the timeout.