MySQL InnoDB Deadlock Analysis: Locking Behavior and Resolution

This article demonstrates how to reproduce and analyze a deadlock in MySQL 8.0.32 InnoDB using two concurrent transactions that delete rows with the same secondary index value, and explains the lock types, the lock‑wait graph, and why the deadlock occurs.

1. Preparation

Create a test table and insert sample data, then set the transaction isolation level to REPEATABLE‑READ.

CREATE TABLE `t_deadlock_1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT NULL,
  `i2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_i1` (`i1`)
) ENGINE=InnoDB;
INSERT INTO `t_deadlock_1` (`id`,`i1`,`i2`) VALUES
(22,2,3),(23,5,4),(24,6,7);
SET transaction_isolation='REPEATABLE-READ';

2. Lock Situation

Open two MySQL sessions, start two transactions, and execute the same DELETE statement on the secondary index idx_i1 where i1 = 5 .

-- session 1 (transaction 1)
BEGIN;
DELETE FROM t_deadlock_1 WHERE `i1` = 5;

-- session 2 (transaction 2)
BEGIN;
DELETE FROM t_deadlock_1 WHERE `i1` = 5;

Query the lock information from performance_schema.data_locks in session 1 to see the lock rows.

SELECT engine_transaction_id, object_name, index_name,
       lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE object_name='t_deadlock_1' AND lock_type='RECORD'\G

The result shows that transaction 1 holds an X‑mode lock on the record <i1 = 5, id = 23> (Next‑Key lock) and a GAP lock on the next record <i1 = 6, id = 24> . Transaction 2 is waiting for the same Next‑Key lock.

3. Deadlock Analysis

After the two DELETEs block each other, session 1 inserts a new row, which triggers the deadlock. The error returned is:

(1213, 'Deadlock found when trying to get lock; try restarting transaction')

Inspecting the latest deadlock log with SHOW ENGINE INNODB STATUS\G reveals that both transactions hold and wait for locks on the same secondary‑index record <i1 = 5, id = 23> . Because InnoDB does not allow a transaction to have two waiting locks simultaneously, the circular wait forms a deadlock, and transaction 2 is chosen as the victim.

4. Summary

If a transaction holds an exclusive Next‑Key lock on a record and no other transaction is waiting for that lock, it can insert a new row into the preceding gap without waiting.

If another transaction is waiting for the same record lock, the first transaction must wait for the waiting transaction to release the lock before it can obtain the insert intention lock.

For readability on mobile devices the author slightly reformatted the deadlock log and removed the transaction numbers.