您的位置：上海毫米网络优化公司 > 网站优化分享 >
相关推荐recommended
- SpringBoot集成EasyExcel 3.x：高效实现Excel
- Linux部署Sonarqube+Gogs+Jenkins（一）
- Springboot中JUNIT5单元测试＋Mockito详解
- Node.js版本升级，修改模块默认的保存位置
- Spring Boot集成knife4j
- 前端 Vue启动本地（.exe）文件
- Scrapy框架（高效爬虫）
- 【每日一题】LeetCode——链表的中间结点
- 爬虫解析——Xpath的安装及使用（五）
- SpringBoot集成Nacos
- 关于MYSQL的XA事务(分布式事务)的了解以及MySQL5.7.7对
- Spring-Kafka 3.0 消费者消费失败处理方案
- Spring Boot如何彻底解决跨域问题，五种方案来看看吧
- SQL Server数据库以及Management Studio详细安
- Golang数据类型
- 如何使用phpStudy快速搭建一个网站
- Python淘宝书籍图书销售数据爬虫可视化分析大屏全屏系统
- MySQL中如何将字符串替换
- Python机器学习实战教程
- 如何为PostgreSQL设置自增主键？
- 云计算时代的运维： 职业发展方向与岗位选择
- Rust｜60个新手常犯的错误
- WordPress 告别 MySQL：Docker SQLite Wo
- 监听Redis中Key值的变化（SpringBoot整合）
- SQL拆分字段内容（含分隔符）
- springboot与tomcat关系
- MySQL日期类型及默认设置
- 【Go设置国内代理】
- Spring Security 之方法级的权限管控 @PreAutho
- 机器学习是什么
详细剖析MySQL临键锁
作者：mmseoamin日期：2024-04-01
💗推荐阅读文章💗
- 🌸JavaSE系列🌸👉1️⃣《JavaSE系列教程》
- 🌺MySQL系列🌺👉2️⃣《MySQL系列教程》
- 🍀JavaWeb系列🍀👉3️⃣《JavaWeb系列教程》
- 🌻SSM框架系列🌻👉4️⃣《SSM框架系列教程》
🎉本博客知识点收录于🎉👉🚀《MySQL系列教程》🚀—>✈️03【锁、事务原理、MVCC】✈️
文章目录
- 2.4 临键锁
- 2.4.1 临键锁的区间测试
- 2.4.2 临键锁-普通列
- 1）间隙锁
- 2）记录锁
- 2.4.3 临键锁-普通索引
- 1）非临界值
- 2）临界值
- 3）范围值
- 2.4.4 临键锁-主键和唯一索引
- 1）非临界值
- 2）临界值
- 3）范围值
- 2.4.5 临键锁总结
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（含）
session1 session2 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-记录锁】
临键锁是间隙锁+记录数的；上述案例中测试了临键锁中的间隙锁，这次我们来测试一下临键锁中的记录锁；
session1 session2 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-表级别间隙锁】
session1 session2 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-表级别记录锁】
session1 session2 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-间隙锁】
session1 session2 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阻塞了
- num索引列排列情况：
查询示意图：
【测试案例-02-间隙锁】
当我们把id列的影响也计算进来时，数据就符合我们正常分析的情况了：
session1 session2 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-记录锁】
当使用普通索引列查询，查询的记录不处于临界值时，那么间隙锁为被查询记录所在的区间，记录锁则不会生效
session1 session2 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]两个区间
session1 session2 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-记录锁】
当使用普通索引列来查询，并且查询的记录处于临界值时，那么间隙锁为相邻的两个区间，记录锁退化成行锁；
session1 session2 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-间隙锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
session1 session2 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; - 分析底层B+Tree构建情况：
【测试案例-02-记录锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
session1 session2 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-01 session-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); – 不阻塞 - 分析num列索引的B+Tree底层构建情况：
Tips：唯一索引冲突时MySQL会立即响应，不会触发临键锁
【测试案例-02-记录锁】
唯一索引在查询非临界值的记录时，记录锁不生效；
session1 session2 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-间隙锁】
session1 session2 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-记录锁】
session1 session2 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-间隙锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
session1 session2 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; - 分析底层B+Tree构建情况：
【测试案例-02-记录锁】
当使用普通索引进行条件范围查询时，那么间隙锁查询范围所涉及到的区间，记录锁也会升级为查询范围涉及到的区间；
session1 session2 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，临键锁则也会失效。
- 分析底层B+Tree构建情况：
- 分析num列索引的B+Tree底层构建情况：
- 被查询的记录是否在临界值情况：
- 分析底层B+Tree构建情况：
- 临键锁区间：
- 索引底层构建过程：
- num索引列排列情况：
- 被查询的记录是否在临界值情况：
- 测试表：
- 区间示意图：
- 关注我们
- 扫描二维码
- 获取更多信息
- 精彩一手掌握
Copyright © 2014-2019 seo,上海网站优化_网络制作_网站seo排名_网站建设推广_上海网络公司 版权所有
沪ICP备19022315号
沪ICP备19022315号 
