Unraveling a Mysterious MySQL Deadlock: Step‑by‑Step Log Analysis

Background

While most deadlocks the author had seen were caused by inconsistent lock order during batch updates, a recent incident presented a more obscure scenario. The author revisited MySQL deadlock fundamentals and, after research and discussion, identified the root cause, demonstrating why backend developers benefit from basic deadlock‑tracing skills.

Test Environment and Schema

The experiment uses MySQL 5.5 with the default Repeatable‑Read isolation level and InnoDB engine. The simulated table test is defined as:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

Sample rows:

+----+------+
| id | a    |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  4 | 4    |
+----+------+

Deadlock Scenario

The two transactions execute the following steps:

Transaction 2: BEGIN Transaction 2: DELETE FROM test WHERE a = 2; Transaction 1: BEGIN Transaction 1: DELETE FROM test WHERE a = 2; (blocked)

Transaction 1 receives error

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

while Transaction 2 attempts

INSERT INTO test (id, a) VALUES (10, 2);

Inspecting the InnoDB Deadlock Log

Running SHOW ENGINE INNODB STATUS; yields the following relevant excerpt (formatted for readability):

*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
... LOCK WAIT ...
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting

*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
... 
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index `a` ... lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... lock mode S waiting

Log Interpretation

Transaction 1 is waiting for an exclusive (X) lock on the unique index a because its DELETE needs to modify the row where a = 2. Transaction 2 already holds that X lock (record lock) from its earlier DELETE. Transaction 2’s subsequent INSERT must first check for duplicate keys on the unique index, which requires a shared (S) lock. Since the S lock is queued behind the pending X lock from Transaction 1, a circular wait forms, causing the deadlock.

Deadlock Flowchart (Textual)

Transaction 2 begins and successfully deletes the row with a = 2, acquiring an X record lock.

Transaction 1 begins and attempts the same DELETE, but must wait because Transaction 2’s X lock blocks it.

Transaction 2 proceeds to INSERT a new row with a = 2. It first requests an S lock for duplicate‑key checking, which is placed after Transaction 1’s pending X lock.

Both transactions now wait for each other’s lock, forming a cycle; InnoDB chooses the lower‑weight transaction (Transaction 1) to roll back.

Extended Scenario

Under high concurrency, a variant deadlock can appear where Transaction 2’s waiting lock changes from S to X (gap‑before‑insert‑intention). The steps are similar, but the lock upgrade from S to X during the second phase of the INSERT creates another circular wait.

Conclusion

Effective deadlock troubleshooting starts with reading the InnoDB status log to identify the lock types and order each transaction holds or requests. By mapping those details to the SQL statements, developers can reconstruct the circular‑wait chain and pinpoint the exact cause, enabling them to redesign queries or adjust isolation levels to avoid recurrence.