1213 — Deadlock found

InnoDB deadlock detection can raise errno 1213, SQLSTATE 40001, and roll back a transaction; retry the complete operation with fresh reads and decisions under a bound, 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;

The full Scenario asserts deadlock detection enabled and observes B's 1213 and A's resumed update. InnoDB rolls back the victim's earlier transactional writes too; the final balances are asserted. It does not measure instantaneous detection or promise B is always the victim. Retry repeats the complete transaction, including decisions, and may exhaust its bound. Database rollback does not undo separately committed external effects. With detection disabled, conflicting waits can instead reach a timeout whose scope is different.

Run it yourself

The Deadlocks lesson renders this exact scenario as a verified transcript. Because a deadlock throws away the whole transaction, the retry has to replay every statement. Retrying deadlocks shows the loop that does it, and Lock queues explains why the waits stack up into a cycle.

The PostgreSQL side

The PostgreSQL 40P01 schedule also breaks an opposite-order cycle, but its failed-block recovery and configurable detection behavior differ. This example does not establish all cross-engine timing or victim-selection differences.