在MySQL中,用记录锁或者间隙锁在啥时候合适呢?
在「读未提交」和「读已提交」隔离级别下,都只会使用记录锁,不会用间隙锁和 Next-Key 锁。而对于「可重复读」隔离级别来说,会使用记录锁、间隙锁和 Next-Key 锁。
那么 MySQL 啥时候会用记录锁,啥时候会用间隙锁,啥时候又会用 Next-Key 锁呢?今天我们就来做一些测试,弄清楚这个问题。 影响因素
在开始之前,我们需要声明的是:本文所有测试及结论的前提均是在「可重复读」隔离级别下,以及 Innodb 存储疫情下。
根据网上资料,我们大概可以知道,影响其使用哪种行级锁的因素有: 索引类型(聚簇索引、唯一二级索引、普通二级索引) 匹配类型(精确匹配、唯一匹配、范围匹配) 事务隔离级别 是否开启 Innodb_locks_unsafe_for_binlog 系统变量 记录是否被标记删除 具体的执行语句类型(SELECT、INSERT、DELETE、UPDATE)
为了让文章相对易懂一些,我准备重点测试索引类型与匹配类型两个影响因素。对于其他的影响因素,我将不做改动。例如:事务隔离级别固定为「可重复读」,Innodb_locks_unsafe_for_binlog 固定为 false。而第 5、6 点相对来说简单一些,则我们会简单带过。
针对上面几个影响因素,我们指定了几个测试实验,分别是: 聚簇索引 + 精确匹配 聚簇索引 + 范围匹配 唯一二级索引 + 精确匹配 唯一二级索引 + 范围匹配 普通二级索引 + 精确匹配 普通二级索引 + 范围匹配 // 表结构 CREATE TABLE `test`.`price_test` ( `id` BIGINT(64) NOT NULL AUTO_INCREMENT, `price` INT(4) NULL, PRIMARY KEY (`id`)); // 表中数据 1, apple, 10 2, orange, 30 50, perl, 60聚簇索引 + 精确匹配
为了测试「聚簇索引 + 精确匹配」下加锁的类型,我们采用如下的测试方法。
事务 A 执行下面命令: begin; select * from price_test where id = 2 for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,其是对 id 为 2 的索引加了一个记录锁。
此时事务 B 执行下面命令: beign; update price_test set price = 25 where id = 2;
执行之后,我们会发现事务 B 阻塞住了。
那如果聚簇索引的值找不到对应的记录呢,将会是一个什么样的结果呢?
我们再来测试一下,开始之前记得将事务 A 和 B 回滚恢复。
事务 A 执行下面命令,其中 id 为 5 的记录是不存在的: begin; select * from price_test where id = 5 for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,其加了一个间隙锁,该间隙锁应该是 (2, 50) 这个范围。
我们可以通过在事务 B 执行如下命令来测试下间隙锁的范围。 beign; // 执行下面任何一个命令,可以通过 update price_test set price = 25 where id = 2; update price_test set price = 25 where id = 50; // 执行下面任何一个命令,都将阻塞 insert into price_test(id,name,price) values(3,"test",25); insert into price_test(id,name,price) values(5,"test",25); insert into price_test(id,name,price) values(49,"test",25);
由此我们可以得出结论: 「聚簇索引 + 精确匹配」,如果能够定位到唯一一条存在的记录,那么其会使用记录锁。如果该记录不存在,那么则会使用间隙锁。 聚簇索引 + 范围匹配
事务 A 执行下面命令: begin; select * from price_test where id >= 2 for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,事务 A 一共加了 3 个锁,其中 1 个记录锁,2 个 Next-Key 锁。其中 1 个记录锁是对 id 为 2 的索引加的锁,Next-Key 锁是对 (2, 50] 和 (50, 正无穷) 这两个区间加的锁。
在事务 B 执行下面命令可以验证间隙锁的加锁区间: beign; // 执行下面任意一条语句,都会阻塞 update price_test set price = 25 where id = 2; update price_test set price = 25 where id = 50; insert into price_test(id,name,price) values(5,"test",25); insert into price_test(id,name,price) values(60,"test",25);
这里我们思考一下,如果范围匹配的值并不存在,那么会是什么情况呢?
即事务 A 执行如下语句,其中 id 为 5 的记录是不存在的。 begin; select * from price_test where id >= 5 for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,其实加了 2 个 Next-Key 锁,锁的范围应该是 (2, 50) 和 [50, + 无穷)。
此时事务 B 执行下面命令,应该都会阻塞。 beign; // 执行下面任意一条语句,都会阻塞 update price_test set price = 25 where id = 50; insert into price_test(id,name,price) values(5,"test",25); insert into price_test(id,name,price) values(45,"test",25); insert into price_test(id,name,price) values(60,"test",25);
由此我们可以得出结论: 「聚簇索引 + 范围匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。 唯一二级索引 + 精确匹配
事务 A 执行下面命令: begin; select * from price_test where price = 10 for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,其加的行级锁是 2 个记录锁,应该是 price = 10 这条索引记录的锁。
此时,如果在事务 B 执行下面命令: beign; // 执行下面任意一条语句,都会阻塞 update price_test set name = "test-name" where price = 10;
执行之后,我们会发现事务 B 阻塞住了。
由此我们可以得出结论: 唯一二级索引与聚簇索引非常类似,都只有一个唯一值,都是使用记录锁。 唯一二级索引 + 范围匹配
事务 A 执行下面命令: begin; select * from price_test where price >= 30 for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,事务 A 一共有 5 个行锁,其中 3 个 Next-Key 锁, 2 个记录锁。大致可以猜测出两个记录锁分别是 price 为 30 和 60 的记录锁。3 个 Next-Key 锁则是 (10, 30)、(30,60)、(60, 正无穷)三个范围。
为了验证我们上面的结论,我们在事务 B 执行下面命令,每条 SQL 都会阻塞住: beign; // 执行下面任意一条语句,都会阻塞 update price_test set name = "price30" where price = 30; update price_test set name = "price60" where price = 60; insert into price_test(id,name,price) values(5,"test", 20); insert into price_test(id,name,price) values(5,"test", 40); insert into price_test(id,name,price) values(5,"test", 70);
执行之后,我们会发现事务 B 阻塞住了。
由此我们可以得出结论: 「唯一二级索引 + 范围匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。 普通二级索引 + 精确匹配
事务 A 执行下面命令: begin; select * from price_test where name = "apple" for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
可以看到,其不仅有一个记录锁,还有一个间隙锁。这里可以猜测记录锁是 apple 索引的记录锁,而间隙锁则是 (负无穷,orange) 的间隙锁。
我们可在事务 B 执行如下命令验证一下: begin; // 执行下面任意一条语句,都会阻塞 update price_test set name = "apple-new" where name = "apple"; insert into price_test(id,name,price) values(5,"aa", 20); insert into price_test(id,name,price) values(5,"ha", 20); // 执行下面的语句正常执行 update price_test set name = "orange-new" where name = "orange"; insert into price_test(id,name,price) values(5,"orb", 20);
之所以二级索引的精确匹配会有间隙锁,是因为二级索引可能匹配到多个。因此当匹配到一个的时候,会继续往后匹配,直到匹配到一个不符合的记录,随后就会以该不符合的记录(这里是 orange)作为值做一个间隙锁。
由此我们可以得出结论: 「普通二级索引 + 精确匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。 普通二级索引 + 范围匹配
事务 A 执行下面命令: begin; select * from price_test where name >= "orange" for update;
执行 show engine innodb statusG; 查看锁信息如下图所示。
从上图可以看到起一共有 2 个记录锁,3 个 Next-Key 锁。其中 2 个记录锁应该是 orange 和 perl 两个记录,3 个 Next-Key 锁,应该是 (apple, orange]、[orange, perl)、[perl, 正无穷)。
我们可在事务 B 执行如下命令验证一下: begin; // 执行下面任意一条语句,都会阻塞 // 验证记录锁 update price_test set price = 1 where name = "orange"; update price_test set price = 1 where name = "perl"; // 验证间隙锁 insert into price_test(id,name,price) values(5,"ba", 20); insert into price_test(id,name,price) values(5,"orb", 20); insert into price_test(id,name,price) values(5,"pes", 20); // 执行下面的语句正常执行 update price_test set price = 1 where name = "apple"; insert into price_test(id,name,price) values(5,"aa", 20);
可以看到「普通二级索引 + 范围匹配」与「普通二级索引 + 精确匹配」结果是类似的。
我们可以得出结论: 「普通二级索引 + 范围匹配」,会使用「记录锁 + 间隙锁 + Next-Key 锁」。 总结
我们做了这么多个测试,虽然有 3 种索引类型(聚簇索引、唯一二级索引、普通二级索引)和 2 种匹配类型(精确匹配、范围匹配),它们两两组合可以得出 6 种情况,再加上查询的值是否存在,可能有更多的可能性。 但是我们发现它们的结构都非常类似,基本上都跟查找的记录是否存在,以及查找的记录是否是唯一的相关。
由此,我们大致可以得出结论: 如果查找的记录是唯一且存在的,那么只会使用记录锁,而不会使用间隙锁或 Next-Key 锁。 如果查找的记录不唯一或者不存在,那么就会使用 Next-Key 锁和间隙锁。
通过这次测试,我们大概知道了加锁的一些原则,但实际上 Innodb 的关于加锁的源码还是比较复杂的。
Original reprint:https://mp.weixin.qq.com/s/ucmIfX8Jc15CP1pqhzbuZg
40岁以后T恤要少穿,不显气质,多穿这4件夏季衫,更优雅时髦中年女人,追求的已不是时尚,而是自成一派适合自己的气质,体现在穿衣打扮上,更需要突出一种高级和精致。夏季无疑是T恤泛滥的季节,看似具有无龄感风格又惬意的衣服,如果没有好的身材和搭配
认识维生素B2,它对我们人体有什么作用呢?维生素是一类人体必需,但自身不能合成,必需从食物中摄取的小分子有机化合物。虽然不能像宏量营养素那样,为身体提供能量构成细胞,但是参与身体新陈代谢,缺乏会导致严重的健康问题。关于维生
球场暴力何时休?中超联赛激战正酣,在刚刚过去的中超第八轮比赛中,裁判员的一些判罚引起了外界的热议,体坛新视野继续邀请资深国际级裁判王学智老师,就本轮中超赛场上出现的争议判罚,为大家一一进行分析和解
百亿富豪朱树豪患癌坚持工作,带着氧气瓶看项目,去世留260亿要说到最受喜爱的体育运动,每个人的答案可能都不相同,但是,如果要是谈到最优雅的体育运动是什么,那么高尔夫一定是很多人心中的首选。新中国自成立以来,从百废待兴到国富民强,这其中少不了
官宣!防守悍将加盟太阳,勇士被马刺截胡,篮网达成两笔冒险引援外媒MassLive带来一篇关于凯尔特人的最新报道,据知情人透露,在确定已经得到布罗格登的情况下,凯尔特人接下来使用交易特例的概率几乎为零,因为目前市面上找不到心仪的补强人选,再加
7月刚开始,已有8位明星去世,最小23岁,有人独居离世无人知7月刚刚开始没多久,娱乐圈就陆续传出8位明星离世的消息,这8位明星中有人为文娱界做出过重大的贡献,有人正值青春年华,他们的离开对娱乐圈来说也是一大遗憾。1,朱俐静,40岁7月4日,
谢娜终于走上高级的台阶,浪姐3中衣品飞升,款款惊艳又时髦你的衣着,能为你的气质锦上添花。高级感这词,在时尚界中,说白了其实就是一种气质,有时尚范儿的阐述。日常生活中,我们往往可以通过服饰造型去提升一个人的气质,对于作为主持人的谢娜也不例
浪姐3五公淘汰2人,郑秀妍队3连败,王心凌成赢家,成团位9人文烤娱作为芒果扛把子级别的综艺,乘风破浪的姐姐来到第3季却遭遇滑铁卢。节目选曲编曲和服装造型槽点满满,公演舞台缺少亮点,最尴尬的观众投票一直为人诟病,野蔷薇梦中人佳人等优秀的舞台作
情歌教父他是真称得上情歌教父的男人。人生太戏剧性,称得上命途多舛一生悲苦了。出道时叫小刚,也就是他小名。周传雄脍炙人口的歌包括但不限于黄昏寂寞沙洲冷关不上的窗我的心太乱男人海洋青花等等,还
孟买女人即使出身低微,也要争取属于自己的尊严印度片孟买女人描述一个富家千金,受到男友欺骗而被卖到妓院,在经历各种劫难,看透社会底层生活之后,逐渐成为当地的黑道领袖,最后演变成女权斗士,向社会争取权利的传奇事迹,整部电影透过夸
岁月神偷,还是带走了他此文为高能E蓓子原创,禁止任何形式的转载,转载请后台联系,但欢迎你们转发到朋友圈。昨天,传来倪匡去世的消息任何有倪匡的地方,就有有趣的故事当日娱乐圈还传来另一噩耗69岁著名导演编剧