Analysis of S‑Gap Lock Deadlocks under READ COMMITTED Isolation in InnoDB

Background: Under READ COMMITTED isolation, a surprising S‑type GAP lock deadlock was observed involving two seemingly unrelated SQL statements that manipulate a file‑move workflow using a table table_ofs with a primary key and two unique indexes.

Transaction logic (simplified):

select * from table_ofs where inode_id='893' lock in share mode;
select * from table_ofs where inode_id='224' lock in share mode;

delete from table table_ofs where parent_id='893' and name='00004_0';
INSERT INTO `table_ofs` (`inode_id`,`name`,`parent_id`) VALUES (704,'00004_0',224);

Transaction 2 uses the same SELECTs but deletes a different record and inserts a different row:

select * from table_ofs where inode_id='893' lock in share mode;
select * from table_ofs where inode_id='224' lock in share mode;

delete from table table_ofs where parent_id='893' and name='00006_0';
INSERT INTO `table_ofs` (`inode_id`,`name`,`parent_id`) VALUES (728,'00006_0',224);

The table schema is:

CREATE TABLE table_ofs (
  inode_id int(11) NOT NULL,
  name varchar(10) DEFAULT NULL,
  parent_id int(11) DEFAULT NULL,
  PRIMARY KEY (inode_id),
  UNIQUE KEY key_pid_name (parent_id,name),
  UNIQUE KEY key_pid_iid (parent_id,inode_id)
) ENGINE=InnoDB;

Deadlock logs show that both transactions hold and request locks on the unique index key_pid_name . Transaction 1’s INSERT holds an unknown lock and requests an INSERT‑INTENTION lock; Transaction 2’s INSERT holds an S‑type GAP lock on the same index entry and also requests an INSERT‑INTENTION lock, leading to a circular wait.

Key observations:

The SELECT … LOCK IN SHARE MODE statements operate on the primary key and do not contribute to the deadlock.

The S‑type GAP lock is not created by the DELETE statements; it originates from the INSERT’s duplicate‑key check.

Insert lock flow (illustrated in the original diagram) shows that when an INSERT checks for a duplicate key, InnoDB may acquire an S‑type next‑key lock on the existing record or on a gap if the record is marked for deletion.

MySQL documentation confirms that GAP locks are disabled for ordinary searches under READ COMMITTED, but they can still appear during foreign‑key checks and duplicate‑key checks.

Reproduced deadlock scenario:

CREATE TABLE ofs (
  inode_id int(11) NOT NULL,
  name varchar(10) DEFAULT NULL,
  parent_id int(11) DEFAULT NULL,
  PRIMARY KEY (inode_id),
  UNIQUE KEY key_pid_name (parent_id,name),
  UNIQUE KEY key_pid_iid (parent_id,inode_id)
) ENGINE=InnoDB;

insert into ofs values(100, "00007", 224);

Step‑by‑step lock evolution (summarized):

Step 1: T1 inserts (224, "00004") → X lock on that index entry.

Step 2: T2 inserts (224, "00005") → X lock on that entry.

Step 3: T3 attempts to insert (224, "00004") → S‑type next‑key lock on (224, "00004").

Step 4: T4 attempts to insert (224, "00005") → S‑type next‑key lock on (224, "00005").

Step 5: T1 rolls back, deleting its row; the S‑type lock held by T3 inherits to the next record (224, "00007") as an S‑type GAP lock.

Step 6: T2 rolls back, causing similar inheritance for T4; both T3 and T4 now wait for an INSERT‑INTENTION lock on the same gap, creating a deadlock.

The lock inheritance is implemented in InnoDB code (functions lock_rec_inherit_to_gap and lock_rec_inherit_to_gap_if_gap_lock ), which transfers GAP attributes to the next record when the original record is deleted.

Lock splitting can further duplicate GAP locks, ensuring the lock semantics remain correct after a new record is inserted within a gap.

Final conclusion: Even under READ COMMITTED, S‑type GAP locks can arise when an INSERT encounters a duplicate‑key situation involving a row that is being deleted but not yet committed. Multiple rollbacks can cause several transactions to inherit GAP locks on the same range, leading to deadlocks when they subsequently try to insert into that range.

Reference: MySQL 5.7 InnoDB Locking documentation (https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html).