Quick 3‑Step Guide to Locate and Analyze MySQL InnoDB Deadlocks

1. Find and Confirm Your Deadlock Log

You can obtain the deadlock information in three ways:

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 record.

If the variable innodb_print_all_deadlocks is OFF, enable it so that every deadlock is written to the error log:

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

SET GLOBAL innodb_print_all_deadlocks = 1;

After enabling, examine the error file (for example /usr/local/mysql/data/mysqld.local.err ) for the recorded deadlock.

2. Analyze the Deadlock Log

Typical log excerpt (simplified):

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

From the log you should determine:

When the deadlock occurred – the timestamp at the beginning of the entry.

The order of the transactions – compare the transaction IDs (larger IDs are started later).

Where the deadlock happened and which SQL statements were involved – look for lines such as DELETE FROM t1 WHERE i = 1 and the lock‑wait description.

Example of lock‑wait information:

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

This indicates that transaction 10047 is waiting for an exclusive record lock on the primary‑key row with value 1 (hex 80000001 ).

The log also shows the lock held by the other transaction:

*** (2) HOLDS THE LOCK(S): RECORD LOCKS ... lock mode S locks rec but not gap

Thus transaction 10048 holds a shared lock on the same row.

3. Determine the Deadlock Reason

Summarising the above:

Transaction 1 (ID 10047) holds no lock yet and requests an exclusive lock on row 1.

Transaction 2 (ID 10048) already holds a shared lock on row 1 and then also requests an exclusive lock on the same row.

Because InnoDB uses a fair lock queue, each transaction must wait until all conflicting requests ahead of it are released. Transaction 1 waits for the shared lock held by Transaction 2, while Transaction 2 waits for its own exclusive request to be granted after Transaction 1’s request, creating a circular wait.

The root cause is the classic “S‑lock then X‑lock” conflict on the same primary‑key record.

4. Extension – Special Locking Situations That Can Cause Deadlocks

MySQL may acquire hidden locks that are not obvious from the SQL text, such as:

Unique‑key checks that first acquire a shared next‑key lock before deciding whether to raise a duplicate‑key error.

INSERT … ON DUPLICATE KEY UPDATE statements that, after detecting a conflict, convert the shared lock into an exclusive lock for the update.

Understanding these implicit locks helps you diagnose deadlocks that arise from statements other than explicit SELECT … FOR UPDATE or DELETE operations.

For more details on InnoDB lock types, refer to the official MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html .

Author: Huang Jingqian, Java Development Engineer at XianKeHui.