关于 mysql 死锁(dead lock)的问题

2019-03-19 21:11:04 +08:00
 Buffer2Disk

线上项目有 2 个定时任务在跑

任务 1:向 A 表中批量插入数据,采用的是 mybatis SqlSession ---> ExecutorType.BATCH 方式来批量提交数据

任务 2: 向 A 表中删除一条数据

在某个时间点,2 个任务同时执行了,然后产生了死锁的情况。

想问下各位大佬,这个死锁是怎么造成的?

我看了一些文档猜测是 gap 锁和插入意向锁,但是又有点理不清楚到底怎么死锁的

mysql 查看死锁的日志 show engine innodb status\G;

日志如下 :

任务 2 的锁

任务 1 的锁(使用了 spring 的事务回滚)

4540 次点击
所在节点    MySQL
17 条回复
xjy110207
2019-03-20 00:55:29 +08:00
一个可能的解释:
1、事务 2 先执行,在索引 218 页的位置先获得了一把排它锁
2、事务 1 开始执行,顺序扫描索引并用 gap 锁锁住了处于 200 页的某条记录
3、事务 2 执行到要在 200 页获得插入意图锁的阶段,开始等待事务 1
4、事务 1 顺序扫描到了 218 页,需要获得排它锁,开始等待事务 2
5、至此,就产生了死锁。
xjy110207
2019-03-20 01:07:51 +08:00
其产生的根本在于你 mysql 的隔离级别是可重复读,gap 锁仅在可重复读模式下出现。所以要解决这个死锁问题有两种方案:
1、修改你删除定时任务的连接的事务隔离级别为读已提交。
2、将插入定时任务修改为一个事务插入一行。
charles2java
2019-03-20 01:13:47 +08:00
有可能,批量更新时加锁顺序不一致而导致的
xjy110207
2019-03-20 01:14:14 +08:00
补充: 处于可重复读模式下也不一定会产生 gap 锁,前提是你的语句会使用唯一索引, 但是你这个两条语句就这个信息来看使用的 uid 索引并不是唯一索引。
Buffer2Disk
2019-03-20 12:48:10 +08:00
@xjy110207 确实是 RR 模式,uid 加了普通索引
任务 1 插入的时候数据太多,是放在一个事务里面批量提交的
Buffer2Disk
2019-03-20 12:49:16 +08:00
@xjy110207 我目前的解决方案是,给 2 个定时任务加了可重入锁,让它们不要同时执行


或者 把 RR 改成 RC 也可以,这个没试过
Buffer2Disk
2019-03-20 12:56:08 +08:00
@xjy110207 其实我没太明白,任务 2 的作用就是执行了一条 sql,删除了一条记录

为啥
"顺序扫描索引并用 gap 锁锁住了处于 200 页的某条记录"



"顺序扫描到了 218 页,需要获得排它锁,开始等待事务 2"

发生了这 2 件事情
Buffer2Disk
2019-03-20 12:58:05 +08:00
这个 gap 锁范围有这么大么?
xjy110207
2019-03-20 12:58:46 +08:00
@Buffer2Disk 你这个死锁的原因就是因为两个事务都是修改大量数据, 这些数据的上锁顺序不一致导致的,这是产生死锁的主要原因之一。 一个通用解决方案就是每个事务都尽可能的只修改少量的数据, 从你这个 log 里面来看,这两个事务改动的都比较多, 执行时间都在 10s 以上,出问题概率还是挺大的。
ps: 可能还有一种解决方案就是,因为索引本身是有序的,索引 update delete 会按照索引顺序加锁,你可以修改插入事务,对 uid 排序后再插入,那插入时候获得锁的顺序就跟 delete 时是一样的,理论上也不会出现死锁,顶多就是会有一个事务等待时间较长.
xjy110207
2019-03-20 13:00:21 +08:00
@Buffer2Disk delete 之类的 gap 锁会锁定记录之前的,你这个 1675 的 id 起始记录位置可能处于 200 到 218 页之间的某个页上,那么这个记录之前的都会被加上 gap 锁
Buffer2Disk
2019-03-20 13:03:53 +08:00
@xjy110207 "一个通用解决方案就是每个事务都尽可能的只修改少量的数据"

会不会造成插入的时间耗时增加呢?
Buffer2Disk
2019-03-20 13:07:31 +08:00
我目前 插入的定时任务一次性插入 200 多条数据,采用的是 mybatis SqlSession ---> ExecutorType.BATCH 方式来批量提交数据;

如果用 mybatis 的 foreach 来提交不知道会不会快一点,但是看这个老外评论好像效率会更低
https://stackoverflow.com/a/40608353/2379891
xjy110207
2019-03-20 13:10:32 +08:00
@Buffer2Disk 你这个是离线任务的话,其实没太大影响。关于事务,锁方面的知识点你可以参考 mysql-refman 里面 14.7 章节
xjy110207
2019-03-20 13:12:50 +08:00
@Buffer2Disk 如果你这个表 uid 不会重复的话,建唯一索引可以快速的解决问题,无需程序做任何改动
Buffer2Disk
2019-03-20 13:13:00 +08:00
@xjy110207 嗯嗯,非常感谢你的回复,受教了
amwyyyy
2019-03-20 13:59:49 +08:00
跟我理解的有点不一样,请问下删除语句那里,uid=1675 的数据当时是存在的吗?
Buffer2Disk
2019-03-20 14:25:54 +08:00
@amwyyyy 是存在的

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/546350

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX