When INSERT Triggers a Deadlock: Decoding MySQL Gap and Intention Locks

Introduction

Deadlock is a challenging problem that many DBAs and developers encounter; this article presents a step‑by‑step case analysis to help readers understand the root causes of deadlocks in MySQL.

Background Knowledge

Insert Intention Lock

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

When a transaction executes an INSERT, it first acquires an Insert Intention Lock on the index gap. If a duplicate‑key error occurs, a shared lock on the conflicting index record is set, which becomes a S Next‑key Lock that blocks other sessions.

Validation

Prepare an environment with the default RC isolation level and create a test table with a unique index.

CREATE TABLE t8 (
  a int AUTO_INCREMENT PRIMARY KEY,
  b int,
  c int,
  UNIQUE KEY ub(b)
) ENGINE=InnoDB;

Insert a row that will acquire the locks. INSERT INTO t8 VALUES (NULL,1,2); After the INSERT, run SHOW ENGINE INNODB STATUS to view the lock information.

Process Analysis

Running SHOW ENGINE INNODB STATUS after the DELETE shows row‑level locks on the unique index and primary key (lock_mode X locks rec but not gap). After the INSERT, the log shows a S Next‑key Lock with lock mode S waiting, indicating a gap lock.

---TRANSACTION 462308671, ACTIVE 6 sec
... lock_mode X locks rec but not gap
... lock_mode S waiting

The presence of lock mode X locks rec but not gap means an exclusive row lock without a gap, while lock mode S with waiting denotes a shared gap lock that blocks other inserts.

Case Analysis

Environment

CREATE TABLE t7 (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a int NOT NULL,
  UNIQUE KEY ua(a)
) ENGINE=InnoDB;

Insert initial data.

INSERT INTO t7(id,a) VALUES (1,1),(5,4),(20,20),(25,12);

Test Project

Deadlock Log

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION: 462308661, ACTIVE 6 sec inserting
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3796966 ...
INSERT INTO t7(id,a) VALUES (30,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` lock_mode S waiting
*** (2) TRANSACTION: 462308660, ACTIVE 43 sec inserting
... lock_mode X locks rec but not gap
*** (2) HOLDS THE LOCK (S):
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` lock_mode X locks rec but not gap
*** WE ROLL BACK TRANSACTION (1)

Log Analysis

Transaction T2 inserts (26,10) successfully and holds an exclusive row lock on a=10 ( LOCK_X + LOCK_REC_NOT_GAP).

Transaction T1 attempts to insert (30,10). Because a=10 already exists, MySQL checks the unique constraint, acquires a S Next‑key Lock on the gap (4,10], and waits.

Transaction T2 then tries to insert (40,9). The value a=9 falls inside the gap locked by T1, so T2 must wait for the S Next‑key Lock, leading to a circular wait and a deadlock.

Conclusion

The case demonstrates that concurrent INSERTs on a unique key can generate both intention locks and gap locks. Even under RC isolation, a S Next‑key Lock can block other sessions, and the exclusive row lock held by the first transaction creates a circular dependency. Understanding the distinction between lock_mode X locks rec but not gap (row lock) and lock_mode S (gap lock) is essential for diagnosing and preventing deadlocks in MySQL.