文章详情

登录

短信预约-IT技能 免费直播动态提醒

选择考试省份

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团

请输入下面的图形验证码

提交验证

短信预约提醒成功

详细剖析MySQL临键锁

2023-10-27 21:37

关注

💗推荐阅读文章💗

🎉本博客知识点收录于🎉👉🚀《MySQL系列教程》🚀—>✈️03【锁、事务原理、MVCC】✈️

文章目录

2.4 临键锁

2.4.1 临键锁的区间测试

临键锁（Next-Key Lock）：临键锁是查询时InnoDB根据查询的条件而锁定的一个范围，这个范围中包含有间隙锁和记录数；临键锁=间隙锁+记录锁。
其设计的目的是为了解决Phantom Problem(幻读)；主要是阻塞insert，但由于临键锁中包含有记录锁，因此临键锁所锁定的范围内如果包含有记录，那么也会给这些记录添加记录锁，从而造成阻塞除insert之外的操作；
Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。
临键锁锁住的区间为：记录+区间（左开右闭）
左开右闭：不锁住左边，锁右边
测试表：

drop table if exists t2;CREATE TABLE `t2`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `num` int(11) ,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB ;INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);-- 创建普通索引create index idx_num on t2(num);-- 创建唯一索引create unique index idx_num on t2(num);-- 删除索引drop index idx_num on t2;


Tips：间隙锁只会阻塞insert，记录锁会阻塞任意的锁（单要注意排他锁和共享锁的关系）；
【测试案例-01-间隙锁】
临键锁的触发不仅把条件区间（11-16）的数据行锁住了，还把临键的数据行统统锁住了；锁住的区间为：(10,15]、(15,20]
锁住的id范围：10（不含）~20（含）

session1session2
begin;
begin;
select * from t2 where id>11 and id<16 for update;
insert into t2 values(10,0); – 不阻塞
insert into t2 values(11,0); – 阻塞
insert into t2 values(15,0); – 阻塞
insert into t2 values(16,0); – 阻塞
insert into t2 values(18,10); – 阻塞
insert into t2 values(20,0); – 阻塞
insert into t2 values(21,0); – 不阻塞
rollback;
rollback;

【案例测试-02-记录锁】
临键锁是间隙锁+记录数的；上述案例中测试了临键锁中的间隙锁，这次我们来测试一下临键锁中的记录锁；

session1session2
begin;
begin;
select * from t2 where id>11 and id<16 for update;
select * from t2 where id=12 for update; – 间隙锁,不阻塞
select * from t2 where id=15 for update; – 记录数,阻塞
select * from t2 where id=17 for update; – 间隙锁,不阻塞
select * from t2 where id=20 for update; – 记录数,不阻塞
rollback;
rollback;

2.4.2 临键锁-普通列

我们刚刚测试的是以主键索引进行测试，如果采用不同的列（普通列、普通索引、唯一索引/主键索引等），则临键锁中的间隙锁和记录锁住的内容大不相同；
如果查询的是普通列，那么触发的临键锁为：表级别的间隙锁+表级别的记录锁

drop table if exists t2;CREATE TABLE `t2`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `num` int(11) ,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB ;INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);
1）间隙锁

【案例测试-01-表级别间隙锁】

session1session2
begin;
begin;
select * from t2 where num=11 for update;
insert into t2 values(null,3); – 阻塞
insert into t2 values(null,5); – 阻塞
insert into t2 values(null,8); – 阻塞
insert into t2 values(null,10); – 阻塞
insert into t2 values(null,18); – 阻塞
insert into t2 values(null,21); – 阻塞
rollback;
rollback;

Tips：innoDB查询如果没有使用到索引默认触发表级临键锁，把所有的间隙都锁住了

2）记录锁

以普通列查询除了会触发表级别的临键锁外，同时还会触发表级别的记录锁；
【案例测试-02-表级别记录锁】

