MySQL 间隙锁测试证明

搭建测试环境

create table t(
  a int ,
  b int ,
  primary key(a),
  key(b)
)engine=innodb;

INSERT INTO `t`(`a`, `b`) VALUES (1, 1);
INSERT INTO `t`(`a`, `b`) VALUES (3, 3);
INSERT INTO `t`(`a`, `b`) VALUES (5, 5);
INSERT INTO `t`(`a`, `b`) VALUES (9, 30);
INSERT INTO `t`(`a`, `b`) VALUES (11, 10);

select * from t;

锁的分类

• Record Lock : 就是具体某一行记录上加的锁
• Gap Lock ：间隙锁，锁定一个范围，但不包含记录，不包含记录，不包含记录，重要的事加粗说3遍
• Next-Key Lock: Gap Lock + Record Lock,锁定一个范围（用Gap Lock实现），并且锁定记录本身（Record Lock实现）

MySQL将数据按照聚集索引存储,这里指的是a字段，即把数据存到磁盘中的顺序是按照a字段的大小1-11顺序存放的。

在b字段的索引中，数据的索引顺序为：行(1,1),行(3,3),行(5,5),行(11,10),行(9,30)。行与行之间产生间隙，在间隙中插入数据，需要先获取排他锁。

实践证明

间隙锁（Gap Lock）的实践证明。

特别说明：
（1）REPEATABLE READ事务级别使用的Next-Key Lock，通过上面对锁的分类，可以知道Next-Key Lock是会用到间隙锁的。而READ COMMITTED就没有用到间隙锁,使用的是Record Lock。
（2）REPEATABLE READ事务级别执行带锁的SQL时，会锁定SQL语句匹配记录两边的间隙，而匹配记录本身是通过Record Lock锁定的

时间	Session1	Session2	间隙情况	阻塞原因
1	set TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2	begin;
3	select * from t where b=10 LOCK in SHARE MODE;
4		set TRANSACTION ISOLATION LEVEL REPEATABLE READ;
5		begin;
6		insert into t(a, b) VALUES (7,5); 阻塞	行(1,1),行(3,3),行(5,5),行(7,5),行(11,10),行(9,30)	gap lock
7		insert into t(a, b) VALUES (8,5); 阻塞	行(1,1),行(3,3),行(5,5),行(8,5),行(11,10),行(9,30)	gap lock
8		insert into t(a, b) VALUES (10,5); 阻塞	行(1,1),行(3,3),行(5,5),行(10,5),行(11,10),行(9,30)	gap lock
9		insert into t(a, b) VALUES (11,5); 阻塞	行(1,1),行(3,3),行(5,5),行(11,5),行(11,10),行(9,30)	gap lock
9		insert into t(a, b) VALUES (4,5); 成功	行(1,1),行(3,3),行(4,5),行(5,5),行(11,10),行(9,30)
9		insert into t(a, b) VALUES (12,31); 阻塞	行(1,1),行(3,3),行(5,5),行(11,10),行(9,30),行(12,31)	gap lock
10		insert into t(a, b) VALUES (13,10); 阻塞	行(1,1),行(3,3),行(5,5),行(11,10),行(13,10),行(9,30)	gap lock
11		update t a=111 where b=10; 阻塞	行(1,1),行(3,3),行(5,5),行(11,10),行(111,10),行(9,30)	gap lock

综上：如果事务（Session1）产生了间隙锁(Gap Lock),那么其他事务（Session2）对间隙中进行insert/update操作，需要先获取排他锁(X)，导致阻塞。在间隙外的insert/update操作不会受影响。