40P01 — deadlock detected

PostgreSQL raises 40P01 on a deadlock, when two transactions each hold a lock the other needs: it breaks the cycle by aborting one of them so the other can finish, and the aborted transaction should retry.

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 fails with ERROR: 40P01: deadlock detected. Each transaction is waiting for a row the other has locked, so neither can ever proceed. PostgreSQL notices the cycle (after deadlock_timeout, one second by default) and aborts one of the two, here B, which frees bob's row and lets A's blocked update complete. The victim is arbitrary in production, so the transaction that loses simply retries.

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

MySQL is a true mirror here: the same opposite-order locking deadlocks InnoDB, which rolls one transaction back with 1213. The only difference worth knowing is that InnoDB detects the cycle instantly instead of waiting out a timeout.