How to Quickly Diagnose MySQL InnoDB Deadlocks in 3 Simple Steps

1. Locate and Identify the Deadlock Log

There are three common ways to obtain the deadlock log:

Method 1: Check the MySQL error log.

Method 2: Run SHOW ENGINE INNODB STATUS and look for the "LATEST DETECTED DEADLOCK" section.

Method 3: Ask your DBA to provide the recent deadlock information.

If the innodb_print_all_deadlocks variable is OFF, enable it:

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

SET GLOBAL innodb_print_all_deadlocks = 1;

After enabling, the deadlock information will be written to the MySQL error log, typically found at /usr/local/mysql/data/mysqld.local.err.

2. Analyze the Deadlock Log

Once you have the log, extract the following details:

Determine the exact timestamp of the deadlock.

Identify the order in which the transactions were executed.

Find the SQL statements and the locked rows involved.

Example log excerpt:

2025-04-19 13:39:45 0x3079da000
*** (1) TRANSACTION:
TRANSACTION 10047, ACTIVE 10 sec starting index read
... (details omitted) ...
*** (2) TRANSACTION:
TRANSACTION 10048, ACTIVE 21 sec starting index read
... (details omitted) ...
*** WE ROLL BACK TRANSACTION (1)

From this we can see:

Transaction 10047 started earlier (10 seconds) and was waiting for an exclusive lock on the primary key record with value 1.

Transaction 10048 started later (21 seconds), already holds a shared lock on the same record, and also attempts to acquire an exclusive lock.

Key observations about transaction IDs:

In MySQL, a transaction ID is assigned only when the transaction performs its first DML operation (INSERT/UPDATE/DELETE). Pure read‑only transactions may run longer without receiving an ID.

A larger transaction ID indicates a later allocation, not necessarily a later start time.

The lock information shows that both transactions are contending for the primary‑key index lock on record 1:

RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `itsuka`.`t1`
lock_mode X locks rec but not gap waiting

Lock type keywords:

LOCK_REC_NOT_GAP → lock_mode X locks rec but not gap LOCK_GAP → lock_mode X locks gap before rec LOCK_ORNIDARY (next‑key) → lock_mode X LOCK_INSERT_INTENTION → lock_mode X locks gap before rec insert intention Parsing the physical record shows the primary‑key value ( 1) stored as 0: len 4; hex 80000001.

3. Determine the Root Cause

Transaction 1 holds no lock yet and is waiting for an exclusive lock on the primary‑key record 1. Transaction 2 already holds a shared lock on the same record and also tries to acquire an exclusive lock, creating a circular wait:

T2 acquires a shared lock (S) on the record.

T1 requests an exclusive lock (X) and is blocked by T2’s S lock.

T2 then requests an X lock and is blocked by T1’s pending X request.

This classic deadlock arises because each transaction is waiting for the other to release its lock.

Reconstructing the timeline:

T2: BEGIN; SELECT * FROM t1 WHERE i=1 LOCK IN SHARE MODE; T1: BEGIN; DELETE FROM t1 WHERE i=1; Both transactions target the same primary‑key row, leading to the deadlock.

4. Extended Cases: Special Locking Situations

MySQL may acquire hidden locks that also cause deadlocks, such as:

When a unique‑key conflict occurs during INSERT or UPDATE, MySQL first places a shared next‑key lock on the conflicting record to check its validity.

If the INSERT uses ON DUPLICATE KEY UPDATE, the subsequent update requires an exclusive lock, potentially leading to a deadlock.

For a complete list of lock types and their behavior, refer to the official documentation:

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

.

Understanding that every lock request creates a lock structure in memory, regardless of whether the lock is eventually granted, helps explain why deadlock logs show both waiting and held locks.