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

两个事务同时以主键为where条件操作不存在的列时,为什么RC级别下事务2的delete不会阻塞事务1的insert,在RR级别下事务2的delete就会阻塞事务1的insert?

作者 李文航·沃趣科技数据库技术专家
出品 沃趣科技

上篇文章《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调优、数据库故障诊断、数据迁移、备份恢复

发表评论

电子邮件地址不会被公开。 必填项已用*标注