How to Quickly Diagnose MySQL InnoDB Deadlocks in 3 Simple Steps

1. Locate and enable deadlock logging

Three common ways to obtain deadlock information are:

Check the MySQL error log.

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

Ask your DBA for the latest deadlock report.

Make sure the innodb_print_all_deadlocks variable is enabled:

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

If it returns OFF, enable it: SET GLOBAL innodb_print_all_deadlocks = 1; After enabling, deadlock details are written to the MySQL error log, typically at /usr/local/mysql/data/mysqld.local.err.

2. Analyze the deadlock log

Extract three key pieces of information from each deadlock entry:

Timestamp – when the deadlock occurred.

Transaction order – compare transaction IDs; larger IDs were started later.

Lock location and SQL statement – the SQL that was executing and the exact lock that was waiting or held.

Example excerpt (formatted for readability):

2025-04-19 13:39:45 0x3079da000
*** (1) TRANSACTION:
TRANSACTION 10047, ACTIVE 10 sec starting index read
... DELETE FROM t1 WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `itsuka`.`t1` lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 10048, ACTIVE 21 sec starting index read
... DELETE FROM t1 WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `itsuka`.`t1` lock_mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `itsuka`.`t1` lock_mode X locks rec but not gap waiting

This shows Transaction 1 (ID 10047) executing a DELETE and waiting for an exclusive lock on the primary‑key row, while Transaction 2 (ID 10048) already holds a shared lock on the same row and is also waiting for an exclusive lock.

3. Determine the deadlock cause

InnoDB creates a lock structure for each transaction; even locks that are not yet granted exist in memory. The deadlock arises because:

Transaction 2 acquires a shared (S) lock on the row.

Transaction 1 requests an exclusive (X) lock and is blocked.

Transaction 2 then requests an X lock on the same row and is blocked by Transaction 1’s S lock.

Both lock requests conflict, creating a circular wait.

Lock type reference

Record lock (LOCK_REC_NOT_GAP) : lock_mode X locks rec but not gap Gap lock (LOCK_GAP) : lock_mode X locks gap before rec Next‑key lock (LOCK_ORNIDARY) : lock_mode X Insert intention lock (LOCK_INSERT_INTENTION) :

lock_mode X locks gap before rec insert intention

4. Special cases that can trigger deadlocks

MySQL may acquire implicit locks that are not obvious from the SQL statements, such as:

Shared locks on rows when checking for unique‑key conflicts during INSERT or INSERT … ON DUPLICATE KEY UPDATE.

Exclusive locks on rows that are being updated as part of duplicate‑key handling.

These hidden locks can combine with explicit locks and cause deadlocks even for simple statements.

For a complete list of InnoDB lock behaviours, see the official documentation:

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

5. Practical steps to resolve a deadlock

1. Locate the deadlock log and ensure innodb_print_all_deadlocks is enabled.

2. Identify the timestamp, transaction IDs, and the SQL statements involved.

3. Map the lock_mode strings to lock types (record, gap, next‑key, insert‑intention) to understand which rows are contested.

4. Typical corrective actions include:

Reordering statements to avoid lock‑order inversion.

Adding or adjusting indexes so that rows are accessed in a consistent order.

Changing the transaction isolation level (e.g., from REPEATABLE READ to READ COMMITTED) if appropriate.

Splitting large transactions into smaller ones to reduce lock hold time.