Why Gap Locks Trigger Deadlocks in InnoDB: A Real‑World MySQL Case Study

Introduction

Deadlock is a challenging issue that many DBAs encounter. This article presents a production case where concurrent gap‑lock requests in REPEATABLE READ (RR) mode cause a deadlock.

Test Environment

Using Percona Server 5.6.24 with transaction isolation set to RR. The table t4 is created with several columns and a unique composite index uniq_kid_aid_biz_rid.

CREATE TABLE `t4` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `kdt_id` int(11) unsigned NOT NULL,
  `admin_id` int(11) unsigned NOT NULL,
  `biz` varchar(20) NOT NULL DEFAULT '1',
  `role_id` int(11) unsigned NOT NULL,
  `shop_id` int(11) unsigned NOT NULL DEFAULT '0',
  `operator` varchar(20) NOT NULL DEFAULT '0',
  `operator_id` int(11) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_kid_aid_biz_rid` (`kdt_id`,`admin_id`,`role_id`,`biz`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Five rows are inserted to set up the scenario.

INSERT INTO `t4` (`id`,`kdt_id`,`admin_id`,`biz`,`role_id`,`shop_id`,`operator`,`operator_id`,`create_time`,`update_time`) VALUES
(1,10,1,'retail',1,0,'0',0,'2017-05-09 15:55:26','2017-05-09 15:55:26'),
(2,20,1,'retail',1,0,'0',0,'2017-05-09 15:55:40','2017-05-09 15:55:40'),
(3,30,1,'retail',1,0,'0',0,'2017-05-09 15:56:06','2017-05-09 15:56:06'),
(4,40,1,'retail',1,0,'0',0,'2017-05-09 15:56:16','2017-05-09 15:56:16');

Test Scenario

Two concurrent transactions each try to delete a non‑existent row (gap lock) and then insert a new row. Because the DELETE on a missing record acquires a gap lock, both transactions block each other when they later request the insert intention lock.

Deadlock Log

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-11 14:51:03f78eaf25700
*** (1) TRANSACTION: 462308535, ACTIVE 20 sec inserting
... (log details omitted for brevity) ...
*** (2) TRANSACTION: 462308534, ACTIVE 29 sec inserting
... (log details omitted for brevity) ...
*** WE ROLL BACK TRANSACTION (2)

Log Analysis

Both DELETE statements target rows that do not exist, so InnoDB acquires lock_mode X locks gap before rec on the index range [(2,20,1,'retail',1,0)-(3,30,1,'retail',1,0)]. The subsequent INSERT statements request an insert‑intention lock, which conflicts with the opposite transaction’s gap X‑lock, creating a circular wait.

Gap locks in InnoDB are “purely inhibitive”; they only prevent other transactions from inserting into the gap, but different transactions can hold conflicting gap locks on the same gap.

Resolution

Check for the row’s existence with a SELECT before issuing DELETE, and serialize the check‑then‑delete sequence.

Use INSERT ... ON DUPLICATE KEY UPDATE (or INSERT IGNORE) to avoid the delete‑then‑insert pattern.

Conclusion

In RR isolation, gap locks are a common source of deadlocks, especially when DELETE on non‑existent rows is followed by INSERT. Adjusting the business logic to avoid unnecessary gap locks eliminates the deadlock.