大家好，我是树哥。在上一篇《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点比较简单，这里简单提一下。针对以上几个影响因素，我们指定了几个测试实验，即：聚簇索引+精确匹配聚簇索引+范围匹配唯一二级索引+精确匹配唯一二级索引+范围匹配普通二级索引+精确匹配普通二级索引+范围匹配//tablestructureCREATETABLE`test`.`price_test`(`id`BIGINT(64)NOTNULLAUTO_INCREMENT,`price`INT(4)NULL,PRIMARYKEY(`id`));//表1中的数据，apple,102,orange,3050,perl,60聚簇索引+精确匹配为了测试“聚簇索引+精确匹配”下的加锁类型，我们使用如下测试方法。事务A执行以下命令：begin；select*fromprice_testwhereid=2forupdate;执行showengineinnodbstatus\G;查看锁信息如下图所示。可以看到在id为2的索引上加了记录锁，此时事务B执行如下命令：beign;updateprice_testsetprice=25whereid=2;执行后，我们会发现事务B被阻塞了。那么如果聚簇索引的值找不到对应的记录，会是什么结果呢？我们再测试一下，开始前记得回滚事务A和B。事务A执行如下命令，其中id为5的记录不存在：begin；select*fromprice_testwhereid=5forupdate;执行showengineinnodbstatus\G;查看锁信息如下图所示。可以看到加了一个gaplock，gaplock应该在(2,50)范围内。我们可以在事务B中执行下面的命令来测试gap锁的范围。beign;//执行下面任意一条命令，可以通过updateprice_testsetprice=25whereid=2;updateprice_testsetprice=25whereid=50;//执行以下任何命令，它将阻止insertintoprice_test(id,name,price)values(3,"test",25);insertintoprice_test(id,name,price)values(5,"test",25);插入price_test(id,name,price)values(49,"test",25);由此我们可以得出结论：“聚簇索引+精确匹配”，如果能定位到唯一存在的记录，那么就会使用记录锁。如果记录不存在，则使用间隙锁。聚簇索引+范围匹配事务A执行如下命令：begin;select*fromprice_testwhereid>=2进行更新；执行showengineinnodbstatus\G;查看锁信息如下图所示。可以看出事务A一共加了3把锁，包括1把记录锁和2把Next-Key锁。其中一个record锁是对id为2的index的锁，Next-Key锁是对两个区间(2,50]和(50,positiveinfinity)的锁。在事务B中执行如下命令验证gaplock的锁定间隔：beign;//执行以下任何语句都会阻塞updateprice_testsetprice=25whereid=2;updateprice_testsetprice=25whereid=50;insertintoprice_test(id,name,price)values(5,"test",25);insertintoprice_test(id,name,price)values(60,"test",25);这里我们考虑一下，如果范围匹配值不存在，那么就会be什么情况呢，即事务A执行了如下语句，id为5的记录不存在begin;select*fromprice_testwhereid>=5forupdate;executeshowengineinnodbstatus\G;检查锁信息如下图可以看出实际上是加了两个Next-Key锁，锁的范围应该是(2,50)和[50,+infinity)。此时事务B执行了以下命令，应该都被阻塞了。beign;//执行以下任何语句将阻止更新price_testsetprice=25whereid=50;insertintoprice_test(id,name,price)values(5,"test",25);insertintoprice_test(id,name,price)values(45,"test",25);插入price_test(id,name,price)values(60,"test",25);由此我们可以得出结论：“聚集索引+范围匹配”会使用“记录锁+间隙锁+Next-Key锁”。唯一二级索引+精确匹配事务A执行如下命令：begin；选择*fromprice_testwhereprice=10forupdate;执行showengineinnodbstatus\G;查看锁信息如下图所示。可以看出加的行级锁是2个记录锁，应该是索引记录price=10的锁。此时如果在事务B中执行如下命令：beign;//执行任意一个以下语句，它将阻止更新price_testsetname='test-name'whereprice=10;执行后，我们会发现事务B被阻塞了。由此我们可以得出结论，唯一二级索引与聚簇索引非常相似，都只有一个唯一值，并且都使用记录锁。唯一二级索引+范围匹配事务A执行如下命令：begin；select*fromprice_testwhereprice>=30进行更新；执行showengineinnodbstatus\G;查看锁信息如下图所示。可以看出事务A一共有5个行锁，包括3个Next-Key锁和2个记录锁。可以大致猜到这两个记录锁分别是价格为30和60的记录锁。三个Next-Key锁在三个范围内(10,30),(30,60),(60,positiveinfinity)。为了验证我们上面的结论，我们在事务B中执行如下命令，每条SQL都会被阻塞：beign;//执行以下任意一条语句，都会被阻塞updateprice_testsetname='price30'whereprice=30;更新price_testsetname='price60'whereprice=60;insertintoprice_test(id,name,price)values(5,"test",20);insertintoprice_test(id,name,price)values(5,"test",40);插入price_test(id,name,price)values(5,"test",70);执行后，我们会发现事务B被阻塞了。由此我们可以得出结论，“唯一二级索引+范围匹配”会使用“记录锁+间隙锁+Next-Key锁”。普通二级索引+精确匹配事务A执行如下命令：begin;select*fromprice_testwherename='apple'forupdate;执行showengineinnodbstatus\G;查看锁信息如下图所示。可以看到，它不仅有记录锁，还有间隙锁。这里可以猜到recordlock就是appleindex的recordlock，gaplock就是（negativeinfinity,orange）gaplock。我们可以在事务B中执行如下命令来验证：begin;//执行以下任意一条语句都会阻塞updateprice_testsetname='apple-new'wherename='apple';insertintoprice_test(id,name,price)values(5,"aa",20);insertintoprice_test(id,name,price)values(5,"ha",20);//执行以下语句执行updateprice_testsetname='orange-new'wherename='orange';insertintoprice_test(id,name,price)values(5,"orb",20);secondaryindex的精确匹配之所以会有gaplock，是因为secondaryindex可能会匹配很多individual。因此，当匹配到一条时，会继续匹配，直到匹配到一条不符合的记录，然后以不符合的记录（这里橙色）为值进行间隙锁。由此我们可以得出一个结论：“普通二级索引+精确匹配”会使用“记录锁+间隙锁+Next-Key锁”。普通二级索引+范围匹配事务A执行如下命令：begin;select*fromprice_testwherename>='orange'进行更新；执行showengineinnodbstatus\G;查看锁信息如下图所示。从上图可以看出，有2个记录锁和3个Next-Key锁。其中2个记录锁应该是orange和perl记录，3个Next-Key锁应该是(apple,orange],[orange,perl),[perl,positiveinfinity)。我们可以在事务B中执行如下命令来验证：begin;//执行下面任意一条语句，都会阻塞//验证记录锁updateprice_testsetprice=1wherename='orange';updateprice_testsetprice=1wherename='perl';//验证间隙锁insertintoprice_test(id,name,price)values(5,"ba",20);insertintoprice_test(id,name,price)values(5,"orb",20);insertintoprice_test(id,name,price)values(5,"pes",20);//执行下面语句正常执行updateprice_testsetprice=1wherename='apple';insertintoprice_test(id,name,price)值(5,”aa”,20);可以看到“普通二级索引+范围匹配”和“普通二级索引+精确匹配”的结果是差不多的。我们可以得出结论，“普通二级索引+范围匹配”会使用“记录锁+间隙锁+Next-Key锁”。综上所述，我们做了这么多测试。虽然有3种索引类型（聚集索引、唯一二级索引、普通二级索引）和2种匹配类型（精确匹配、范围匹配），但是可以两两组合得到6种情况，加上是否查询的值存在，或许会有更多的可能。但是我们发现他们的结构非常相似，基本上都与查找的记录是否存在，查找的记录是否唯一有关。由此我们可以大致得出结论，如果要查找的记录唯一且存在，则只使用记录锁，不会使用间隙锁或Next-Key锁。如果搜索到的记录不唯一或不存在，则使用Next-Key锁和间隙锁。通过这次测试，我们大概知道了一些加锁的原理，但实际上Innodb加锁的源码是相当复杂的。有篇文章写的很不错。这篇文章可以说做了一些简化。有兴趣的朋友可以自行阅读：完整版：Innodb是如何加锁的。完整版参考资料：Innodb如何加锁？[Lock]MySQLgaplock-阿里云开发者社区MySQLnext-keylock锁定范围是多少？-SegmentFault思否
