性能文章>一次线上报错引起对 MySQL 间隙锁的研究>

一次线上报错引起对 MySQL 间隙锁的研究转载

3周前
189103

导语

间隙锁是一个在索引记录之间的间隙上的锁,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,也造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。本篇从mysql角度向大家介绍间隙锁。

正文

报错分析

 
一次线上错误日志显示锁等待超时,错误提示为 Lock wait timeout exceeded;。
 
异常发生时,从其他日志、业务场景排除了单个业务并发操作的可能性,因此数据库锁产生是因为不同业务的操作引起的。
 

一次线上报错引起对 MySQL 间隙锁的研究数据图表-heapdump性能社区

 
我们数据库环境为 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)
  )
还原当时的场景为:
 
事务一 事务二
 
一次线上报错引起对 MySQL 间隙锁的研究数据图表-heapdump性能社区
为什么事务二插入数据 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锁的问题大家可以阅读以下内容加深了解

因索引合并导致的MySQL死锁分析与解决实战!

MySQL 死锁套路:再来看一例走不同索引更新的例子

 

分类:
标签:
请先登录,再评论

暂无回复,快来写下第一个回复吧~

为你推荐

构建企业级业务高可用的延时消息中台
业务场景剖析公司业务系统(比如:电商系统)中有大量涉及定时任务的业务场景,例如:实现买卖双方在线沟通的IM系统,为了确保接收方能够收到消息,服务端一般都会有重试策略,即服务端在消息发出的一段时间内,如
5G时代,如何彻底搞定海量数据库的设计与实践
5G时代,业务数据越来越丰富,业务使用MySQL数据库作为后台存储,存储引擎使用InnoDB,会带来哪些挑战?如何针对公司业务特点及MySQL数据库特性,制定若干数据库使用规范供一线RD在设计业务时参
Redis client链接池配置不当引起的频繁full gc
现象笔者负责的一个RPC服务就是简单的从Redis Cluster中读取数据,然后返回给上游。理论上该服务的对象大部分都应该是朝生夕死的,但是笔者查看gc log 的时候发现 age =2 的对象还真
记一次线上请求偶尔变慢的排查
前言最近解决了个比较棘手的问题,由于排查过程挺有意思,于是就以此为素材写出了本篇文章。 Bug现场这是一个偶发的性能问题。在每天几百万比交易请求中,平均耗时大约为300ms,但总有那么100多笔会超过
MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
记一次中间件导致的慢SQL排查过程
前言最近发现线上出现一个奇葩的问题,这问题让笔者定位了好长时间,期间排查问题的过程还是挺有意思的,正好博客也好久不更新了,就以此为素材写出了本篇文章。 Bug现场我们的分库分表中间件在经过一年的沉淀之
解Bug之路-主从切换"未成功"?
前言数据库主从切换是个非常有意思的话题。能够稳定的处理主从切换是保证业务连续性的必要条件。今天笔者就来讲讲主从切换过程中一个小小的问题。 故障场景最近线上进行主从切换,大部分应用都切过去了,但是某些应
一次线上报错引起对 MySQL 间隙锁的研究
导语间隙锁是一个在索引记录之间的间隙上的锁,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,也造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很