Deadlocks
Two transactions can each hold a lock the other needs. With MySQL 8.4 InnoDB's default innodb_deadlock_detect=ON, detection breaks a detected cycle by rolling back a victim. It does not wait for the row-lock timeout. The manual documents the setting, detection limits, and exceptions involving other engines or MySQL table locks.
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 row-lock timeout with innodb_rollback_on_timeout=OFF, this deadlock rolls back B's entire transaction — there is nothing left to ROLLBACK.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Here B receives 1213 (SQLSTATE 40001), and the final balances prove B's earlier debit was undone. The entire transaction rolled back. If the operation is still required, retry it from the start, including its reads; apply a bounded retry policy. The victim is not an application choice: InnoDB tries to select a small transaction, measured by rows inserted, updated, or deleted, rather than promising a particular victim.
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…In this schedule B waits before holding any record lock 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.11 · Run it yourself · Scenario source
Both transfers acquire the two primary-key rows in the same order before changing them. In this schedule B waits and both transfers commit. Consistent acquisition order reduces cycles among those participating writers; this demonstration does not prove that arbitrary queries, other indexes, foreign-key checks, or metadata locks cannot deadlock.
The handling manual describes SHOW ENGINE INNODB STATUS for the latest deadlock and innodb_print_all_deadlocks=ON for logging detected deadlocks. These diagnostics are Documented contracts, not assertions in the two transcripts. If a wait is not a detected deadlock, identify its blocker.
Further reading
The reconstructed support assessment records the exact manual support and execution limits for this lesson.