Locking lets one transaction wait for another to finish. A deadlock is what happens when two transactions wait for each other — a circular standoff neither can escape. This lesson shows how that arises, how Postgres breaks it, and the simple ordering discipline that stops it from happening at all.
The seed is the Module 9 ledger: four accounts with a starting balance of 100 each.
SELECT id, owner, balance FROM accounts ORDER BY id;
What a deadlock actually is
Picture two money transfers running at the same moment. One moves money from Ada (id 1) to Sofia (id 4); the other moves money from Sofia to Ada. Each UPDATE takes a row lock, and each transaction grabs its two rows in a different order:
-- Session 1: transfer ada -> sofia
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- locks row 1
-- ... now wants row 4, but Session 2 holds it
UPDATE accounts SET balance = balance + 10 WHERE id = 4; -- BLOCKS
-- Session 2: transfer sofia -> ada
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 4; -- locks row 4
-- ... now wants row 1, but Session 1 holds it
UPDATE accounts SET balance = balance + 10 WHERE id = 1; -- BLOCKS
Session 1 holds row 1 and waits for row 4. Session 2 holds row 4 and waits for row 1. Neither will ever release, because each is blocked on the other. That cycle is a deadlock. Note it has nothing to do with how much work each does — it is purely the order in which they reach for the two locks.
Postgres detects it and picks a victim
Left alone, those two sessions would hang forever. Postgres doesn't allow that. Whenever a transaction waits on a lock for longer than deadlock_timeout (default 1 second), the engine pauses to check the wait graph for a cycle. If it finds one, it aborts one of the transactions — the victim — so the other can proceed:
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 678;
blocked by process 12346.
Process 12346 waits for ShareLock on transaction 679;
blocked by process 12345.
HINT: See server log for details.