上海网页制作与网站设计,软件公司招聘最新信息,微信小程序开发注册,wordpress sql查询分类MySQL 啥时候会用记录锁#xff0c;啥时候会用间隙锁#xff0c;啥时候又会用 Next-Key 锁呢#xff1f;今天我们就来做一些测试#xff0c;弄清楚这个问题。文章思维导图影响因素在开始之前#xff0c;我们需要声明的是#xff1a;本文所有测试及结论的前提均是在「可重…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 status\G; 查看锁信息如下图所示。可以看到其是对 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 status\G; 查看锁信息如下图所示。可以看到其加了一个间隙锁该间隙锁应该是 (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 status\G; 查看锁信息如下图所示。可以看到事务 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 status\G; 查看锁信息如下图所示。可以看到其实加了 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 status\G; 查看锁信息如下图所示。可以看到其加的行级锁是 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 status\G; 查看锁信息如下图所示。可以看到事务 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 status\G; 查看锁信息如下图所示。可以看到其不仅有一个记录锁还有一个间隙锁。这里可以猜测记录锁是 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 status\G; 查看锁信息如下图所示。从上图可以看到起一共有 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 锁和间隙锁。原文https://mp.weixin.qq.com/s/ucmIfX8Jc15CP1pqhzbuZg作者树哥聊编程