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 会导致行锁升级为表锁，反而不会导致问题出现，但是并发太差

结论

先说结论：

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

解决办法：

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

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

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

作者：京东零售 吴法刚

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