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

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 2668 days ago, the information mentioned may be changed or developed.

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

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

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

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

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

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

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

日志如下：

任务 2 的锁

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

死锁

img

src

任务

17 replies • 2019-03-20 14:25:54 +08:00

xjy110207

Mar 20, 2019 via Android

一个可能的解释:
1、事务 2 先执行，在索引 218 页的位置先获得了一把排它锁
2、事务 1 开始执行，顺序扫描索引并用 gap 锁锁住了处于 200 页的某条记录
3、事务 2 执行到要在 200 页获得插入意图锁的阶段，开始等待事务 1
4、事务 1 顺序扫描到了 218 页，需要获得排它锁，开始等待事务 2
5、至此，就产生了死锁。

xjy110207

Mar 20, 2019

其产生的根本在于你 mysql 的隔离级别是可重复读，gap 锁仅在可重复读模式下出现。所以要解决这个死锁问题有两种方案:
1、修改你删除定时任务的连接的事务隔离级别为读已提交。
2、将插入定时任务修改为一个事务插入一行。

charles2java

Mar 20, 2019 via Android

有可能，批量更新时加锁顺序不一致而导致的

xjy110207

Mar 20, 2019

补充: 处于可重复读模式下也不一定会产生 gap 锁，前提是你的语句会使用唯一索引，但是你这个两条语句就这个信息来看使用的 uid 索引并不是唯一索引。

Buffer2Disk

Mar 20, 2019

@xjy110207 确实是 RR 模式，uid 加了普通索引
任务 1 插入的时候数据太多，是放在一个事务里面批量提交的

Buffer2Disk

Mar 20, 2019

@xjy110207 我目前的解决方案是，给 2 个定时任务加了可重入锁，让它们不要同时执行

或者把 RR 改成 RC 也可以，这个没试过

Buffer2Disk

Mar 20, 2019

@xjy110207 其实我没太明白，任务 2 的作用就是执行了一条 sql，删除了一条记录

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

又

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

发生了这 2 件事情

Buffer2Disk

Mar 20, 2019

这个 gap 锁范围有这么大么？

xjy110207

Mar 20, 2019

@Buffer2Disk 你这个死锁的原因就是因为两个事务都是修改大量数据，这些数据的上锁顺序不一致导致的，这是产生死锁的主要原因之一。一个通用解决方案就是每个事务都尽可能的只修改少量的数据, 从你这个 log 里面来看，这两个事务改动的都比较多，执行时间都在 10s 以上，出问题概率还是挺大的。
ps: 可能还有一种解决方案就是，因为索引本身是有序的，索引 update delete 会按照索引顺序加锁，你可以修改插入事务，对 uid 排序后再插入，那插入时候获得锁的顺序就跟 delete 时是一样的，理论上也不会出现死锁，顶多就是会有一个事务等待时间较长.

xjy110207

Mar 20, 2019

@Buffer2Disk delete 之类的 gap 锁会锁定记录之前的，你这个 1675 的 id 起始记录位置可能处于 200 到 218 页之间的某个页上，那么这个记录之前的都会被加上 gap 锁

Buffer2Disk

Mar 20, 2019

@xjy110207 "一个通用解决方案就是每个事务都尽可能的只修改少量的数据"

会不会造成插入的时间耗时增加呢？

Buffer2Disk

Mar 20, 2019

我目前插入的定时任务一次性插入 200 多条数据，采用的是 mybatis SqlSession ---> ExecutorType.BATCH 方式来批量提交数据；

如果用 mybatis 的 foreach 来提交不知道会不会快一点，但是看这个老外评论好像效率会更低
https://stackoverflow.com/a/40608353/2379891

xjy110207

Mar 20, 2019 via Android

@Buffer2Disk 你这个是离线任务的话，其实没太大影响。关于事务，锁方面的知识点你可以参考 mysql-refman 里面 14.7 章节

xjy110207

Mar 20, 2019 via Android

@Buffer2Disk 如果你这个表 uid 不会重复的话，建唯一索引可以快速的解决问题，无需程序做任何改动

Buffer2Disk

Mar 20, 2019

@xjy110207 嗯嗯，非常感谢你的回复，受教了

amwyyyy

Mar 20, 2019

跟我理解的有点不一样，请问下删除语句那里，uid=1675 的数据当时是存在的吗？

Buffer2Disk

Mar 20, 2019

@amwyyyy 是存在的