How a deadlock forms: the waiting-for graph
A deadlock is a cycle in a directed graph. Transaction A holds L1 and waits for L2; transaction B holds L2 and waits for L1. InnoDB detects the cycle, rolls back the cheaper transaction (usually the one with fewer changes), and raises `ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction`.
Crucially, InnoDB detects deadlocks in real time — it does not wait for `innodb_lock_wait_timeout`. So a deadlock manifests as an instant failure, whereas a plain lock-wait timeout manifests as a 50-second stall followed by failure. If yours fails only after a long wait, you are almost certainly dealing with lock-wait timeout, and the investigation is entirely different: look for a long-running transaction holding locks.
There is a third case: a cycle exists but one side commits in the instant before detection, so nothing is reported and both succeed. Such near-misses leave no error at all and show up only as occasional latency spikes. If your application is intermittently slow with no deadlock log, suspect this.
Keeping that waiting-for graph in mind is what makes the report readable. Each `HOLDS THE LOCK` and `WAITING FOR` pair is one edge; joining two transaction blocks reconstructs the cycle. The next two sections show how to extract exactly that.
SHOW ENGINE INNODB STATUS\G
# 关键段落关键词：LATEST DETECTED DEADLOCK
# 该命令输出的是「最近一次」死锁，历史死锁不在这里Reading the deadlock report line by line
Within `LATEST DETECTED DEADLOCK` you will find one `*** (1) TRANSACTION:` block per transaction on the cycle. The first line gives the transaction ID and its position among MySQL threads; `TRANSACTION N ACTIVE N statement executing` names the statement it is running at that moment.
Next come the locks it holds, for example `RECORD LOCKS space id 5 page id 11 nbits 72 index PRIMARY of table `db`.`orders` trx id 12345 lock_mode X locks rec but not gap`. Three facts matter here: the lock mode (X = exclusive), that it sits on `index PRIMARY`, and the `trx id` that identifies the holder.
Finally you see what it waits for, such as `waiting for X lock on record number 9`. Match that against the `trx id` held by the other block and you have "who waits for whom". Pairing every block reconstructs the waiting-for graph, and the cycle becomes obvious.
Practical advice: do not plan on running `SHOW ENGINE INNODB STATUS` by hand. Deadlocks are instantaneous and by the time you finish typing, the report may already show a different one. Enable `innodb_print_all_deadlock = ON` so MySQL logs every occurrence, and alert on it from your log pipeline.
SET GLOBAL innodb_print_all_deadlock = ON;
SET GLOBAL innodb_deadlock_detect = ON; -- 默认即为 ON
SHOW VARIABLES LIKE "innodb_print_all_deadlock";Gap locks: why an unindexed update still deadlocks
Under the default `REPEATABLE READ` isolation level, InnoDB uses gap locks to prevent phantom reads. When an UPDATE cannot use an index, InnoDB does not lock just the matching rows — it locks every gap in the scanned range. Two otherwise unrelated transactions then wait on each other over the same span of the table.
That answers "why do we deadlock when updating different rows". If `idx_status` is missing, transaction A running `UPDATE t SET v=1 WHERE status=0` locks the entire scan range; transaction B updating a different row finds the whole range locked and queues. Touch the range in a different order from the two transactions and you have a cycle.
There are two levels of mitigation. First, **add the index**: once the predicate uses an index the lock set narrows to concrete rows, raising concurrency and removing the deadlock at its source. Second, **lower the isolation level**: if the business allows `READ COMMITTED`, InnoDB generally skips gap locks (except for uniqueness and foreign-key checks), which cuts deadlock probability substantially — after evaluating phantom-read exposure.
One practice worth adopting: any UPDATE whose WHERE clause cannot use an index is a red flag. Catch it in code review rather than in production alerts, using `EXPLAIN` on every high-frequency UPDATE — a `type` of `ALL` means the whole table is being scanned and locked.
EXPLAIN UPDATE orders SET v = 1 WHERE status = 0;
-- 关注 EXPLAIN 输出中的 type 列：ALL 表示全表扫描（危险）
ALTER TABLE orders ADD INDEX idx_status (status);
SHOW INDEX FROM orders;Access resources in a fixed order: the most effective rewrite
The primary cause of deadlocks is multiple resources being accessed in differing orders. Transaction A updates the orders table then the stock table; transaction B does the reverse. Run them concurrently and a deadlock window exists — this is not probabilistic, it is guaranteed.
The fix is to fix the order in the architecture: require that every multi-table transaction touches the lower-id (or alphabetically first) table first. Encode this in the development guidelines, enforce it in code review, and this entire class of deadlock disappears.
Batch updates need special care. `UPDATE orders SET status=2 WHERE id IN (1,3,5,7,...)` locks rows in primary-key order and is safe. But when the row set comes from a subquery or a JOIN, the actual locking order follows the execution plan and is **not** guaranteed to match the IN-list order. Add an explicit `ORDER BY` in the subquery to force it.
Another effective pattern is shortening transactions: the less time locks are held, the smaller the window for a cycle. Split large transactions, and never perform network calls or wait for user input inside one. The core principle is to finish cheap validation and external calls first, then open the database transaction.
UPDATE orders o JOIN (
SELECT id FROM orders WHERE account_id = ? ORDER BY id
) AS t ON o.id = t.id
SET o.status = 2;
-- 子查询里显式 ORDER BY，保证加锁顺序确定Prevention, retries and keeping evidence
Deadlocks are by design a normal event: they cannot be eliminated, only prevented and recovered from quickly. MySQL signals them with error code 1213, whose semantics are "the transaction was not committed, so replaying the whole transaction is safe". The application layer must therefore implement dead-lock retries — the database cannot do it for you.
Bounded retries with backoff are the norm. Catch 1213, replay the whole transaction, cap it at roughly three attempts, and use exponential backoff with random jitter. The jitter matters: two transactions retrying in lockstep will collide in lockstep again. Retries must replay the entire transaction, never just the failing statement, because the transactional context changed at rollback.
For long-term evidence, rely on configuration. `innodb_print_all_deadlock = ON` writes every occurrence to the error log, and Performance Schema lets you count deadlocks over time to confirm whether a change worked. For investigating long stalls, the `TRANSACTIONS` section of `SHOW ENGINE INNODB STATUS` lists active transactions running longer than zero seconds — a common cause of lock-wait timeouts.
Close the loop with monitoring. Chart deadlock counts per hour and align them with deployments: a sharp rise after a release almost always identifies the offending change. For live lock-wait analysis without application instrumentation, use `performance_schema.data_locks` together with `data_lock_waits`.
SET GLOBAL innodb_print_all_deadlock = ON;
SELECT count FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_lock_waits LIMIT 10;
SELECT ENGINE_TRANSACTION_ID, trx_started FROM information_schema.innodb_trx ORDER BY trx_started;