MySQL innoDB 间隙锁产生的死锁问题

（给ImportNew加星标，提高Java技能）

背景

线上经常偶发死锁问题，当时处理一张表，也没有联表处理，但是有两个 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 有三种行锁的算法：

Record Lock：是加在索引记录上的。
Gap Lock（间隙锁）：对索引记录间的范围加锁，或者加在最后一个索引记录的前面或者后面。
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) 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

解决办法：

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

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

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

转自：吴法刚 / 京东云开发者，

链接：blog.csdn.net/jdcdev_/article/details/133636102

- EOF -

推荐阅读点击标题可跳转

1、死锁问题排查过程-间隙锁的复现以及解决

2、跑了 4 个实验，实战讲解 MySQL 的行锁、间隙锁

3、MySQL 最佳实践：一次 InnoDB 死锁 Bug 排查

看完本文有收获？请转发分享给更多人

关注「ImportNew」，提升Java技能

点赞和在看就是最大的支持❤️

MySQL innoDB 间隙锁产生的死锁问题

session2

相关推荐