InnoDB间隙锁疑问：同b值插入操作为何结果不同？

环境与数据准备 #

CREATE TABLE `z` ( 
  `a` int(11) NOT NULL, 
  `b` int(11) DEFAULT NULL, 
  PRIMARY KEY (`a`), 
  KEY `b` (`b`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

INSERT INTO `test`.`z` (`a`, `b`) VALUES (1, 1); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (3, 1); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (5, 3); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (7, 6); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (10, 8); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (20, 30); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (50, 60); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (45, 90); 
INSERT INTO `test`.`z` (`a`, `b`) VALUES (2, 99);

-- 事务隔离级别
transaction_isolation = REPEATABLE-READ 
tx_isolation = REPEATABLE-READ 

操作流程 #

会话1 #

begin;
select * from z where b = 60 for update;

会话2 #

begin;
-- 执行失败，触发锁超时
insert into z select 4, 90; /* 为何该插入操作无法执行？ */
-- 报错：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 执行成功
insert into z select 46, 90; /* 但该插入操作可以执行？ */
-- 结果：Query OK, 1 row affected (0.00 sec)
-- Records: 1 Duplicates: 0 Warnings: 0

问题 #

如上操作所示：两条插入语句的b值均为90，却一个执行失败一个成功，请问这是什么原因？

问题解答 #

这个问题本质是InnoDB在**可重复读（REPEATABLE-READ）**隔离级别下，**间隙锁（Gap Lock）**和索引排序规则共同作用的结果，我给你一步步拆解：

1. 会话1的加锁逻辑

当你执行select * from z where b = 60 for update时，因为是RR隔离级别，InnoDB会用临键锁（记录锁+间隙锁）来防止幻读：

• 首先对b=60的那条记录（a=50）加记录锁，阻止其他事务修改这条记录；
• 同时，为了防止其他事务插入b值介于60和下一个存在的b值（也就是90）之间的新记录，InnoDB会对间隙**(60, 90)加间隙锁**。

这里要注意：InnoDB的普通索引（比如b）排序时，是先按索引字段值排序，再按主键值排序。现有数据中b=90的记录主键是45，所以索引b的排序顺序是：
b=1(a=1) → b=1(a=3) → b=3(a=5) → b=6(a=7) → b=8(a=10) → b=30(a=20) → b=60(a=50) → b=90(a=45) → b=99(a=2)

2. 会话2两次插入的锁冲突分析

插入(4,90)失败的原因

新插入的(4,90)，因为主键a=4比现有b=90记录的主键45小，所以它在索引b中的位置是**b=60(a=50)和b=90(a=45)之间**——这个位置正好落在会话1加的间隙锁(60,90)范围内。

InnoDB插入数据前会检查插入位置的间隙是否被其他事务锁定，所以这次插入会被阻塞，直到锁超时，就报了1205错误。

插入(46,90)成功的原因

而新插入的(46,90)，主键a=46比现有b=90记录的主键45大，所以它在索引b中的位置是**b=90(a=45)和b=99(a=2)之间**——这个间隙不在会话1的锁定范围(60,90)内，所以没有锁冲突，插入顺利执行。

总结

核心就是：

• RR隔离级别下，等值查询匹配到存在的记录时，InnoDB会对该记录加记录锁，同时对该记录到下一个存在索引值的间隙加间隙锁；
• 普通索引的排序是索引字段+主键的组合排序，插入位置由这个组合决定，只有当插入位置落在被锁定的间隙里时，才会触发锁等待。

内容的提问来源于stack exchange，提问作者XL.Wei