What happens when two transactions deadlock?

A deadlock is a traffic jam where everyone's blocking everyone and no one is yielding: T1 holds A and wants B; T2 holds B and wants A. Both wait forever — unless something breaks the cycle. The database's answer is a small piece of graph theory running every second: build the wait-for graph, find the cycle, kill one participant. Here's the exact sequence, in Postgres terms (InnoDB's variant noted where it differs).

Step 1 — the two requests

-- T1                          -- T2
BEGIN;                         BEGIN;
UPDATE accounts SET bal=bal-10 WHERE id=1;   -- T1: lock row 1
                               UPDATE accounts SET bal=bal-10 WHERE id=2;  -- T2: lock row 2
UPDATE accounts SET bal=bal+10 WHERE id=2;   -- T1: wants row 2 → WAIT
                               UPDATE accounts SET bal=bal+10 WHERE id=1;  -- T2: wants row 1 → WAIT

Every UPDATE takes a row lock (Postgres: a lock on the row version in the heap, plus the tuple header's xmax/xmin machinery; InnoDB: a record lock). T1's first statement committed its intent instantly — locks are held until transaction end. Now T1 is a waiter on row 2, T2 a waiter on row 1. Neither can proceed; neither's locks can be released. Classic cycle.

Step 2 — the wait

The blocked statement sits in the lock manager's wait queue for that object — it's not spinning, it's asleep, woken when the lock becomes grantable (ProcSleep → wait event Lock:transactionid). The transaction itself is otherwise frozen: no new work, all its earlier locks still held. This is the important part: a waiting transaction doesn't time out on its own (well — it can, with lock_timeout/innodb_lock_wait_timeout, but by default Postgres waits forever).

Step 3 — the detector wakes

Every deadlock_timeout (default 1 second in Postgres), the waiters' handle_timeout runs the detector: DeadLockCheck(). It builds the wait-for graph: nodes are transactions, edges are "A waits for a lock held by B." InnoDB skips the timer for simple cases — it checks for cycles on every lock wait, which finds deadlocks instantly (and is why InnoDB aborts immediately where Postgres waits ~1s).

Step 4 — the cycle

The graph has a cycle: T1 → T2 → T1. (Real systems: cycles of 2 are common; cycles of 5+ happen with multi-statement transactions and nested locks — advisory locks, tuple locks, and page locks all participate.) The detector also checks edge cases like "waits for a lock the other already got granted meanwhile" — the graph is current or the check is redone. Timeout-fired checks in Postgres also take the opportunity to just time out long waiters (lock_timeout).

Step 5 — choosing the victim

Postgres picks the victim by rolling back the transaction that cost least to roll back: roughly, the youngest transaction (xmax newer) and, beyond that, the one whose rollback undoes the least work. InnoDB picks the one that holds the fewest locks (smallest undo). The choice is deliberately cheap, not fair: any victim is better than a deadlock that never ends. The victim gets an error; the survivor gets the lock.

Step 6 — abort and error

The victim transaction is aborted: locks released, work rolled back (see the transaction-abort trace), and the client receives:

ERROR:  deadlock detected
DETAIL:  Process 41204 waits for ShareLock on transaction 94513; blocked by process 41207.
DETAIL:  Process 41207 waits for ShareLock on transaction 94512; blocked by process 41204.
HINT:  See server log for query details.

(SQLSTATE 40P01.) The deadlock error is not a bug signal in the database sense — it's the designed outcome of the designed detector. The survivor's blocked statement proceeds. Meanwhile the waiting machinery resets: the detector re-arms, and the next deadlock gets the same treatment a second later.

Step 7 — the aftermath

Deadlocks are expected at any real concurrency level; the design expects retry at the application level: catch 40P01, retry the whole transaction (idempotent design required). Databases do not retry for you. What they give you instead is tools to make deadlocks rare: consistent lock ordering (always update in primary-key order), short transactions (fewer locks held → fewer cycles), and indexes on FK constraints (row-level locks on the parent propagate to child checks).

What it costs

• Detection cadence: 1s (Postgres default) — a deadlock costs you ~1 second of wall time before it's even noticed.
• Per-check cost: building the wait-for graph is O(waiters + edges); on a busy server with thousands of waiters, DeadLockCheck can show up in perf — it's bounded, not free.
• InnoDB: instant detection at high contention costs CPU per wait; the innodb_deadlock_detect=OFF escape hatch exists for it.
• The real cost is after: victim rollback re-runs its statements' undo — proportional to work done.

-- from the server log — the exact, retryable diagnosis
LOG:  process 41204 still waiting for ShareLock on transaction 94513 after 1000.124 ms
DETAIL:  Process holding the lock: 41207. Wait queue: 41204.
LOG:  deadlock detected, process 41204 aborted
DETAIL:  Process 41204 waited for ShareLock on transaction 94513, blocked by 41207.
          Process 41207 waited for ShareLock on transaction 94512, blocked by 41204.

The full transcript is in the log, always — process IDs, locks, wait direction. That transcript is your app's bug report: two statements, opposite orders, one retryable victim. Fix the ordering in code, and the 40P01s become a zero-count log line instead of a support ticket.