在MySQL中，用记录锁或者间隙锁在啥时候合适呢？

　　在读未提交和读已提交隔离级别下，都只会使用记录锁，不会用间隙锁和NextKey锁。而对于可重复读隔离级别来说，会使用记录锁、间隙锁和NextKey锁。
　　那么MySQL啥时候会用记录锁，啥时候会用间隙锁，啥时候又会用NextKey锁呢？今天我们就来做一些测试，弄清楚这个问题。影响因素
　　在开始之前，我们需要声明的是：本文所有测试及结论的前提均是在可重复读隔离级别下，以及Innodb存储疫情下。
　　根据网上资料，我们大概可以知道，影响其使用哪种行级锁的因素有：索引类型（聚簇索引、唯一二级索引、普通二级索引）匹配类型（精确匹配、唯一匹配、范围匹配）事务隔离级别是否开启Innodblocksunsafeforbinlog系统变量记录是否被标记删除具体的执行语句类型（SELECT、INSERT、DELETE、UPDATE）
　　为了让文章相对易懂一些，我准备重点测试索引类型与匹配类型两个影响因素。对于其他的影响因素，我将不做改动。例如：事务隔离级别固定为可重复读，Innodblocksunsafeforbinlog固定为false。而第5、6点相对来说简单一些，则我们会简单带过。
　　针对上面几个影响因素，我们指定了几个测试实验，分别是：聚簇索引精确匹配聚簇索引范围匹配唯一二级索引精确匹配唯一二级索引范围匹配普通二级索引精确匹配普通二级索引范围匹配表结构CREATETABLEtest。pricetest（idBIGINT（64）NOTNULLAUTOINCREMENT，priceINT（4）NULL，PRIMARYKEY（id））；表中数据1，apple，102，orange，3050，perl，60聚簇索引精确匹配
　　为了测试聚簇索引精确匹配下加锁的类型，我们采用如下的测试方法。
　　事务A执行下面命令：selectfrompricetestwhereid2
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，其是对id为2的索引加了一个记录锁。
　　此时事务B执行下面命令：updatepricetestsetprice25whereid2；
　　执行之后，我们会发现事务B阻塞住了。
　　那如果聚簇索引的值找不到对应的记录呢，将会是一个什么样的结果呢？
　　我们再来测试一下，开始之前记得将事务A和B回滚恢复。
　　事务A执行下面命令，其中id为5的记录是不存在的：selectfrompricetestwhereid5
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，其加了一个间隙锁，该间隙锁应该是（2，50）这个范围。
　　我们可以通过在事务B执行如下命令来测试下间隙锁的范围。执行下面任何一个命令，可以通过updatepricetestsetprice25whereid2；updatepricetestsetprice25whereid50；执行下面任何一个命令，都将阻塞insertintopricetest（id，name，price）values（3，test，25）；insertintopricetest（id，name，price）values（5，test，25）；insertintopricetest（id，name，price）values（49，test，25）；
　　由此我们可以得出结论：聚簇索引精确匹配，如果能够定位到唯一一条存在的记录，那么其会使用记录锁。如果该记录不存在，那么则会使用间隙锁。聚簇索引范围匹配
　　事务A执行下面命令：selectfrompricetestwhereid2
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，事务A一共加了3个锁，其中1个记录锁，2个NextKey锁。其中1个记录锁是对id为2的索引加的锁，NextKey锁是对（2，50〕和（50，正无穷）这两个区间加的锁。
　　在事务B执行下面命令可以验证间隙锁的加锁区间：执行下面任意一条语句，都会阻塞updatepricetestsetprice25whereid2；updatepricetestsetprice25whereid50；insertintopricetest（id，name，price）values（5，test，25）；insertintopricetest（id，name，price）values（60，test，25）；
　　这里我们思考一下，如果范围匹配的值并不存在，那么会是什么情况呢？
　　即事务A执行如下语句，其中id为5的记录是不存在的。selectfrompricetestwhereid5
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，其实加了2个NextKey锁，锁的范围应该是（2，50）和〔50，无穷）。
　　此时事务B执行下面命令，应该都会阻塞。执行下面任意一条语句，都会阻塞updatepricetestsetprice25whereid50；insertintopricetest（id，name，price）values（5，test，25）；insertintopricetest（id，name，price）values（45，test，25）；insertintopricetest（id，name，price）values（60，test，25）；
　　由此我们可以得出结论：聚簇索引范围匹配，会使用记录锁间隙锁NextKey锁。唯一二级索引精确匹配
　　事务A执行下面命令：selectfrompricetestwhereprice10
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，其加的行级锁是2个记录锁，应该是price10这条索引记录的锁。
　　此时，如果在事务B执行下面命令：执行下面任意一条语句，都会阻塞updatepricetestsetnametestnamewhereprice10；
　　执行之后，我们会发现事务B阻塞住了。
　　由此我们可以得出结论：唯一二级索引与聚簇索引非常类似，都只有一个唯一值，都是使用记录锁。唯一二级索引范围匹配
　　事务A执行下面命令：selectfrompricetestwhereprice30
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，事务A一共有5个行锁，其中3个NextKey锁，2个记录锁。大致可以猜测出两个记录锁分别是price为30和60的记录锁。3个NextKey锁则是（10，30）、（30，60）、（60，正无穷）三个范围。
　　为了验证我们上面的结论，我们在事务B执行下面命令，每条SQL都会阻塞住：执行下面任意一条语句，都会阻塞updatepricetestsetnameprice30whereprice30；updatepricetestsetnameprice60whereprice60；insertintopricetest（id，name，price）values（5，test，20）；insertintopricetest（id，name，price）values（5，test，40）；insertintopricetest（id，name，price）values（5，test，70）；
　　执行之后，我们会发现事务B阻塞住了。
　　由此我们可以得出结论：唯一二级索引范围匹配，会使用记录锁间隙锁NextKey锁。普通二级索引精确匹配
　　事务A执行下面命令：
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　可以看到，其不仅有一个记录锁，还有一个间隙锁。这里可以猜测记录锁是apple索引的记录锁，而间隙锁则是（负无穷，orange）的间隙锁。
　　我们可在事务B执行如下命令验证一下：执行下面任意一条语句，都会阻塞insertintopricetest（id，name，price）values（5，aa，20）；insertintopricetest（id，name，price）values（5，ha，20）；执行下面的语句正常执行insertintopricetest（id，name，price）values（5，orb，20）；
　　之所以二级索引的精确匹配会有间隙锁，是因为二级索引可能匹配到多个。因此当匹配到一个的时候，会继续往后匹配，直到匹配到一个不符合的记录，随后就会以该不符合的记录（这里是orange）作为值做一个间隙锁。
　　由此我们可以得出结论：普通二级索引精确匹配，会使用记录锁间隙锁NextKey锁。普通二级索引范围匹配
　　事务A执行下面命令：
　　执行showengineinnodbstatusG；查看锁信息如下图所示。
　　从上图可以看到起一共有2个记录锁，3个NextKey锁。其中2个记录锁应该是orange和perl两个记录，3个NextKey锁，应该是（apple，orange〕、〔orange，perl）、〔perl，正无穷）。
　　我们可在事务B执行如下命令验证一下：执行下面任意一条语句，都会阻塞验证记录锁updatepricetestsetprice1updatepricetestsetprice1验证间隙锁insertintopricetest（id，name，price）values（5，ba，20）；insertintopricetest（id，name，price）values（5，orb，20）；insertintopricetest（id，name，price）values（5，pes，20）；执行下面的语句正常执行updatepricetestsetprice1insertintopricetest（id，name，price）values（5，aa，20）；
　　可以看到普通二级索引范围匹配与普通二级索引精确匹配结果是类似的。
　　我们可以得出结论：普通二级索引范围匹配，会使用记录锁间隙锁NextKey锁。总结
　　我们做了这么多个测试，虽然有3种索引类型（聚簇索引、唯一二级索引、普通二级索引）和2种匹配类型（精确匹配、范围匹配），它们两两组合可以得出6种情况，再加上查询的值是否存在，可能有更多的可能性。但是我们发现它们的结构都非常类似，基本上都跟查找的记录是否存在，以及查找的记录是否是唯一的相关。
　　由此，我们大致可以得出结论：如果查找的记录是唯一且存在的，那么只会使用记录锁，而不会使用间隙锁或NextKey锁。如果查找的记录不唯一或者不存在，那么就会使用NextKey锁和间隙锁。
　　通过这次测试，我们大概知道了加锁的一些原则，但实际上Innodb的关于加锁的源码还是比较复杂的。
　　Originalreprint：https：mp。weixin。qq。comsucmIfX8Jc15CP1pqhzbuZg