写点什么

MySQL innoDB 间隙锁产生的死锁问题 | 京东云技术团队

  • 2023-10-07
    北京
  • 本文字数:2084 字

    阅读完需:约 7 分钟

MySQL innoDB 间隙锁产生的死锁问题 | 京东云技术团队

背景

线上经常偶发死锁问题,当时处理一张表,也没有联表处理,但是有两个 mq 入口,并且消息体存在一样的情况,频率还不是很低,这么一个背景,我非常容易怀疑到,两个消息同时近到这一个事务里面导致的,但是是偶发的,又模拟不出来什么场景会导致死锁,只能进行代码分析,问题还原的方式去排查问题。


业务代码简化成下面


begin

update test set yn = 0 where dm_code = "3";SELECT * from test where dm_code = '3'INSERT INTO demand_flow_followers (dm_code, erp )values('3', 'a'),('3', 'b'),('3', 'c')
复制代码


也就是说先 update ,select , insert 这么一个顺序


表中存在 dm_code ,erp 唯一索引


如果不存在索引 第一行 update 会导致行锁升级为表锁,反而不会导致问题出现,但是并发太差

结论

先说结论:



重点: 无论哪个事务 insert,两个事务必须都 update 完成,只要满足这个条件,两个 insert 执行的时候就会报死锁


原因:我先按照自己的理解解释下:


innodb 的行锁,存在间隙锁,为啥要去有索引,如果没有索引,第一个 update 就直接进行了表锁,这样导致另外一个事务无法进入,就只能进行等待了。


有索引的情况下:


两个事务都执行 update,都拿到了[当前值,+∞) 的锁(记录锁+间隙锁),(update 的时候,无数据命中)


第一个 insert 时,希望等待另外一个事务释放锁。第二个事务希望第一个事务释放锁,因此出现了死锁问题

相关知识梳理

InnoDB 有三种行锁的算法:


1.Record Lock:是加在索引记录上的。


2.Gap Lock(间隙锁):对索引记录间的范围加锁,或者加在最后一个索引记录的前面或者后面


3.Next-Key Lock:前两种锁的结合,锁定一个范围,并且锁定记录本身,主要目的是解决幻读的问题。


间隙锁主要是防止幻象读,用在 Repeated-Read(简称 RR)隔离级别下。在 Read-Commited(简称 RC)下,一般没有间隙锁(有外键情况下例外,此处不考虑)。间隙锁还用于 statement based replication


间隙锁有些副作用,如果要关闭,一是将会话隔离级别改到 RC 下,或者开启 innodb_locks_unsafe_for_binlog(默认是 OFF)。


间隙锁(无论是 S 还是 X)只会阻塞 insert 操作。


CREATE TABLE `test` (

`id` bigint(20) NOT NULL,

`k` bigint(20) DEFAULT '0',

PRIMARY KEY (`id`),

KEY `idx_k` (`k`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8INSERT into test values(2,2),(5,5),(10,10)
复制代码


select @@global.tx_isolation, @@tx_isolation;
复制代码


RR 隔离级别


delete from test where k=5;
复制代码

session2

insert into test (id,k) values (3,3)insert into test (id,k) values (4,4)insert into test (id,k) values (6,6)insert into test (id,k) values (7,7)insert into test (id,k) values (8,8)insert into test (id,k) values (9,9)
复制代码


上面都报错:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


这个证明 id (3,5)都被间隙锁锁住了


insert into test (id,k) values (1,1)insert into test (id,k) values (11,11)delete from test where id in (1,11)
复制代码


(3,5) 区间之外都可以执行 insert,delete 操作


可以看到,delete k=5 的记录阻塞了 k=3、4、5、6、7、8、9 记录的插入操作,事实上,除了对于 k=5 这条记录上 record lock 之外,innoDB 对于 delete 和 update 在辅助索引(非主键索引)上的条件时会对扫过的记录上间隙锁,为了防止幻读,会锁住 k=5 这条记录的前面一条记录(id=2,k=2)到后面一条记录(id=10,k=10)之间的区间,即锁住 k 在区间(2,10)的范围(如果没有后一条记录,一直锁到正无穷),至于在边界 k=2 及 k=10 上,由于索引内是按照主键排序的,不会锁住(id<2,k=2)但是会锁住(id>2,k=2),同理不会锁住(id>10,k=10)但是会锁住(id<10,k=10).


insert into test (id,k) values (1,2) okinsert into test (id,k) values (11,2) noinsert into test (id,k) values (11,9) noinsert into test (id,k) values (11,10) okinsert into test (id,k) values (1,10) noinsert into test (id,k) values (11,10) ok
复制代码


由于索引内是按照主键排序的,不会锁住(id<2,k=2)但是会锁住(id>2,k=2),同理不会锁住(id>10,k=10)但是会锁住(id<10,k=10).


值得注意的是,delete 和 update 在唯一索引(primary key/unique key)上更新存在的记录时只会上行级记录锁(record key),而在唯一索引上更新不存在的记录时同辅助索引一样会上间隙锁;在上例中,delete id=5 只会在(id=5,k=5)这条记录上上 X 锁,而 delete id=7 却会锁住(id>5&&id<10)这个区间。

线上问题还原


重点: insert 之前两个回话都执行完 update


SQL 错误 [1213] [40001]: Deadlock found when trying to get lock; try restarting transaction
复制代码

解决办法:

避免更新或者删除不存在的记录,虽然更新存在的记录也会产生间隙锁,但是间隙锁锁住的范围会更小;


更新不存在的记录会锁住意想不到的区间范围,极其容易导致死锁问题


这些仅仅是解决问题的一个小的技巧,不能从根本上解决问题,如果想从根本上解决就从代码级别上加锁,这样避免了这种问题,但是同时并发就小了,根据自己的实际情况进行定夺方案


作者:京东零售 吴法刚

来源:京东云开发者社区 转载请注明来源

发布于: 刚刚阅读数: 3
用户头像

拥抱技术,与开发者携手创造未来! 2018-11-20 加入

我们将持续为人工智能、大数据、云计算、物联网等相关领域的开发者,提供技术干货、行业技术内容、技术落地实践等文章内容。京东云开发者社区官方网站【https://developer.jdcloud.com/】,欢迎大家来玩

评论

发布
暂无评论
MySQL innoDB 间隙锁产生的死锁问题 | 京东云技术团队_MySQL_京东科技开发者_InfoQ写作社区