40P01 — deadlock detected

PostgreSQL raises 40P01 when deadlock detection rejects an attempt to break a lock cycle; recover the failed block and retry the complete operation with fresh decisions or return controlled failure.

Reproduce it

Two sessions lock the same two rows in opposite order. The -- [A] and -- [B] markers say who runs each statement, top to bottom.

-- [A] lock alice's row
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

-- [B] lock bob's row
BEGIN;
UPDATE accounts SET balance = balance - 25 WHERE id = 2;

-- [A] now reach for bob's row: B holds it, so A waits
UPDATE accounts SET balance = balance + 10 WHERE id = 2;

-- [B] now reach for alice's row: A holds it, and the cycle closes
UPDATE accounts SET balance = balance + 25 WHERE id = 1;

B's last statement receives 40P01 under the full Scenario's unequal deadlock_timeout settings. B's failed block releases its conflicting locks and A's pending UPDATE completes. The victim is not universally B, and the example does not measure a minimum detection latency. Full rollback or valid savepoint recovery ends the failed-block condition; complete-operation retry still requires fresh reads/decisions, a bound, and external-effect handling. Neither successful retry nor every survivor's eventual commit is guaranteed by detection.

Run it yourself

The Deadlocks lesson renders this exact scenario as a verified transcript, including the trick that makes the victim deterministic. To see why the waits line up the way they do, read Lock queues; to spot a real cycle while it's happening, Monitoring locks shows the view that names who blocks whom.

The MySQL side

With InnoDB deadlock detection enabled, the corresponding opposite-order schedule produces 1213 and transaction rollback. Detection can be disabled. Timing, victim selection, error representation, and recovery scope are separate engine/configuration conditions, not a universal mirror.