Why a Simple DELETE Triggers a MySQL Deadlock – Step‑by‑Step Log Analysis

Background

The author encountered a hard‑to‑understand deadlock on a MySQL 5.5 InnoDB table (default Repeatable‑Read isolation) that has a primary key id and a unique index on column a. Sample data contain three rows with values (1,1), (2,2) and (4,4).

Deadlock Cause

Two transactions run concurrently:

Transaction 2 starts and executes DELETE FROM test WHERE a = 2, acquiring an X (exclusive) record lock on the index entry a=2.

Transaction 1 starts, also tries to DELETE FROM test WHERE a = 2 and waits for the same X lock, entering the lock‑wait queue.

Transaction 2 then attempts INSERT INTO test (id,a) VALUES (10,2). Because a is uniquely indexed, MySQL first checks for duplicate keys, which requires an S (shared) lock on the same index entry. The S lock is placed behind the pending X lock from Transaction 1, creating a circular wait.

The circular wait causes InnoDB to detect a deadlock and roll back the lower‑weight transaction (Transaction 1).

Reading the Deadlock Log

Running SHOW ENGINE INNODB STATUS yields a log that can be split into two parts. The upper part shows Transaction 1 waiting for an X lock on index a. The lower part shows Transaction 2 holding that X lock and waiting for an S lock needed by the INSERT.

*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
... LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
... delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index `a` ... 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 ... index `a` ... lock mode S waiting

From this output we can see exactly which locks each transaction holds and which it is waiting for, allowing us to reconstruct the deadlock cycle.

Extended Scenario

Under high concurrency a variant appears where Transaction 2’s waiting lock changes from S to X ("lock_mode X locks gap before rec insert intention waiting"). The same circular‑wait pattern occurs, but the lock upgrade happens later in the INSERT process.

Conclusion

Effective deadlock troubleshooting requires reading the InnoDB status, identifying the lock types (record vs. gap, X vs. S), and mapping them to the SQL statements that generated them. By reversing this mapping you can pinpoint the exact sequence that creates the circular wait and resolve the underlying issue.