聊聊行锁、间隙锁、临键锁的区别
- https://blog.csdn.net/sunboylife/article/details/132222220
一、准备
创建 student 表
CREATE TABLE student (
id bigint NOT NULL,
age int DEFAULT NULL,
b int DEFAULT NULL,
a int DEFAULT NULL,
c int DEFAULT NULL,
PRIMARY KEY ( id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
插⼊数据
insert into student (id,age,a,b,c) values(1,1,1,1,1),(5,5,5,5,5),(9,9,9,9,9),(11,11,11,11,11);
二、锁区间 * 记录 间隙 临键
记录：1 5 9 11
间隙 -无穷 1,1 到 5,5到9,9到11,11到无穷 （不包含）
临键：-无穷 1,1 到 5,5到9,9到11,11到无穷 （包含1 5 9 11 后面的）
1.什么是锁区间？
区间从字⾯意思解释就是⼀个范围。
2.什么范围呢？怎么建⽴的呢？
mysql的锁区间是在当前会话 创建后 事物开始时 当前数据库 已存在数据 相邻数据之间 两两作为区间边界值。
3.相邻数据⼜以什么作为依据建⽴锁区间呢，有以下情况？
1.查询条件是主键索引
就以主键索引 从⼩到大 两两数据以主键列作为数据依据建⽴锁区间
2.查询条件时普通索引
和主键索引⼀致
3.组合索引
以组合索引第⼀个字段为依据
4.两个读索引
a字段建⽴索引，b字段建⽴索引
a和b都作为查询条件，并且使⽤and作为条件，结果集求交集。
条件查询中涉及到的索引依照创建索引顺序排序，以排在第⼀个创建的索引作为锁区间依据字段。
5.组合索引
a字段建⽴索引，b字段建⽴索引
a和b都作为查询条件，并且使⽤or作为条件，结果集求并集。
- 当or ⾛索引，合并索引，两个字段都作为锁区间依据字段，其他操作满⾜任何⼀个字段的锁区间都将阻塞。
当or不⾛索引，全表查询，两个字段都作为锁区间依据字段，其他操作满⾜任何⼀个字段的锁区间都将阻 塞。
三、从锁区间看临键锁和间隙锁的区别
从区间上看，临键锁和间隙锁的区别是锁区间不同，临键锁包含右边界数据，间隙锁不包含边界数据。
四、记录锁（Record Locks）
select * from student where id=1 for update;
锁住 id=1 的记录。
当前查询的id不存在时，将升级为间隙锁
疑问1:升级为间隙锁，锁的是什么锁区间？
测试查询id = 3，此时数据库不存在id为3的数据，
- 那么会以id字段作为依据找到id=3⼊的锁区间（1，5）
疑问2:为什么是 间隙锁锁区间 不是临键锁锁区间 有什么依据？
- 如果是临键锁我们更改边界值会阻塞
建⽴会话 1 ，执⾏选中 s q l
--insert into student (id,age,a,b,c) values(2,2,2,2,2);
insert into student (id,age,a,b,c) values(4,4,4,4,4);
update student set age = 9 where id = 5;
insert into student (id,age,a,b,c) values(6,6,6,6,6);
建⽴会话 2 ，执⾏ s q l
进⼊阻塞状态
执⾏会话 2 中的第⼆条 s q l
依然还是阻塞状态
执⾏会话 2 第三条 s q l ，修改边界值
并没有阻塞，执⾏成功
数据修改成功年龄成功修改为 9
我们可以测⼀下是否影响 id 在（5， 9 ）区间的，执⾏选中 s q l
执⾏成功
字段数值不存在时，只影响 锁区间范围数据。
- 因此当查询 字段数值不存在时，只影响该数值落⼊的依据字段锁区间范围数据。
- 即 间隙锁
五、间隙锁（Gap Locks）
- 这里是 闭区间，不会锁住： 5 和 9
- SELECT * FROM student WHERE id > 5 and id < 7 for update; 依然是锁：5-9 闭区间
间隙锁的触发⽅式：当我们进⾏范围查询的时候会触发间隙锁
Select * from student where id >5 for update
- 落⼊（5， 无穷大 ）锁区间，ID=5，不包含，是可以更新的。
- ID=9 或 11 更新的话，会卡死
- 这里文档说的有问题，应该是锁的为：6到无穷大
begin;
select * from student where id>5 and id<7 for update;
如果是这个SQL，锁的是：（5 - 9）区间， 不包含5 和 9，
6-8 没有数据，更新的话，没问题，不会卡死
1.主键作为查询条件
思考
Select * from student where id >5 for update 锁的是哪⾥？
预测：
- 落⼊（1， 5 ）锁区间
- 落⼊（5， 9 ）锁区间
- 落⼊（1， 5 ）锁区间和（5， 9 ）锁区间
- 两个都不落⼊
会话1 执⾏
- 锁的是 6-无穷大
会话 2
可得并没有落⼊（1， 5 ）锁区间
会话 2 ，执⾏插⼊ id=6 ，阻塞了
落⼊了（5， 9）锁区间 (这里错误，锁的是 5 到 无穷大)
那好，我们修改⼀下会话 1 的 id 条件值， id>5 改成 id>7
会话 2 执⾏，看右上⻆，仍然是阻塞状态
>7 落到5-9区间，5以上的都会被锁
因为 7 仍然是落⼊（5， 9 ）锁区间，进⽽导致该锁区间有效。 那好我们接着测试（9， 11 ），（ 11 ，正⽆穷⼤）
依然是阻塞状态，因为查询的条件是 >5 ，（9， 11 ），（ 11 ，正⽆穷⼤）也属于该范围内的锁区间，进⽽也会⽣效。
- 这里说的没问题， >5 ，锁到 无穷大。
- 如果是 5 到 7，也是会锁 5到9区间（不包含）
2.普通索引作为查询条件
和主键⼀致
3.组合索引作为查询条件
表数据以及字段顺序如下
测试⽤例：
- 和数据库字段保持⼀致
- 改变查询字段顺序
- 改变表结构字段顺序
- 改变组合索引字段顺序
3.1数据库字段顺序保持⼀致
创建组合索引 a,b
会话 1 执⾏选中 s q l
会话 2 执⾏选中 s q l
进⼊阻塞状态，因此 a >5 ⽣效，导致 a=6 时插⼊阻塞
- 注意了：这里比较特殊：a>=5 都会阻塞
-- 会阻塞。
UPDATE `student` SET `a` = 5 WHERE `id` = 5;
-- 不会阻塞。 和 插入一样
UPDATE `student` SET `a` = 5 WHERE `id` = 5;
- a是11，且索引是 ab，a在前面**（只看a）**。所以会阻塞
我们将插⼊数据 a=6 改成 a=2 ， b=11 改为 2 ，只让插⼊数据 b=2 落⼊ b<9 中，
- a>5，a的值为2，已经不符合了，所以不阻塞。索引只看 a
此时并没有阻塞因此，⽣效的是 a 字段， b 字段虽然也是组合索引字段，但是并没有满⾜范围的锁区间⽣效。
思考：
因为 a 字段在查询条件之前的原因导致 a>5 的锁区间⽣效了吗？好的，带着疑问来下⾯。
3.2改变查询字段顺序
将 where a>5 and b<9 for update;
改为 :
where b <9 and a>5 for update;
执⾏会话 1 选中 s q l
执⾏会话 2
将 a=2 改为 6,b=2 改为 11
当 a=6 时依然阻塞 ,a>5 仍然⽣效
执⾏会话 2
将 a=6 改为 2,b=11 改为 2
当 a=2,b=2 时并没有阻塞，因此查询条件顺序并不会影响⽣效字段。
思考：
不是条件查询顺序影响，会不会是表结构顺序影响？好的，带着疑问继续来下⾯。
3.3 改变表结构字段顺序
b 字段放在 a 字段之前
执⾏会话 1
执⾏会话 2
将 a=2 改为 6,b=2 改为 11
阻塞了。
执⾏会话 2
将 a=6 改为 2,b=11 改为 2
执⾏成功。
此处做个⼩总结：
使⽤组合索引作为条件查询，⽣效的锁区间与表结构字段顺序、查询条件字段顺序⽆关。
3.4改变组合索引字段顺序
表结构恢复
组合索引修改
会话 1 执⾏选中 s q l
会话 2 执⾏选中 s q l
插⼊ a=6 ，没有阻塞，此时 a 字段没有⽣效
删除插⼊数据，将 a=6 改为 a=4 ，将 b=11 改为 b=8, 执⾏会话 2
修改数据后重新插⼊，阻塞了
总结： ** 仅有组合索引⾸字段作为锁区间依据。
4 两个索引作为查询条件
测试⽤例：
- 按照表结构顺序和查询条件顺序⼀致
- 改变查询条件顺序
- 改变表结构字段顺序
前提： - 删除组合索引（ a,b ）
- 建⽴普通索引 a,b
- 删除表数据
4.1按照表结构顺序和查询条件顺序⼀致
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏选中 s q l
当 a=6,b=11 时执⾏阻塞，因此 a>5 ⽣效。
会话 2 ，执⾏选中 s q l
将 a=6 改为 a=2,b=11 改为 b=8
并没有阻塞，
- 此时： a是单独的索引，a在前面
select * from student where a > 5 and b < 9 for update;
- a >=5 都会锁，
- 改变了 索引的顺序，会锁5
4.2改变查询条件顺序
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏选中 s q l
阻塞了， a>5 ⽣效
会话 2 ，执⾏选中 s q l
⽆阻塞， b<9 五⽣效
总结：两个索引的条件查询顺序不能影响使⽤哪个字段作为锁区间的依据
4.3改变表结构字段顺序
表结构
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏选中 s q l
a=6 阻塞了，因此 a>5 ⽣效了
会话 2 ，执⾏选中 s q l
执⾏成功了
4.4修改索引创建顺序
将创建 b 字段索引提前到创建 a 字段索引之前
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏选中 s q l
b=8 时，阻塞了，因此 b<9 ⽣效
总结
** 索引创建顺序影响锁区间依据字段
表结构字段顺序和条件查询顺序不会影响锁区间依据字段，
索引创建顺序影响锁区间依据字段。
5.合并索引和⾮合并索引
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏选中 s q l
当 a=6 时，阻塞， a>5 ⽣效
会话 2 ，执⾏选中 s q l
当 b=2 时，阻塞， b<9 ⽣效
总结：
** 条件字段中的索引都将作为锁区间依据字段。
-
索引合并查询，
-
这里真实测试的是：感觉锁表了，哪个条件插入都会卡顿
6.普通字段
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏选中 s q l
插⼊ c=6 时，阻塞，因为
* ⾮索引字段查询时全表查询，锁的是全表，因此阻塞全表带有加锁操作。
7.组合索引和两个索引总结
表结构顺序和条件查询顺序不能影响组合索引和两个索引在条件查询时锁区间依据字段，组合索引锁区间依据字段
是创建组合索引时的⾸字段，两个索引锁区间依据字段是各个索引创建的顺序。
题外话：
插⼊数据的时候，先判断唯⼀ ID 是否已经存在，再去尝试加；
使⽤ o r 不⼀定⾛索引，即使⾛也是合并索引
六、临键锁
触发时机：当范围查询命中数据触发临键锁。
临键锁在 mysql8 进⾏了优化：
mysql5.7 版本
如： id>5 and id <10,
- Id=9 命中数据库数据， 9 左右锁区间为（5， 9],(9,11], 因此锁住的数据范围（ 5,11] 。
Mysql8.0.28 版本
- 如： id>5 and id <10,Id=9 命中数据库数据，
- 9 左右锁区间为（5， 9),(9,11), ⼜因为命中 9 数据，因此锁住的数据范围 （5,11 ）
1.mysql5.7****版本
会话 1 ，执⾏选中 s q l
会话 2 ，执⾏对 id=11 数据更新
发⽣了阻塞，符合我们前⾯介绍的。
2.Mysql8.0.28****版本
会话 1 ，执⾏ s q l
会话 2 ，执⾏ s q l
会话 2 ，执⾏ s q l ，对 id=9 数据进⾏更新
会话 2 进⼊阻塞状态
会话 2 再次执⾏ s q l ，对 id=11 数据进⾏更新
本次会话 2 并没有阻塞，什么⻤？为什么不阻塞，猜测 mysql 开发者对临键锁进⾏了优化。
- 答：因为是临界锁。只要id是 id<11，在11这个锁区间里的，都不会锁 id=11
1981
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
