Mysql 死锁问题，原因探讨

# InnoDB , RR 事务隔离级别
# t2 表的 pulbish_id 为非唯一索引
# S1 语句：

INSERT INTO `t1` (contract_no...)
SELECT 'A10-SJZ-21017' ...
FROM DUAL
WHERE NOT EXISTS(SELECT *
                 FROM `t2` tmrd
                 where publish_id = '1123424214');  #这里的 publish_id 一定不存在，所以会产生全表锁


# S2 语句：
INSERT INTO t2

(publish_id...)
values ('1123424214'...)

目前的问题是：

事务一运行 S1 未提交，事务二运行 S2 会阻塞
事务一运行 S1 未提交，事务二运行对 t2 表的 for update /lock in share model不会阻塞
事务一运行 S1 未提交，事务二运行 S1 ，S2 （阻塞），事务一运行 S2 ，产生死锁异常 [40001][1213] Deadlock found when trying to get lock; try restarting transaction

事务

publish_id

运行

死锁

19 replies • 2022-01-05 09:58:00 +08:00

yibo2018

Dec 31, 2021

问题一：
事务一种 S1 会在 t2 表产生共享锁，事务二 insert t2 首先会插入意向写锁，所以阻塞

问题二：
事务一运行 S1 同上，所以事务二运行 t2 的 for update / lock in share model 应该会阻塞！但是并没有阻塞，和问题一的答案相悖

问题三：
如果问题一回答的是正确的，那么是可以解释的

yibo2018

Dec 31, 2021

翻阅了大量的资料，让我头越来越大，确丝毫用不到自己的问题中，学以致用对我来说真难，我好菜

orzwalker111

Dec 31, 2021

个人理解：
1.s1 事务未提交，因为间隙锁，事务 2 执行 s2 阻塞，正确
2.s1 select 添加悲观锁（ x/s ），命中覆盖索引 publish_id ，加锁范围(xx, 1123424214]，下一个锁范围是(1123424214, xxx]，继续向右遍历加 next-key lock ，因为最右值 xxx 不等于 1123424214 ，这个锁回退化成间隙锁(1123424214,xxx)；加锁结束后，事务 2 执行 s2 ，inset 时，publish_id= 1123424214 不在锁范围，所以写入成功，正确

orzwalker111

Dec 31, 2021

--(xx, 1123424214]这个回退化成间隙锁(xx, 1123424214)

Feiex

Dec 31, 2021

INSERT INTO T SELECT……FROM S WHERE……
对于被插入到表 T 中的元组，在其对应的索引项上施加排他记录锁
《数据库事务处理的艺术》

yibo2018

Dec 31, 2021

@orzwalker111 哇塞分析的很详细

问题 2 中
事务一运行 S1 ，如果 publish_id 没有命中的话，会产生全表的间隙锁吧？（自问自答）也不其然，有一个 semi-consistent read 机制，对于不满足查询条件的记录，MySQL 会提前释放，同时不加 GAP 锁，就和你说的退化一样。
但是对于事务 2 的 select ... where publish_id = 1123424214 for update 也是针对全表的排它锁，如果他也退化，至少也要对 1123424214 左右进行 GAP ，但是目前看下来也没有（没阻塞）

yibo2018

Dec 31, 2021

@Feiex 这句话我理解的就是，会在 t 表上加 X 锁。然后呢？

ozipin

Dec 31, 2021

产生锁是和索引有关的，有索引应该是间隙锁。然后 s1 加的应该是读锁吧，所以 s2 for update 不会阻塞，读写之间才会阻塞

yibo2018

Dec 31, 2021

@ozipin 不对哦，我可以试试
我简单的测试了下
事务一
SELECT *
FROM `t2` tmrd
where publish_id = '1123424214' lock in share mode

事务二
SELECT *
FROM `t2` tmrd
where publish_id = '1123424214' for update

事务二阻塞了

换句话说 for update 加的是 X 锁（排它锁）也就是写锁

Feiex

Dec 31, 2021

@yibo2018 #5 ，在 S 表的索引项加的 s 锁；
你的过程里事务二的 s2 要申请 t2 表的 ix 锁，就需要事务一的 s1 先释放 t2 表的 s 锁；然后事务一又想申请 t2 表的 ix 锁，就成环了

yibo2018

Jan 4, 2022

@Feiex 嗯，问题三的死锁问题是可以解释的

但是问题 2 就让我很困惑，对 t2 的共享锁（ S1 语句运行）和排它锁（对 t2 进行 for update ）应该是互斥。但其实没有互斥锁。
写着写着突然发现答案了：
那么原因只有一个就是：俩个上锁的地方没有重合！
即便是全表加锁，也会因为 MySQL 自己的优化机制退化。
为了证实上述的点，我选择了一个已经有的条件，会上间隙锁+定向锁，结果是阻塞了

至此我提出来的 3 个问题就解决了，感谢大家

YIERIC

Jan 4, 2022

@yibo2018 请教一下，“事务一运行 S1 未提交，事务二运行 S2 会阻塞”，为什么“事务一种 S1 会在 t2 表产生共享锁”？

YIERIC

Jan 4, 2022

@YIERIC 因为既没有 for update / lock in share model 也没有数据更新，所以我不明白为什么会在 t2 上有共享锁

yibo2018

Jan 4, 2022

@YIERIC 我的理解是 s1 (insert ... exists (select ...)) 这个形式会对 select 的内容上共享锁

yibo2018

Jan 4, 2022

@YIERIC 对的，我也很纳闷，这个结论可以说是由问题一反证回来的

YIERIC

Jan 4, 2022

@yibo2018 我的理解是这样的：
1 、事务一持有 t2 间隙锁，事务二插入意向锁被间隙锁阻塞
2 、事务一持有 t2 间隙锁，事务二同样间隙锁，不阻塞
3 、事务一持有 t2 间隙锁，事务二运行 S1 不阻塞（同 2 ），运行 S2 阻塞（同 1 ，插入意向锁被间隙锁阻塞），事务一运行 S2 阻塞（插入意向锁被间隙锁阻塞），彼此的插入意向锁在等对方的间隙锁，所以死锁了

YIERIC

Jan 4, 2022

@yibo2018 总结一下，我的观点有两个关键点：1 、S1 间隙锁； 2 、间隙锁不互斥

YIERIC

Jan 4, 2022

再补充一句，是间隙锁不互斥，而不是 next-lock

yibo2018

Jan 5, 2022

@YIERIC 哇塞，感谢大佬，很清晰