Analyzing MySQL InnoDB Deadlock Logs: A Detailed Walkthrough

1. Preparation

Create a test table and insert sample data, then enable deadlock logging by setting the global variables innodb_print_all_deadlocks = ON and log_error_verbosity = 3 :

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO `t1` (`id`, `i1`) VALUES
(10, 101), (20, 201), (30, 301), (40, 401);

SET GLOBAL innodb_print_all_deadlocks = ON;
SET GLOBAL log_error_verbosity = 3;

2. Deadlock Log

Run two concurrent sessions with the following SQL sequence to trigger a deadlock:

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

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

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

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

The MySQL error log will contain entries similar to the following (truncated for brevity):

2024-08-04T07:51:55.786277Z 0 [Note] [MY-012468] [InnoDB] Transactions deadlock detected, dumping detailed information.
2024-08-04T07:51:55.786346Z 0 [Note] [MY-012469] [InnoDB]   *** (1) TRANSACTION:
TRANSACTION 246047, ACTIVE 7 sec starting index read
... (more transaction, lock, and thread info) ...
2024-08-04T07:51:55.787944Z 0 [Note] [MY-012469] [InnoDB]   *** WE ROLL BACK TRANSACTION (2)

3. Detailed Analysis

3.1 Auxiliary Information

Each log line consists of five fields: timestamp, thread ID (always 0 for InnoDB auxiliary logs), log level (Note), error code (e.g., MY‑012468), and subsystem name (InnoDB). The auxiliary section also indicates the type of following information, such as (1) TRANSACTION for transaction details.

3.2 Transaction Information

Typical transaction data includes the transaction ID, state (ACTIVE), elapsed time, and operation info (e.g., starting index read ). It also shows the number of tables used and locked, lock‑wait structures, heap size, and the number of row locks held.

TRANSACTION 246047, ACTIVE 7 sec
  starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1192, 2 row lock(s)
MySQL thread id 8, OS thread handle 123145473822720, query id 136, localhost 127.0.0.1 root statistics
SELECT i1 FROM t1 WHERE id = 20 FOR UPDATE

3.3 Row Lock Information

Row‑lock entries describe the space ID, page number, bitmap size, index, transaction ID, lock mode (X for exclusive, S for shared), and whether the lock is a gap or record lock. Physical record details list field lengths, hex values, and the implicit InnoDB fields DB_TRX_ID and DB_ROLL_PTR .

RECORD LOCKS
  space id 0
  page no 46
  n bits 72
  index PRIMARY of table `test`.`t1`
  trx id 246047
  lock_mode X
  locks rec but not gap
Record lock, heap no 2
  PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  0: len 4; hex 0000000a; asc 10;;
  1: len 6; hex 00000003bf87; asc DB_TRX_ID;;
  2: len 7; hex 81000001890110; asc DB_ROLL_PTR;;
  3: len 4; hex 80000065; asc 101;;

Conversion of hex values to decimal can be done with shell arithmetic, e.g., echo $((0x0000000a)) yields 10 .

4. Summary

The article demonstrates how to enable detailed deadlock logging, reproduce a deadlock scenario, and interpret the rich auxiliary information provided by InnoDB, including transaction metadata, lock structures, and physical record contents, which is essential for diagnosing and resolving deadlocks in MySQL.