session1session2
begin;
begin;
select * from t2 where num=11 for update;
select * from t2 where id=3 for update; – 间隙锁,不阻塞
select * from t2 where id=5 for update; – 记录数,阻塞
select * from t2 where id=8 for update; – 间隙锁,不阻塞
select * from t2 where id=15 for update; – 记录数,阻塞
select * from t2 where id=18 for update; – 间隙锁,不阻塞
select * from t2 where id=20 for update; – 记录数,阻塞
rollback;
rollback;

2.4.3 临键锁-普通索引

如果查询的列为普通索引列，要看被查询的记录是否在临界值，以及是否是范围查询，才能判断临建锁的范围；

drop table if exists t2;CREATE TABLE `t2`  (  `id` int(11) NOT NULL AUTO_INCREMENT,  `num` int(11) ,  PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB ;INSERT INTO `t2`(`id`, `num`) VALUES (5, 5);INSERT INTO `t2`(`id`, `num`) VALUES (10, 10);INSERT INTO `t2`(`id`, `num`) VALUES (15, 15);INSERT INTO `t2`(`id`, `num`) VALUES (20, 20);-- 创建普通索引create index idx_num on t2(num);
1）非临界值

当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效；
【测试案例-01-间隙锁】

session1session2
begin;
begin;
– 触发间隙锁，锁住(15,20]区间
select * from t2 where num=17 for update;
insert into t2 values(null,15); – 阻塞
insert into t2 values(null,18); – 阻塞
insert into t2 values(null,20); – 不阻塞
rollback;
rollback;

num=17这条记录不是会锁定(15,20]区间吗？为什么15被阻塞了，20反而没被阻塞呢？
这里需要牵扯到另一个问题了，在InnoDB中，相同的普通索引的叶子节点是以主键的顺序进行排列的，我们来模拟一下刚刚插入的数据在B+Tree上的变化：

只考虑叶子节点的变化，可以看到在上图在演变的过程中产生了分裂情况（假设每个叶子节点都只存储两个元素），如果普通索引的重复值太多势必会造成大量的分裂情况，减低插入效率，因此索引列不宜选择重复率太大的列；
再看下图数据库表中实际存储的列的样子我们就会明白为什么num=20不阻塞，num=15阻塞了


查询示意图：

【测试案例-02-间隙锁】
当我们把id列的影响也计算进来时，数据就符合我们正常分析的情况了：

session1session2
begin;
begin;
– 触发间隙锁，锁住(15,20]区间
select * from t2 where num=17 for update;
insert into t2 values(14,15); – 不阻塞
insert into t2 values(18,18); – 阻塞
insert into t2 values(19,20); – 阻塞
rollback;
rollback;

【测试案例-03-记录锁】
当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效

session1session2
begin;
begin;
– 没有num=17的这条记录，记录锁不会存在
select * from t2 where num=17 for update;
select * from t2 where num=15 for update; – 不阻塞
select * from t2 where num=16 for update; – 不阻塞
select * from t2 where num=17 for update; – 不阻塞
select * from t2 where num=20 for update; – 不阻塞
rollback;
rollback;
2）临界值

【测试案例-01-间隙锁】
当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；
下面案例将会锁住(10,15]、(15,20]两个区间

session1session2
begin;
begin;
– 触发的间隙锁的区间为(10,15]、(15,20]
select * from t2 where num=15 for update;
insert into t2 values(null,8); – 不阻塞
insert into t2 values(null,10); – 阻塞
insert into t2 values(null,11); – 阻塞
insert into t2 values(null,15); – 阻塞
insert into t2 values(null,18); – 阻塞
insert into t2 values(null,20); – 不阻塞
rollback;
rollback;

发现实际插入的数据跟我们分析的情况不一致，这个时候我们依然也要观察B+Tree的实现：


15处于(10,15]和(15,20]两个临键区间，因此在两个区间内的数据行都被锁住了
【测试案例-02-记录锁】
当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；

session1session2
begin;
begin;
– 记录锁只锁住num=15这行记录
select * from t2 where num=15 for update;
select * from t2 where num=10 for update; – 不阻塞
select * from t2 where num=12 for update; – 不阻塞
select * from t2 where num=15 for update; – 阻塞
select * from t2 where num=18 for update; – 不阻塞
select * from t2 where num=20 for update; – 不阻塞
select * from t2 where num=22 for update; – 不阻塞
rollback;
rollback;
3）范围值

【测试案例-01-间隙锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1session2
begin;
begin;
– 间隙锁为(10,20]区间
select * from t2 where num>11 and num <16 for update;
insert into t2 values(9,10); – 不阻塞
insert into t2 values(11,10); – 阻塞（参考B+Tree的构建）
insert into t2 values(11,11); – 阻塞
insert into t2 values(12,12); – 阻塞
insert into t2 values(15,15); – 阻塞（被记录锁阻塞）
insert into t2 values(18,18); – 阻塞
insert into t2 values(19,20); – 阻塞
insert into t2 values(21,20); – 不阻塞（参考B+Tree的构建）
rollback;
rollback;


【测试案例-02-记录锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1session2
begin;
begin;
– 记录锁的区间为(10,20]区间
select * from t2 where num>11 and num <16 for update;
select * from t2 where num=10 for update; – 不阻塞（左开右闭）
select * from t2 where num=12 for update; – 不阻塞（属于间隙）
select * from t2 where num=15 for update; – 阻塞（触发记录锁）
select * from t2 where num=16 for update; – 不阻塞（属于间隙）
select * from t2 where num=18 for update; – 不阻塞（属于间隙）
select * from t2 where num=20 for update; – 阻塞（左开右闭，触发记录锁）
select * from t2 where num=21 for update; – 不阻塞（即是间隙，也不在区间）
rollback;
rollback;

2.4.4 临键锁-主键和唯一索引

如果查询的是唯一索引或主键索引，也要看被查询的记录是否在临界值；是否是范围查询等

创建唯一索引：

-- 删除索引drop index idx_num on t2;-- 创建唯一索引create unique index idx_num on t2(num);
1）非临界值

唯一索引在查询非临界值的记录时和普通索引的特点一样，即==间隙锁为当前记录所在的区间，记录锁不生效；==
【测试案例-01-间隙锁】

session-01session-02
begin;
begin;
– 间隙锁锁住的区间为(15,20]
select * from t2 where num=17 for update;
insert into t2 values(null,11); – 不阻塞
insert into t2 values(null,15); – 不阻塞
insert into t2 values(null,16); – 阻塞
insert into t2 values(null,18); – 阻塞
insert into t2 values(null,20); – 不阻塞
insert into t2 values(null,21); – 不阻塞


Tips：唯一索引冲突时MySQL会立即响应，不会触发临键锁
【测试案例-02-记录锁】
唯一索引在查询非临界值的记录时，记录锁不生效；

session1session2
begin;
begin;
– 没有num=17的这条记录，记录锁不会存在
select * from t2 where num=17 for update;
select * from t2 where num=15 for update; – 不阻塞
select * from t2 where num=16 for update; – 不阻塞
select * from t2 where num=17 for update; – 不阻塞
select * from t2 where num=20 for update; – 不阻塞
rollback;
rollback;
2）临界值

在使用唯一索引查询临界值时，间隙锁会消失，记录锁会退化成行锁；
【测试案例-01-间隙锁】

session1session2
begin;
begin;
select * from t2 where num=15 for update;
insert into t2 values(null,4); – 不阻塞
insert into t2 values(null,8); – 不阻塞
insert into t2 values(null,11); – 不阻塞
insert into t2 values(null,15); – 阻塞(阻塞的原因是记录锁，而不是间隙锁)
insert into t2 values(null,28); – 不阻塞
rollback;insert into t2 values(null,20); – 不阻塞
rollback;

【测试案例-02-记录锁】

session1session2
begin;
begin;
– 记录锁只锁住num=15这行记录
select * from t2 where num=15 for update;
select * from t2 where num=10 for update; – 不阻塞
select * from t2 where num=12 for update; – 不阻塞
select * from t2 where num=15 for update; – 阻塞
select * from t2 where num=18 for update; – 不阻塞
select * from t2 where num=20 for update; – 不阻塞
select * from t2 where num=22 for update; – 不阻塞
rollback;
rollback;
3）范围值

【测试案例-01-间隙锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1session2
begin;
begin;
– 间隙锁为(10,20]区间
select * from t2 where num>11 and num <16 for update;
insert into t2 values(9,10); – 不阻塞
insert into t2 values(11,10); – 阻塞（参考B+Tree的构建）
insert into t2 values(11,11); – 阻塞
insert into t2 values(12,12); – 阻塞
insert into t2 values(15,15); – 阻塞（被记录锁阻塞）
insert into t2 values(18,18); – 阻塞
insert into t2 values(19,20); – 阻塞
insert into t2 values(21,20); – 不阻塞（参考B+Tree的构建）
rollback;
rollback;


【测试案例-02-记录锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；

session1session2
begin;
begin;
– 记录锁的区间为(10,20]区间
select * from t2 where num>11 and num <16 for update;
select * from t2 where num=10 for update; – 不阻塞（左开右闭）
select * from t2 where num=12 for update; – 不阻塞（属于间隙）
select * from t2 where num=15 for update; – 阻塞（触发记录锁）
select * from t2 where num=16 for update; – 不阻塞（属于间隙）
select * from t2 where num=18 for update; – 不阻塞（属于间隙）
select * from t2 where num=20 for update; – 阻塞（左开右闭，触发记录锁）
select * from t2 where num=21 for update; – 不阻塞（即是间隙，也不在区间）
rollback;
rollback;

2.4.5 临键锁总结

临键锁是InnoDB在查询数据时锁定的一个范围，这个范围包含有间隙锁和记录锁；根据查询的条件不同、列的类型不同（是否是索引等）触发的临键锁范围也不同；

Tips：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

来源地址：https://blog.csdn.net/Bb15070047748/article/details/131766686

阅读原文内容投诉

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题

  • 2024年上半年信息系统项目管理师第二批次真题及答案解析（完整版）

    难度     801人已做
    查看

  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     348人已做
    查看

  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     311人已做
    查看

  • 2024年上半年软考高项第一、二批次真题考点汇总（完整版）

    难度     432人已做
    查看

  • 2024年上半年系统架构设计师考试综合知识真题

    难度     220人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机

详细剖析MySQL临键锁

数据库2023-10-27

什么是mysql临键锁

数据库2023-06-20

MySQL的锁机制——记录锁、间隙锁、临键锁

数据库2023-08-18

MySQL锁机制的详细分析

数据库2024-04-02

MySQL间隙锁和临键锁的区别有哪些

数据库2023-10-21

详细分析mysql MDL元数据锁

数据库2022-05-23

【Mysql系列】——详细剖析数据库“索引”【上篇】

数据库2023-08-16

MySQL死锁问题排查与详细分析

MySQL死锁问题排查与详细分析
数据库2024-09-11

JavaSynchronized的偏向锁详细分析

数据库2023-05-15

史上最详细MySQL全局锁和表锁

数据库2024-04-02

Redis超详细分析分布式锁

数据库2022-07-27

AndroidLock锁实现原理详细分析

数据库2023-02-17

C++ Explicit关键字详细解析

数据库2022-11-15

Java static关键字详细解析

数据库2024-04-02

Android关键字persistent详细分析

数据库2024-04-02

C++临时性对象的生命周期详细解析

数据库2022-11-15

MySQL索引详细解析

数据库2024-04-02

剖析6个MySQL死锁案例的原因以及死锁预防策略

剖析6个MySQL死锁案例的原因以及死锁预防策略
数据库2015-02-16

【MySQL进阶教程】锁的详细介绍

数据库2023-09-01

MySQL InnoDB事务与锁的详细讲解

数据库2024-04-02
位置：首页-资讯-数据库
咦！没有更多了？去看看其它编程学习网 内容吧
首页课程
资料下载问答资讯