Analyzing MySQL Deadlock Logs: A Step-by-Step Guide

The article introduces how to interpret MySQL InnoDB deadlock logs to diagnose deadlock causes.

It begins by creating a test table ty with columns id, a, b, and indexes.

CREATE TABLE `ty` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idxa` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

Sample data is inserted.

Two concurrent transactions cause a deadlock: Transaction 1 runs delete from ty where a=5; Transaction 2 runs insert into ty(a,b) values(2,10).

The deadlock log from SHOW ENGINE INNODB STATUS is examined, showing lock waits and held locks.

Key lock modes and attributes are explained: LOCK_X, LOCK_S, LOCK_REC_NOT_GAP, LOCK_GAP, LOCK_ORDINARY (Next-Key lock), LOCK_INSERT_INTENTION, and how they combine (e.g., lock_mode X waiting, lock_mode X locks gap before rec insert intention waiting).

The analysis shows that Transaction 1 holds an IX table lock and waits for an X lock on index idxa (a Next-Key lock), while Transaction 2 holds an X lock on the same index gap and waits for an insert intention lock, creating a classic deadlock.

The article concludes that understanding lock modes, compatibility, and the exact SQL statements is essential for deadlock analysis.

A short excerpt of the deadlock log is shown below:

LATEST DETECTED DEADLOCK ------------------------ 2017-09-09 22:34:13 7f78eab82700 *** (1) TRANSACTION: TRANSACTION 462308399, ACTIVE 33 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating delete from ty where a=5 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting *** (2) TRANSACTION: TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2 MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update insert into ty(a,b) values(2,10) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (1)