mysql的间隙锁有时候让我们感觉怪怪的,不知道大家是否有这种感觉,记录下最近实验的间隙锁出现的临界值问题
mysql 版本8.0.21
测试表
建表语句
Create Table: CREATE TABLE `t` (
`a` int NOT NULL AUTO_INCREMENT,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `idx` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
值
insert into t values(1,5,10),(5,10,8),(10,1,4);
测试内容
使用mysql默认隔离级别RR
关闭autocommit
开启两个mysql客户端
为了防止被额外的主键索引的行锁影响,所有的查询语句使用的模式均为S锁
样本数据状态
a | b | c |
---|---|---|
2 | 5 | 10 |
5 | 10 | 8 |
10 | 1 | 4 |
客户端A | 客户端B |
---|---|
select * from t where b = 4 lock in share mode; | |
insert into t values(4,4,4); //blocked,ctrl c打断 | |
insert into t values(9,1,4);//success | |
insert into t values(11,1,4);//blocked,ctrl c打断 | |
insert into t values(3,5,4);//success | |
insert into t values(1,5,4);//blocked,ctrl c打断 |
分析:
由于b是普通索引,所以查询b的时候会加入间隙锁,现在加入的间隙锁范围是 1<b<5
,这是毫无争议的,所以如果插入任何b区间在(1,5)之间的值,均会被阻塞
但是让我们来看两个临界点1和5
对于临界点1,在唯一索引是10以上的情况下,会被阻塞,对于临界点5,在唯一索引是2以前的情况下,会被阻塞
还原,继续测试范围查询的情况
客户端A | 客户端B |
---|---|
select * from t where b <= 5 lock in share mode; | |
insert into t values(9,1,4);//blocked,ctrl c打断 | |
insert into t values(11,1,4);//blocked,ctrl c打断 | |
insert into t values(6,10,4);//success | |
insert into t values(4,10,4);//blocked,ctrl c打断 |
分析:
由于b是普通索引,所以查询b的时候会加入间隙锁,现在加入的间隙锁范围是 b<10
,所以2,3的sql会阻塞,不是因为唯一索引阻塞,而是因为间隙锁阻塞
但是让我们来看临界点10
对于临界点10,对标唯一索引5,在唯一索引是5以前的情况,会被阻塞
总结
间隙锁不仅受限于当前索引,还受限于数据在磁盘上排放的位置,对标到数据库就是当前的聚集索引,当在临界位置时,如果是左边的临界位置,那么会锁对应聚集索引下标的右半部分,如果是右临界位置,会锁对应聚集索引下标的左半部分,然后再与中间锁定的部分一起,形成完整的间隙锁