Deadlocks - When Lock Waits Form a Cycle

Day 10 taught transactions. Day 24 taught that FOR UPDATE is a real wait. Today: two waits that point at each other. PostgreSQL does not hang forever — it detects the cycle and aborts one victim.

A lock wait is normal: transaction B needs a row A holds, so B sleeps until A commits. A deadlock is different: A waits for B and B waits for A. That is a wait-for cycle. No amount of patience unblocks it. PostgreSQL’s deadlock detector walks the wait-for graph, picks a victim, and raises ERROR: deadlock detected so the other transaction can finish.

The problem: opposite update order

Imagine a transfer service that locks two accounts with SELECT … FOR UPDATE before moving money:

-- Session A (user pays B)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- holds 1
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- wants 2
-- …

-- Session B (user pays A) — opposite order
BEGIN;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;  -- holds 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- wants 1
-- cycle: A waits for B, B waits for A

Each session is “doing the right thing” for its own transfer. Together they create a cycle. This is not a schema bug like a missing CHECK (Day 29). It is a scheduling bug: lock acquisition order differs across code paths.

The mechanism: wait-for graph + detector

PostgreSQL does not check for deadlocks on every lock. After a backend has waited about deadlock_timeout (default 1 second), the detector runs. It is relatively expensive, so the timeout avoids false work on ordinary short waits. If a cycle exists, one transaction is aborted; the other can acquire the lock and continue.

Official docs are blunt: applications should avoid deadlocks when possible by acquiring locks in a consistent order. When a deadlock still happens, treat it as a retriable error — not a permanent data failure.

Defense 1: consistent lock order (worked helper)

If every code path locks min(id) before max(id), two transfers on the same pair cannot form a cycle. One waits; the other finishes. That is a plain lock wait — Day 24’s world — not a deadlock.

// One shared helper — transfer AND refund call this
function lockPair(a, b) {
  const [lo, hi] = a < b ? [a, b] : [b, a];
  // always: lo first, then hi
  return [lo, hi];
}

// Session A (pay B) and Session B (pay A) both:
const [lo, hi] = lockPair(fromId, toId);
BEGIN;
SELECT * FROM accounts WHERE id = lo FOR UPDATE;
SELECT * FROM accounts WHERE id = hi FOR UPDATE;
-- transfer …
COMMIT;

Interview trap: “we lock the source account first” is not consistent if source and destination swap roles across endpoints. Sort the keys, not the business roles.

Defense 2: short transactions + retry the victim

Long transactions hold locks longer, which multiplies the chance of cycles with other writers (pools from Day 8 make concurrent backends easy). Keep the critical section small. When you see 40P01 / deadlock detected, retry the whole transaction with backoff — same family as Serializable retries (Day 10) and idempotent side effects (Day 30) if the transfer can be replayed safely.

// Sketch — app layer
for (let attempt = 0; attempt < 5; attempt++) {
  try {
    await transferTx(from, to, amount); // one BEGIN…COMMIT
    return;
  } catch (e) {
    if (e.code !== "40P01") throw e; // not a deadlock
    await sleep(fullJitter(attempt)); // Day 37
  }
}
throw new Error("deadlock retries exhausted");

Wait vs cycle vs constraint (decision table)

Symptom	Graph shape	Engine action	App fix
Plain lock wait	Line: B → A	B sleeps until A ends	Usually none; keep txs short
Deadlock	Cycle: A ↔ B	Abort one victim (40P01)	Consistent order + retry
lock_timeout	Line (you gave up)	Error after your budget	Raise timeout or shrink hold
CHECK / EXCLUDE / FK	No wait-for graph	Reject illegal state	Fix data / use Day 34 deferral only when intended

Forward link: queues and SKIP LOCKED (Day 38)

Job workers that SELECT … FOR UPDATE the same queue head without SKIP LOCKED pile into a wait line (or worse, multi-row lock orders). Day 38 is the queue-shaped escape hatch: skip locked rows so workers claim different jobs instead of forming a convoy. Deadlocks still matter when workers touch shared business rows in opposite order — order helpers still apply.

Benchmark — real cycle on PostgreSQL 18.4

Two concurrent psql sessions against Podman til-postgres. Opposite order vs consistent order vs plain wait. 30 runs each, median wall time. Lab sets session-local deadlock_timeout = 200ms so detection is visible; production default is 1s.

Scenario	Result (30 runs)	Median wall ms
Opposite order (A: 1→2, B: 2→1)	30/30 deadlock detected	1108.880
Consistent order (both 1→2)	30/30 both committed · 0 deadlocks	850.595
Plain lock wait (A holds 1 ~150 ms, B waits)	30/30 both committed · 0 deadlocks	834.842

Headline: opposite order deadlocked every trial. Same two rows with consistent order never deadlocked. Plain wait never deadlocked. The engine’s job is cycle detection; the app’s job is lock order + retry.

What breaks? — Anti-patterns

1. Different lock orders in different endpoints. “Transfer” locks A then B; “refund” locks B then A. Fix: one shared helper that always sorts ids.

2. Treating deadlock as a hard failure. The victim is rolled back; the business operation may still be valid on retry. Fix: catch 40P01, retry with backoff + idempotency (Day 30).

3. Giant multi-row transactions “for consistency.” More locks × longer hold time = more cycles with other writers. Fix: shrink the critical section; prefer row-level order over table locks.

4. Confusing lock wait with deadlock. One blocked backend is not a cycle. Fix: read the error / pg_locks wait edges before “tuning deadlock_timeout.”

How it connects

• Day 10 (ACID / isolation): Serializable can also abort; deadlocks are another retriable abort class under locking.
• Day 24 (MVCC): plain SELECT does not block writers; FOR UPDATE does — and multi-row FOR UPDATE order creates cycles.
• Day 29–31, 34 (constraints): integrity failures reject illegal state; deadlocks reject a stuck schedule.
• Day 30 (idempotency): retries after deadlock need a key so side effects do not double-apply.
• Day 35 (circuit breaker): fail-fast protects callers from a dead dependency; deadlock retry protects writers from a stuck lock graph.

Transfer questions

What you should be able to do

• Draw a two-transaction wait-for cycle on two rows.
• Explain why PostgreSQL aborts one victim instead of waiting forever.
• Name deadlock_timeout as the delay before the expensive check (default 1s).
• Apply consistent lock order as the primary defense.
• Treat deadlock detected as retriable, with idempotent side effects when needed.

Quiz

Your teach step

Close this lesson. Write the “Explain like I’m 10” and the “60-second LinkedIn version” from memory. Focus on: wait-for cycle vs plain wait, detector + victim, consistent lock order, and the 30/30 lab. Post it, and paste the link.

Questions? Ask the agent — multi-row lock graphs, advisory locks, or how SKIP LOCKED changes queue workers are fair game.