InnoDB引擎下的间隙锁及加锁规则

我们知道，在RR的隔离级别下，为了解决幻读问题，MySQL引入了间隙锁和Next-Key Lock。这里可以参考极客时间的 MySQL 实战 45 讲-幻读是什么，幻读有什么问题？ 这一章节，讲的很是清晰透彻。

在这篇文章中，举了一个insert on duplicate key update，当有多个唯一键时的死锁问题。这里死锁的原因其实就是因为insert on duplicate key update会加一个间隙锁。并发时多个间隙锁阻塞了真正的block操作，最后引发了死锁，可以详见 讲讲insert on duplicate key update 的死锁坑

以上，列举了一下select for update/insert on duplicate key update场景下的间隙锁以及对应的死锁问题。到这里我曾自信满满认为锁这块已经看明白了，直到真正线上生产环境遇到了死锁问题。

简单说一下我们的业务场景: 比如有一张商品属性表，我们在每次更新商品的时候，需要更新其多个属性的值。那么显然商品id是该表的一个索引且非唯一。

而业务代码也很容易写成 先根据商品id在商品属性表中删除所有记录，而后在把本次更新的内容全量插入。

这么操作看起来除了做了硬删除外也没啥问题，但是实际在RR的隔离级别下，两个完全不相干的商品在保存的时候却可能触发死锁问题。

其核心就在于，delete语句的时候，也会加上Next-key Lock。比如现在库里的最大商品id是10。A在保存id=11的商品时，由于先delete会加(10,+∞)的Next-key Lock。同理B在保存id=12的商品时，也由于delete的原因同样加了(10,+∞)的Next-key Lock。两条delete均加锁成功。而后当insert执行的时候，RR级别下insert语句会加Insert Intention Lock。此时事务A的Insert Intention Lock被事务B的Next-key Lock阻塞，同理事务B的Insert Intention Lock又被事务A的Next-key Lock阻塞。最终引发死锁问题

那么怎么解决呢？比如我们可以把RR的隔离级别下调到RC，这样由于不用防止幻读问题也就不会插入间隙锁，这个需要从实际业务场景出发看业务上是否需要避免幻读问题。

另一个方式，就是在代码层面上，我们在delete语句时不根据商品id这个字段来做where条件，即不选用普通索引来做delete语句的where条件。而是先做为selete的where条件得到所有需要删除的行的主键索引（唯一索引）而后根据这些唯一索引来做delete语句的where条件。使用唯一索引的优势时，根据MySQL的优化 唯一索引的等值查询时，Next-Key Lock会退化到行锁，此时由于不锁间隙自然对后续的insert语句不会有冲突。不过这样也可能有问题就是select出待删的主键后，在执行delete前又有人多该商品做了变更从而引发数据不一致的问题。这个其实也可以通过引入分布式锁，来保证同一商品的变更的并发控制。

最后，我们把极客时间的章节往后翻一章，章节标题是 为什么我只改一行的语句，锁这么多？ 这里作者总结了间隙锁的加锁规则，包含了两个“原则”、两个“优化”和一个“bug”。并全文举例说明该规则的应用。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

这个其实是口诀，那有没有懒人版/分析不明白版呢，其实也有，就是如下的脑图版（Next-Key锁范围针对Mysql 5.7.26）将所有的情况按照口诀规则进行了罗列，方便大家进行查询使用