sysbench花式踩坑之三：自增值导致的锁等待

上篇文章 《sysbench花式采坑之二：自增值导致的主键冲突》 遗留了一个问题，为什么在RR级别下delete一条id不存在的列，再在另一个事务用同样的id插入一条数据会发生锁等待，在RC级别下就不会出现锁等待？

| 自增值导致的锁等待

看到RR级别和RC级别下不同的锁等待表现，第一个应该想到的原因就是gap锁。

我们知道gap锁会阻塞insert，那么delete会产生gap锁吗？借用mysql 8.0新加入的performance_schema.data_locks观察一下。

首先准备测试所用的表结构和表数据。  

id为主键列，age为索引列，首先我们删除id为12的这条数据，观察一下锁信息。

可以看到delete产生了一个意向排它表锁和一个排它行锁。

回滚这个事务，然后我们删除一条id不存在的数据，观察一下锁信息。

发现行锁变成了gap锁，这时候我们往锁住的范围里面做insert操作都是会等待的。 

这时通过performance_schema.data_locks查看锁信息

• 事务1 

• 事务2  

• 锁信息 

发现表中显示的insert的事务有一条GAP锁正在等待delete的GAP锁。

这里竟然是GAP锁等待GAP锁，前面也验证了，两条对同一个不存在的id进行delete的语句相互之间不会冲突，也就是说delete产生的GAP锁不会阻塞delete产生的GAP锁。

• 事务1 

• 事务2 

• 锁信息

可以看到，上述两个锁信息的图中除了第一张的lock status为waiting外，其他内容完全相同，这么看来performance_schema.data_locks里面记录的信息不是太全面，那我们看一下insert锁等待的时候具体点的锁信息吧。

• 事务1 

• 事务2 

• show engine innodb status\G 

发现insert持有的锁为insert intention lock，那么这个insert intention lock为什么在performance_schema.data_locks显示为GAP呢，看一下官方文档insert intention lock的解释。 

第一句就表明了insert intention lock是GAP锁的一种，因此performance_schema.data_locks显示为GAP好像也没什么毛病，只是和两个delete的锁信息对比起来就比较容易让人困扰了，再上图仔细感受一下：  

| 总结

• 对不存在的行以id为where条件进行delete或者update的时候会产生gap lock；

• gap lock和gap lock之间互相兼容；

• insert intention lock是一种特殊的gap lock，当先持有gap lock时，会阻塞后面的insert intention lock；

• sysbench压测的时候自增值要设置为1，否则对数据的间隙进行dml的时候存在很多问题。

| 作者简介

李文航·沃趣科技数据库技术专家

熟悉MySQL体系结构和工作原理、SQL调优、数据库故障诊断、数据迁移、备份恢复

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28218939/viewspace-2636609/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28218939/viewspace-2636609/