Why Concurrent Inserts Trigger MySQL Deadlocks: An In‑Depth InnoDB Lock Analysis

Introduction

This article analyses a deadlock that occurs in MySQL 5.6.24 (transaction isolation level REPEATABLE READ) when two sessions execute concurrent batch INSERT statements that target a unique index.

Table schema

CREATE TABLE `tc` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto_increment ID',
  `c1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c2` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c3` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c4` tinyint(4) NOT NULL DEFAULT '0',
  `c5` tinyint(4) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00',
  `deleted_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_cid_bid_dt_tid` (`c1`,`c2`,`deleted_at`,`c3`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4;

Test case

The following SQL statements simulate the concurrent inserts. Session 2 (T1/T3) runs the first and third statements, while Session 1 (T2/T4) runs the second and the implicit deadlock point.

-- Session 2 (T1)
INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
  (95529, 4083702165, 3549694, NOW(), 1, 5),
  (95529, 4083702165, 3544063, NOW(), 1, 5),
  (95529, 4083702165, 3078355, NOW(), 1, 5);

-- Session 1 (T2)
INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
  (95529, 4083702165, 3549685, NOW(), 1, 4),
  (95529, 4083702165, 3549694, NOW(), 1, 4);

-- Session 2 (T3)
INSERT IGNORE INTO tc (c2, c1, c3, created_at, c4, c5) VALUES
  (95529, 4083702165, 3549691, NOW(), 1, 5);

-- Session 1 (T4) – deadlock point (implicit)

Lock log excerpt

2018-04-01 21:41:34 0x7f75c8bff700
*** (1) TRANSACTION:
TRANSACTION 2004, ACTIVE 6 sec inserting
LOCK WAIT 2 lock struct, heap size 1136, 1 row lock(s), undo log entries 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 2004 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 1999, ACTIVE 16 sec inserting
3 lock struct, heap size 1136, 2 row lock(s), undo log entries 4
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 1999 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 4 n bits 72 index uniq_cid_bid_dt_tid of table `test`.`tc` trx id 1999 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

Locking mechanism for INSERT

When an INSERT touches a unique index InnoDB acquires locks in two phases:

Phase 1 – Uniqueness check : a shared lock ( LOCK_S + LOCK_ORDINARY) is taken on the index entry.

Phase 2 – Row insertion : after the row is written, InnoDB holds a gap lock ( LOCK_INSERT_INTENTION) to prevent other sessions from inserting a conflicting key.

After a successful insert the transaction keeps LOCK_X + LOCK_REC_NOT_GAP on the index record. A concurrent session that tries to insert the same key must first obtain a S Next‑key Lock. If the first transaction still holds LOCK_REC_NOT_GAP, the second session waits, which appears in the log as “lock mode S waiting”. Even under REPEATABLE READ, Next‑Key locks are created for unique‑key checks, so they can block concurrent inserts.

Lock compatibility matrix

INSERT‑INSERT operations do not conflict. GAP and Next‑Key locks block INSERTs. GAP and Record locks do not conflict. Record‑Record and Record‑Next‑Key locks conflict with each other. Existing INSERT locks do not block newly requested locks. Held GAP locks block incoming INSERT_INTENTION locks.

Step‑by‑step deadlock formation

T1 (Session 2) inserts three rows. After each row InnoDB holds LOCK_X + LOCK_REC_NOT_GAP on the unique index entry.

T2 (Session 1) attempts to insert two rows that conflict with the key held by T1. It requests a S Next‑key Lock, which conflicts with T1’s exclusive lock, producing the “lock mode S waiting” entry.

T3 (Session 2) inserts another row whose key is adjacent to the one held by T1. It acquires an INSERT_INTENTION lock, but this lock is blocked by the gap lock held by T1.

The three locks form a circular wait:

T1 holds LOCK_REC_NOT_GAP and blocks T2.

T2 holds S Next‑key Lock and blocks T3.

T3 waits for the gap lock released by T1.

InnoDB detects the deadlock and rolls back one of the transactions (Transaction 2004 in the log).

Mitigation strategies

There is no universal SQL‑level fix for this pattern. Practical approaches include:

Redesigning the unique index to reduce contention (e.g., adding a non‑overlapping prefix or using a surrogate key).

Splitting bulk data loads into smaller batches or sharding the data so that concurrent inserts target different index ranges.

Reordering the insert statements to avoid overlapping unique‑key values.

All options require changes to the application logic or data model.

Conclusion

The deadlock is caused by two concurrent INSERT statements that target adjacent values of a unique index. InnoDB’s lock acquisition order (shared lock for uniqueness check, followed by exclusive and gap locks) together with the lock compatibility rules creates a circular wait. Understanding these lock semantics is essential for diagnosing and preventing similar deadlocks in MySQL.