一次线上报错引起对 MySQL 间隙锁的研究转载
2年前
489514
导语
间隙锁是一个在索引记录之间的间隙上的锁，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，也造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。本篇从mysql角度向大家介绍间隙锁。
正文
报错分析
一次线上错误日志显示锁等待超时，错误提示为 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};
更多思考
mysql的锁是非常多的，更多关于mysql锁的问题大家可以阅读以下内容加深了解
点赞收藏
分类：
