MySQL线上死锁分析实战

前言

mysql 的锁机制相信大家在学习 mysql 的时候都有简单的了解过，那既然有锁就必定绕不开死锁这个问题。其实 mysql 在大部分场景下是不会存在死锁问题的（比如并发量不高，sql 写得不至于太拉胯的情况），但是在高并发的业务场景下，一不注意就会产生死锁，而这个死锁分析起来也比较麻烦。

前段时间在公司实习的时候就遇到了一个比较奇怪的死锁，之前一直没来得及好好整理，最近有空复现了一下，算是积累一点经验。

业务场景

简单说一下业务背景，公司做的是电商直播，我负责的是主播端相关的业务。而这个死锁就出现在主播后台对商品信息进行更新的时候。

我们的一个商品会有两个关联的 id，通过其中任何一个 id 都无法确定唯一一件商品（也就是说这个 id 和商品是一对多的关系），只能同时查询两个 id，才能确定一件商品。所以在更新商品信息的时候，需要在 where 条件中同时指定两个 id，下面是死锁 sql 的结构（已脱敏）：

1	`update` `test_table` `set` ``name``=`"zhangsan"` `where` `class_id = 10` `and` `teacher_id = 8;`

这个 sql 非常简单，根据两个等值条件，对一个字段进行更新。

不知道你看到这个 sql 会不会懵逼，按常理来说，应该是一个事务里有多条 sql 才会有可能出现死锁，这一条 sql 怎么可能出现死锁呢？

是的，我当时也有这样的疑惑，甚至怀疑是不是报警系统瞎报（最后证明不是…），当时是真的摸不着头脑。并且因为数据库权限的原因，想看死锁日志都看不到，又是临近下班的时候，找 dba 能麻烦死，所以就直接搜索引擎走起了……（关键词：update 死锁单条 sql），最后查出来是由于 mysql 的索引合并优化导致的，即 index merge，下面会进行详细讲解并复现一下死锁场景。

索引合并

index merge 是 mysql 在 5.0 的时候引入的一项优化功能，主要是用于优化一条 sql 使用多个索引的情况。

我们来看刚刚的 sql，假设 class_id 和 teacher_id 分别是两个普通索引：

1	`update` `test_table` `set` ``name``=`"zhangsan"` `where` `class_id = 10` `and` `teacher_id = 8;`

如果没有 index merge 优化的时候，mysql 查询数据的步骤如下：

根据 class_id 或 teacher_id （具体使用哪个索引由优化器根据实际数据情况自行判断，这里假设使用 class_id的索引）在二级索引上查询到对应数据的主键 id
根据查询到的主键 id 进行回标查询（即查询聚簇索引），得到相应的数据行
从数据行中获取 teacher_id ，判断其是否等于 8，满足条件则返回

从这个过程中，不难看出，mysql 只使用到了一个索引，至于为什么不使用多个索引，简单来说就是因为多个索引在多棵树上，强行使用反而降低性能。

再来看看引入了 index merge 优化后，mysql 查询数据的步骤如下：

根据 class_id 查询到相应的主键，再根据主键回表查询到对应的数据行（记为结果集 a）
根据 teacher_id 查询到相应的主键，再根据主键回表查询到对应的数据行（记为结果集 b）
将结果集 a 和结果集 b 执行交集操作，获得最终满足条件的结果集

这里可以看出，有了 index merge 之后，mysql 将一条 sql 语句拆分成了两个查询步骤，分别使用两个索引，再用交集操作优化性能。

死锁分析

分析完了 index merge 的步骤，我们再回过头想一下为什么会出现死锁呢？

还记得上面说的 index merge 将一条 sql 查询拆分成了两个步骤吗，问题就出现在这里。我们知道 update 语句是会加上一个行级排他锁的，在分析加锁步骤之前，我们假设有如下一个数据表：

MySQL线上死锁分析实战

上表数据满足我们文章开头说的特点，根据 class_id 和 teacher_id 单个字段均无法唯一确定一条数据，只能联合两个字段，才能确定一条数据，并且设定 class_id 和 teacher_id 分别为两个普通索引。

假设有如下两条 sql 语句并发执行，它们的参数完全不同，直觉告诉我们应该不会出现死锁，但直觉往往是错误的：

									// 线程 a 执行

									update test_table set `name`="zhangsan" where class_id = 2 and teacher_id = 1;

									// 线程 b 执行

									update test_table set `name`="zhangsan" where class_id = 1 and teacher_id = 2;

那么在 index merge 的优化下，并发执行如上 sql 的时候，mysql 的加锁步骤如下：

MySQL线上死锁分析实战

最终，两个事务互相等待，形成死锁

解决方案

因为这个死锁本质上还是由于 index merge 这个优化导致的，所以要解决这个场景的死锁问题，本质上只要让 mysql 不走 index merge 优化即可。

方案一

手动将一条 sql 拆分成多条 sql，在逻辑层做交集操作，阻止 mysql 的憨憨优化行为，比如这里我们可以先根据 class_id 查询到相应主键，再根据 teacher_id 查询相应主键，最后根据交集后的主键查询数据。

方案二

建立联合索引，比如这里可以将 class_id 和 teacher_id 建立一个联合索引，mysql 就不会走 index merge 了

方案三

强制走单个索引，在表名后添加 for index(class_id) 可以指定该语句仅走 class_id 索引

方案四

关闭 index merge 优化：

永久关闭：set [global|session] optimizer_switch='index_merge=off';
临时关闭：update /*+ no_index_merge(test_table) */ test_table set name="zhangsan" where class_id = 10 and teacher_id = 8;

场景复现

数据准备

为了方便测试，这里提供一个 sql 脚本，将其用 navicat 导入后即可得到需要的测试数据：

下载地址：https://cdn.juzibiji.top/file/index_merge_student.sql

导入之后，我们会得到如下格式的 10000 条测试数据：

MySQL线上死锁分析实战

测试代码

由于篇幅限制，这里仅给出代码 gist 链接：https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60

MySQL线上死锁分析实战

上述代码主要是开启 100 个线程执行我们的数据修改 sql 语句，来模拟线上并发情况，在运行几秒钟后，我们会得到下面这样一个报错：

com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction

这代表已经产生了死锁异常

死锁分析

上面我们用代码已经构造出了一个死锁，接下来我们进入 mysql 看看死锁日志，在 mysql 中执行如下命令即可查看死锁日志：

1	`show engine innodb status;`

MySQL线上死锁分析实战

在日志中，我们找到 latest detected deadlock 这一行，这里开始便是我们上次产生的死锁，接下来我们开始分析。

通过第 29 行可以看到，事务 1 执行的 sql 的条件是 class_id = 6 和 teacher_id = 16 ，它目前持有了一个行锁，第 34~39 行是该行数据，34 行是主键的十六进制表示，我们转换为 10 进制即为 1616。同样的，看 45 行，其等待拿锁的是主键 id 1517 的数据。

MySQL线上死锁分析实战