报错分析

一次线上错误日志显示锁等待超时，错误提示为 Lock wait timeout exceeded;。

异常发生时，从其他日志、业务场景排除了单个业务并发操作的可能性，因此数据库锁产生是因为不同业务的操作引起的。

我们数据库环境为 MySQL，存储引擎是 InnoDB，事务隔离级别可重复读。

表结构如下：

t (
  id bigint(20) NOT NULL COMMENT '主键 id',
  biz_id bigint(20) NOT NULL COMMENT '业务 id', 
  field varchar(200) NOT NULL COMMENT '字段 1',
  PRIMARY KEY (id),
  KEY idx_biz_id (biz_id)
  )

还原当时的场景为：

为什么事务二插入数据 blocked 了呢？这是因为 InnoDB 的间隙锁（Gap Lock）导致的。

幻读

说到间隙锁，首先要提到一个词：幻读。

事务 A 按照一定条件进行数据读取， 期间事务 B 插入了相同搜索条件的新数据，事务 A 再次按照原先条件进行读取时，发现了事务 B 新插入的数据，这种现象称为幻读。

间隙锁是 InnoDB 在可重复读的事务隔离级别下为了解决幻读问题时引入的锁机制。

幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。

在可重复读隔离级别下，数据库是通过行锁和间隙锁共同组成的（next-key lock），来实现解决幻读问题。

行锁也称记录锁，记录锁是加在索引上的锁。

间隙锁

间隙锁是在索引记录之间的间隙上的锁，或者是在第一个索引记录以前或最后一个索引记录以后的间隙上的锁。

例如，SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20;  能够阻止其余事务插入 t.c1 = 15 的记录，不论列中是否已有此值，由于在此范围内的全部现有值之间的间隙被锁定了。

间隙可能会跨越单个索引值，多个索引值，甚至空值。

当使用唯一索引查询唯一行时不会使用间隙锁。

（这不包含此种状况，查询条件只包含多列唯一索引的一部分字段；这种状况下仍是会用到间隙锁。）

例如，若是 id 列上有唯一索引，下面的语句仅对 id 等于 100 的行使用索引记录锁，不会关心其余会话是否在间隙以前插入行：SELECT * FROM child WHERE id = 100;

若 id 字段未加索引或者为非唯一索引，此语句会锁住 id = 100 记录前面的间隙。

值得注意的是经过不一样的事务相互冲突的锁能够持有同一个间隙。

例如，事务 A 在一个间隙上持有共享间隙锁（gap S-lock），同时事务 B 能够在此间隙上持有排他间隙锁（gap X-lock）。

这种状况被容许的缘由是：若是一个记录从索引上被清除，则此记录上被不一样事务持有的间隙锁必须合并。

在 InnoDB 中，间隙锁是“彻底被抑制（purely inhibitive）”的，也就是说它只会阻止其余事务在间隙中进行插入操作。

它不会阻止不一样的事务在同一间隙上获取间隙锁。所以，排他间隙锁跟共享间隙锁具备相同的效果。

禁用间隙锁

当事务隔离级别设置为 READ COMMITTED 或者 启用系统变量 innodb_locks_unsafe_for_binlog 时，间隙锁能够被显示禁用。

在这种状况下，间隙锁在查询和索引扫描时会被禁用，仅在外键约束检查和唯一性检查时启用。

当事务隔离级别设置为 READ COMMITTED 或者启用系统变量 innodb_locks_unsafe_for_binlog 时还有其余效果。

MySQL 在评估完 WHERE 条件后针对不匹配的行会释放记录锁。

对于 UPDATE 语句，InnoDB 做了半一致性（semi-consistent）读，它会返回最后提交的版本到 MySQL，以便 MySQL 能够决定这行是否跟 UPDATE 语句的 WHERE 条件匹配。

改进措施

问题：

除了将长事务优化、将 MySQL 隔离级别降级为 读已提交 以外，在 RR 隔离级别下还有什么办法可以避免此类现象的锁等待？

答案：

SQL 优化，避免出现间隙锁。

本文的删除语句 delete from t where biz_id= #{bizId} 可以改成查询后，依据主键删除即可避免间隙锁。

因为索引上的等值查询, 给唯一索引加锁的时候, next-key lock 会退化为行锁。

依据主键删除，只会对访问的数据加行锁。

修改方案：

select id from t where biz_id= #{bizId};

delete from t where id = #{id};

招贤纳士

政采云技术团队（Zero），一个富有激情、创造力和执行力的团队，Base 在风景如画的杭州。团队现有300多名研发小伙伴，既有来自阿里、华为、网易的“老”兵，也有来自浙大、中科大、杭电等校的新人。团队在日常业务开发之外，还分别在云原生、区块链、人工智能、低代码平台、中间件、大数据、物料体系、工程平台、性能体验、可视化等领域进行技术探索和实践，推动并落地了一系列的内部技术产品，持续探索技术的新边界。此外，团队还纷纷投身社区建设，目前已经是 google flutter、scikit-learn、Apache Dubbo、Apache Rocketmq、Apache Pulsar、CNCF Dapr、Apache DolphinScheduler、alibaba Seata 等众多优秀开源社区的贡献者。

如果你想改变一直被事折腾，希望开始折腾事；如果你想改变一直被告诫需要多些想法，却无从破局；如果你想改变你有能力去做成那个结果，却不需要你；如果你想改变你想做成的事需要一个团队去支撑，但没你带人的位置；如果你想改变本来悟性不错，但总是有那一层窗户纸的模糊……如果你相信相信的力量，相信平凡人能成就非凡事，相信能遇到更好的自己。如果你希望参与到随着业务腾飞的过程，亲手推动一个有着深入的业务理解、完善的技术体系、技术创造价值、影响力外溢的技术团队的成长过程，我觉得我们该聊聊。任何时间，等着你写点什么，发给 [email protected]