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

京东云开发者
• 阅读 306

背景

线上经常偶发死锁问题,当时处理一张表,也没有联表处理,但是有两个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 会导致行锁升级为表锁,反而不会导致问题出现,但是并发太差

结论

先说结论:

session1 session2
开启事务
update
开启事务
update
insert
insert出现死锁

重点: 无论哪个事务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=utf8
INSERT 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) ok
insert into test (id,k) values (11,2) no
insert into test (id,k) values (11,9) no
insert into test (id,k) values (11,10) ok
insert into test (id,k) values (1,10) no
insert 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)这个区间。

线上问题还原

session1 session2
begin
begin
update test set k = 20 where id = 20
update test set k = 20 where id = 20
INSERT into test values(25,25)
INSERT into test values(25,25)

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

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


解决办法:

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

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

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

作者:京东零售 吴法刚

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

点赞
收藏
评论区
推荐文章
Wesley13 Wesley13
3年前
mysql数据库先update后insert死锁分析
先update再insert的并发死锁问题分析。背景“如果库里有对应记录,就更新,没有就插入”很简单的一个逻辑,相信很多人都会遇到。最近看一个工程里实现代码是这样的,mysql数据库走的是默认的事务级别:可重复读。包在一个事务中执行:ifupdate更新结果0thenreturn"成功";el
Wesley13 Wesley13
3年前
mysql查询每个学生的各科成绩,以及总分和平均分
今天看一个mysql教程,看到一个例子,感觉里面的解决方案不是很合理。问题如下:有学生表:!在这里插入图片描述(https://oscimg.oschina.net/oscnet/07b001b0c6cb7e0038a9299e768fc00a0d3.png)成绩表:!在这里插入图片描述(https://oscimg.o
Wesley13 Wesley13
3年前
Java多线程之死锁编码及定位分析
目录死锁是什么代码实现死锁解决办法1\.死锁是什么死锁是指两个或两个以上的进程在执行过程中因争夺资而造成的一种互相等待的现象,若无外力干涉那它们都将无法推进下去,如果系统资源允是,进程的资源请求都能够得到满是,死锁出现的可能性就很低,否则就会因争夺有限的资源而陷入死锁。2.代码实现代码实现import
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Wesley13 Wesley13
3年前
Java并发编程之锁的活跃性问题
引子在安全性和活跃性之间通常存在一种制衡。当我们使用锁来保证线程的安全的同时,如果过度使用加锁,可能会导致死锁。应用无法从死锁中恢复过来,所以在设计时一定要避免会排除这些可能会出现的活跃性问题。死锁死锁描述了这样一种情景,两个或多个线程永久阻塞,互相等待对方释放资源如果线程1锁住了A,然后尝试对B进行加锁,同
京东云开发者 京东云开发者
11个月前
记一次线上问题引发的对 Mysql 锁机制分析 | 京东物流技术团队
背景最近双十一开门红期间组内出现了一次因Mysql死锁导致的线上问题,当时从监控可以看到数据库活跃连接数飙升,导致应用层数据库连接池被打满,后续所有请求都因获取不到连接而失败整体业务代码精简逻辑如下:@Transactionpublicvoidservic
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
MySQL事务死锁问题排查 | 京东云技术团队
一、背景在预发环境中,由消息驱动最终触发执行事务来写库存,但是导致MySQL发生死锁,写库存失败。com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:rpcerror:code
MySQL的index merge(索引合并)导致数据库死锁分析与解决方案 | 京东云技术团队
背景在DBS集群列表更多连接查询死锁中,看到9月22日有数据库死锁日志,后排查发现是因为mysql的优化indexmerge(索引合并)导致数据库死锁。定义indexmerge(索引合并):该数据库查询优化的一种技术,在mysql5.1之后进行引入,它可以
京东云开发者 京东云开发者
11个月前
线上SQL超时场景分析-MySQL超时之间隙锁 | 京东物流技术团队
前言之前遇到过一个由MySQL间隙锁引发线上sql执行超时的场景,记录一下。背景说明分布式事务消息表:业务上使用消息表的方式,依赖本地事务,实现了一套分布式事务方案消息表名:mqmessages数据量:3000多万索引:createtime和statuss