MySQL Insert Locking, Insert‑Intention Locks, Gap Locks, and Deadlock Analysis

The article is part of the "MySQL Column" series produced by the Aikesheng operations team, sharing real‑world experience on MySQL features, optimization cases, architecture, HA, and monitoring.

Before answering the main question, two prerequisites are required: (1) column c2 must have a unique index, and (2) the transaction isolation level must be READ‑COMMITTED.

The author references similar analyses in Ding Qi's "MySQL Practical 45 Lectures" and proceeds to discuss the locking situation and deadlock causes for a specific INSERT scenario.

Locking Situation and Deadlock Cause Analysis

Table definition and sample data used for reproduction:

CREATE TABLE `t3` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB;

INSERT INTO t3 VALUES (1,1),(15,15),(20,20);

When session1 commits, either session2 or session3 will report a deadlock. The deadlock occurs as follows:

Session1 executes DELETE on the unique index c2 = 15 , acquiring an X lock (X Lock but not gap).

Session2 and session3 attempt INSERT ; the unique‑key check triggers a S Next‑Key lock on the interval (1,15], which is blocked by session1's X lock.

After session1 commits, its X lock is released, allowing session2 and session3 to obtain the S Next‑Key lock.

Both sessions then acquire an INSERT INTENTION LOCK; because this lock can be blocked by a gap lock, the two sessions wait on each other, forming a deadlock.

The deadlock log is shown in the original article (image omitted).

INSERT INTENTION LOCK

An insert‑intention gap lock is set before inserting a row; it signals the intent to insert and allows multiple transactions inserting into different positions of the same gap to proceed without waiting. However, it can be blocked by existing gap locks.

Key properties of the insert‑intention lock:

It does not block any other lock.

It can be blocked only by a gap lock.

GAP LOCK

Even under READ‑COMMITTED, gap locks appear when a unique‑key check encounters a conflict; a S Next‑Key lock (shared lock with gap attribute) is added on the record and the preceding gap.

An example demonstrates that a session inserting a duplicate value acquires a S Next‑Key lock on the gap (15,20] which is not released immediately, causing other sessions to wait.

The article raises a question about the purpose of the S Next‑Key lock during unique‑key checks, noting that it seems to violate the two‑phase locking principle when the lock is released instantly after a successful insert.

Additional observations for REPEATABLE‑READ isolation level:

For non‑unique indexes, S/X locks include gap attributes, locking the interval between the previous and next records.

Lock Conflict Matrix

A matrix (image omitted) shows how lock modes (S, X) combine with lock attributes (gap, next‑key) to determine conflicts, aiding deadlock analysis.

INSERT Lock Summary

If there is no unique key, an X lock without gap is taken.

When a unique‑key conflict occurs, a S lock with gap is added, locking the record and the preceding gap.

If the insertion position already has a gap‑attribute S/X lock, the INSERT INTENTION LOCK is blocked and waits.

On successful insertion, the final lock on the new record is an X lock without gap.

The author invites discussion on locking behavior for SELECT, DELETE, UPDATE, and REPLACE statements.

Practical Recommendations

In most business scenarios, set MySQL isolation level to READ‑COMMITTED.

When possible, minimize the number of unique indexes to reduce lock contention.

Finally, the article includes promotional sections for the open‑source distributed middleware DBLE and DTLE, with links to community sites, GitHub repositories, and technical chat groups.