Understanding InnoDB Deadlock Detection and Resolution in MySQL 8.0

The article is based on MySQL 8.0.32 source code with the InnoDB storage engine and examines the behavior of the deadlock detection thread.

1. Does the deadlock detection thread always run?

The thread checks and resolves deadlocks only when the system variable innodb_deadlock_detect is ON (default). If it is OFF, the thread skips the check, but its preparation work—such as initializing weights for waiting transactions—still influences which transaction acquires a lock first.

2. Finding a deadlock cycle

The detection thread uses the lock‑wait array built during preparation to traverse possible wait paths. Starting from each array element, it follows the wait relationships; if it returns to the starting element, a deadlock cycle is found.

Example SQL statements and the corresponding lock‑wait array are shown below.

-- Session 1 (Transaction 1)
BEGIN;
SELECT id FROM t1 WHERE id = 10 FOR UPDATE;

-- Session 2 (Transaction 2)
BEGIN;
SELECT id FROM t1 WHERE id = 20 FOR UPDATE;

-- Session 3 (Transaction 3)
BEGIN;
SELECT i1 FROM t1 WHERE id = 10 FOR UPDATE;

-- Session 4 (Transaction 4)
BEGIN;
SELECT id, i1 FROM t1 WHERE id = 10 FOR UPDATE;

-- Session 1 (Transaction 1)
SELECT i1 FROM t1 WHERE id = 20 FOR UPDATE;

-- Session 2 (Transaction 2)
SELECT * FROM t1 WHERE id = 10 FOR UPDATE;

Lock‑wait array:

/* lock‑wait array */
[
  0: 1, /* txn 1 waits for txn 2 */
  1: 0, /* txn 2 waits for txn 1 */
  2: 0, /* txn 3 waits for txn 1 */
  3: 0  /* txn 4 waits for txn 1 */
]

Step‑by‑step traversal shows that transaction 1 waits for transaction 2, which in turn waits for transaction 1, forming a deadlock cycle.

3. Secondary verification

After a cycle is found, the thread must confirm that the cycle still exists. It examines the snapshot array to ensure each involved transaction still holds its slot and is still waiting.

/* snapshot array */
/* 0 */ { txn 3, txn 1, srv_slot_t 0, 4 }
/* 1 */ { txn 4, txn 1, srv_slot_t 1, 5 }
/* 2 */ { txn 1, txn 2, srv_slot_t 2, 6 }
/* 3 */ { txn 2, txn 1, srv_slot_t 3, 7 }

If any slot has been released or the transaction is no longer waiting, the deadlock is considered resolved and no further action is taken. Only when all transactions pass both checks does the thread proceed to resolve the deadlock.

4. Summary

The deadlock detection thread’s activity is controlled by innodb_deadlock_detect . When enabled, it iterates over each entry of the lock‑wait array, looks for cycles, and then performs the two‑step secondary verification to ensure the cycle still exists before attempting resolution.