Why Three Concurrent INSERTs Trigger a Deadlock in InnoDB?

Introduction

Deadlocks are a fascinating and challenging technical problem that many DBAs and developers encounter. This article presents a concrete case where three concurrent INSERT statements lead to a deadlock, highlighting the role of the Insert Intention Gap Lock (also called Insert Intention Lock) in InnoDB.

Environment Setup

Database: Percona Server 5.6 running with the REPEATABLE READ isolation level.

Table definition:

CREATE TABLE `t6` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

Initial data (used to illustrate the lock behaviour):

INSERT INTO t6 VALUES
  (1,2),
  (2,8),
  (3,9),
  (4,11),
  (5,19);

Deadlock Log

The InnoDB deadlock report captured by SHOW ENGINE INNODB STATUS looks like this (relevant excerpts are shown):

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-18 10:03:50 7f78eae30700
*** (1) TRANSACTION:
TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB1
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update
insert into t6 values(1,2),(2,8),(3,9),(4,11),(5,19);
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308725 lock_mode X insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update
insert into t6(id,a) values(8,15)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

Locking Mechanism for INSERT

INSERT acquires locks in several stages:

Stage 1 – Unique‑key check: InnoDB requests a LOCK_INSERT_INTENTION (also known as Insert Intention Gap Lock).

Stage 2 – Gap lock after the row is found: It obtains LOCK_S + LOCK_ORDINARY on the index entry to prevent other INSERTs that would conflict with the unique key.

Stage 3 – Row lock for the new record: The actual row is locked with LOCK_X + LOCK_REC_NOT_GAP.

If a unique‑key conflict is detected, InnoDB first acquires a shared lock ( LOCK_S) on the conflicting index entry, then tries to acquire the exclusive lock for the new row. Even under REPEATABLE READ, a Next‑Key lock is created, which can block other sessions.

The compatibility matrix (shown in the original article) leads to the following conclusions:

INSERT vs INSERT do not conflict.

GAP/Next‑Key locks block INSERT.

GAP and Record/Next‑Key do not conflict.

Record vs Record or Record vs Next‑Key conflict.

Step‑by‑Step Execution

Session 1 (sess1) executes INSERT INTO t6(id,a) VALUES(6,15);. The unique check passes, and the row is inserted. sess1 now holds an exclusive lock LOCK_X|LOCK_REC_NOT_GAP on the index entry a=15.

Session 2 (sess2) runs INSERT INTO t6(id,a) VALUES(7,15);. During the unique‑key check it finds the existing a=15 entry, requests a shared lock LOCK_S|LOCK_ORDINARY, and is blocked because sess1 holds an exclusive lock.

Session 3 (sess3) runs INSERT INTO t6(id,a) VALUES(8,15);. It follows the same path as sess2 and also waits for the shared lock.

sess1 rolls back . Its exclusive lock on a=15 is released.

Both sess2 and sess3 acquire the shared lock LOCK_S|LOCK_ORDINARY and then each tries to upgrade to an exclusive lock for its own row ( LOCK_X|LOCK_REC_NOT_GAP). Because the exclusive lock is incompatible with the other session’s shared lock, they wait on each other, forming a classic deadlock. InnoDB chooses one transaction (sess2 or sess3) to roll back.

Root Cause of the Deadlock

sess1 inserts successfully and holds an X lock on the unique key a=15 . sess2 and sess3 both encounter the same unique‑key conflict, request a shared lock, and are blocked by sess1. After sess1 rolls back, sess2 and sess3 obtain the shared lock, but each then tries to acquire an exclusive lock on the same index entry, leading to a lock‑compatibility deadlock.

Conclusion

Analyzing deadlocks caused by INSERT on a unique key requires understanding the multiple lock stages and the lock‑compatibility matrix. The case demonstrates how Insert Intention Gap Locks, shared locks, and exclusive row locks interact, and why a seemingly simple INSERT can deadlock when multiple sessions contend for the same unique value.