Deadlocks
Two transactions, each holding a lock the other needs: neither can ever proceed. InnoDB detects the cycle the moment it closes — no timeout involved — and resolves it by rolling back one transaction, the victim, with errno 1213.
The classic: opposite lock order
A transfers 10 from alice to bob; B transfers 25 from bob to alice.
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1; -- A locks alice
Query OK, 1 row affected
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2; -- B locks bob
Query OK, 1 row affectedA now needs bob's row (B has it) — it waits.
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
⏳ A is waiting for a lock…B now needs alice's row (A has it). A waits for B, B waits for A: a cycle.
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1; -- ER_LOCK_DEADLOCK — B's whole transaction is rolled back…
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionThe victim's abort frees bob's row, so A's stuck UPDATE completes.
⏵ A resumes:
Query OK, 1 row affected
A> COMMIT;
Query OK
A> SELECT owner, balance FROM accounts ORDER BY id; -- A's transfer survived; B's evaporated — retry it
owner | balance
-------+---------
alice | 90
bob | 110
(2 rows)Unlike a lock timeout, a deadlock rolls back B's entire transaction — there is nothing left to ROLLBACK.
Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Note what 1213 did: unlike a lock timeout, it rolled back B's entire transaction — bob's -25 was undone too. The victim's only move is to retry from the top. (InnoDB rolls back whichever transaction changed the fewest rows — its measure of the cheaper one to undo — and you don't get to choose.)
The cure: consistent lock order
Both transfers grab all their row locks up front, ordered by id.
A> BEGIN;
Query OK
A> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
id
----
1
2
(2 rows)
B> BEGIN;
Query OK
B> SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
⏳ B is waiting for a lock…No cycle is possible: B parks at the first row and holds nothing A needs.
A> UPDATE accounts SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected
A> COMMIT;
Query OK
⏵ B resumes:
id
----
1
2
(2 rows)
B> UPDATE accounts SET balance = balance - 25 WHERE id = 2;
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 25 WHERE id = 1;
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT owner, balance FROM accounts ORDER BY id; -- both transfers landed — same workload, zero deadlocks
owner | balance
-------+---------
alice | 115
bob | 85
(2 rows)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Detection is instant and always on (innodb_deadlock_detect=ON): the victim takes errno 1213 (SQLSTATE 40001) and a full rollback, so retrying on 1213 is mandatory in any code that writes several rows under contention. Deadlocks aren't database bugs — they're a property of your access order, and locking rows in one global order (by primary key, say) makes cycles impossible, as the fix above shows. When one slips through anyway, SHOW ENGINE INNODB STATUS prints the last deadlock's full story and innodb_print_all_deadlocks=ON logs every one. PostgreSQL works the same way except it waits out a 1-second deadlock_timeout first and reports 40P01 (compare); either way, when a hang turns out not to be a deadlock, you'll want to see who's blocking whom.