MySQL线上死锁分析实战
前言
MySQL的锁机制相信大家在学习MySQL的时候都有简单的了解过，那既然有锁就必定绕不开死锁这个问题。其实MySQL在大部分场景下是不会存在死锁问题的（比如并发量不高，SQL写得不至于太拉胯的情况），但是在高并发的业务场景下，一不注意就会产生死锁，而这个死锁分析起来也比较麻烦。
前段时间在公司实习的时候就遇到了一个比较奇怪的死锁，之前一直没来得及好好整理，最近有空复现了一下，算是积累一点经验。
业务场景
简单说一下业务背景，公司做的是电商直播，我负责的是主播端相关的业务。而这个死锁就出现在主播后台对商品信息进行更新的时候。
我们的一个商品会有两个关联的ID，通过其中任何一个ID都无法确定唯一一件商品（也就是说这个ID和商品是一对多的关系），只能同时查询两个ID，才能确定一件商品。所以在更新商品信息的时候，需要在where条件中同时指定两个ID，下面是死锁SQL的结构（已脱敏）：
UPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=10ANDteacher_id=8;
这个SQL非常简单，根据两个等值条件，对一个字段进行更新。
不知道你看到这个SQL会不会懵逼，按常理来说，应该是一个事务里有多条SQL才会有可能出现死锁，这一条SQL怎么可能出现死锁呢？
是的，我当时也有这样的疑惑，甚至怀疑是不是报警系统瞎报（最后证明不是…），当时是真的摸不着头脑。并且因为数据库权限的原因，想看死锁日志都看不到，又是临近下班的时候，找DBA能麻烦死，所以就直接搜索引擎走起了……（关键词：update死锁单条sql），最后查出来是由于MySQL的索引合并优化导致的，即IndexMerge，下面会进行详细讲解并复现一下死锁场景。
索引合并
IndexMerge是MySQL在5.0的时候引入的一项优化功能，主要是用于优化一条SQL使用多个索引的情况。
我们来看刚刚的SQL，假设class_id和teacher_id分别是两个普通索引：
UPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=10ANDteacher_id=8;
如果没有IndexMerge优化的时候，MySQL查询数据的步骤如下：
- 根据class_id或teacher_id（具体使用哪个索引由优化器根据实际数据情况自行判断，这里假设使用class_id的索引）在二级索引上查询到对应数据的主键ID
- 根据查询到的主键ID进行回标查询（即查询聚簇索引），得到相应的数据行
- 从数据行中获取teacher_id，判断其是否等于8，满足条件则返回
从这个过程中，不难看出，MySQL只使用到了一个索引，至于为什么不使用多个索引，简单来说就是因为多个索引在多棵树上，强行使用反而降低性能。
再来看看引入了IndexMerge优化后，MySQL查询数据的步骤如下：
- 根据class_id查询到相应的主键，再根据主键回表查询到对应的数据行（记为结果集A）
- 根据teacher_id查询到相应的主键，再根据主键回表查询到对应的数据行（记为结果集B）
- 将结果集A和结果集B执行交集操作，获得最终满足条件的结果集
这里可以看出，有了IndexMerge之后，MySQL将一条SQL语句拆分成了两个查询步骤，分别使用两个索引，再用交集操作优化性能。
死锁分析
分析完了IndexMerge的步骤，我们再回过头想一下为什么会出现死锁呢？
还记得上面说的IndexMerge将一条SQL查询拆分成了两个步骤吗，问题就出现在这里。我们知道UPDATE语句是会加上一个行级排他锁的，在分析加锁步骤之前，我们假设有如下一个数据表：
上表数据满足我们文章开头说的特点，根据class_id和teacher_id单个字段均无法唯一确定一条数据，只能联合两个字段，才能确定一条数据，并且设定class_id和teacher_id分别为两个普通索引。
假设有如下两条SQL语句并发执行，它们的参数完全不同，直觉告诉我们应该不会出现死锁，但直觉往往是错误的：
//线程A执行 UPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=2ANDteacher_id=1; //线程B执行 UPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=1ANDteacher_id=2;
那么在IndexMerge的优化下，并发执行如上SQL的时候，MySQL的加锁步骤如下：
最终，两个事务互相等待，形成死锁
解决方案
因为这个死锁本质上还是由于IndexMerge这个优化导致的，所以要解决这个场景的死锁问题，本质上只要让MySQL不走IndexMerge优化即可。
方案一
手动将一条SQL拆分成多条SQL，在逻辑层做交集操作，阻止MySQL的憨憨优化行为，比如这里我们可以先根据class_id查询到相应主键，再根据teacher_id查询相应主键，最后根据交集后的主键查询数据。
方案二
建立联合索引，比如这里可以将class_id和teacher_id建立一个联合索引，MySQL就不会走IndexMerge了
方案三
强制走单个索引，在表名后添加forindex(class_id)可以指定该语句仅走class_id索引
方案四
关闭IndexMerge优化：
- 永久关闭：SET[GLOBAL|SESSION]optimizer_switch='index_merge=off';
- 临时关闭：UPDATE/*+NO_INDEX_MERGE(test_table)*/test_tableSETname="zhangsan"WHEREclass_id=10ANDteacher_id=8;
场景复现
数据准备
为了方便测试，这里提供一个SQL脚本，将其用Navicat导入后即可得到需要的测试数据：
下载地址：https://cdn.juzibiji.top/file/index_merge_student.sql
导入之后，我们会得到如下格式的10000条测试数据：
测试代码
由于篇幅限制，这里仅给出代码Gist链接：https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60
上述代码主要是开启100个线程执行我们的数据修改SQL语句，来模拟线上并发情况，在运行几秒钟后，我们会得到下面这样一个报错：
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException:Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
这代表已经产生了死锁异常
死锁分析
上面我们用代码已经构造出了一个死锁，接下来我们进入MySQL看看死锁日志，在MySQL中执行如下命令即可查看死锁日志：
SHOWENGINEINNODBSTATUS;
在日志中，我们找到LATESTDETECTEDDEADLOCK这一行，这里开始便是我们上次产生的死锁，接下来我们开始分析。
通过第29行可以看到，事务1执行的SQL的条件是class_id=6和teacher_id=16，它目前持有了一个行锁，第34~39行是该行数据，34行是主键的十六进制表示，我们转换为10进制即为1616。同样的，看45行，其等待拿锁的是主键id1517的数据。
接下来用同样的方法分析事务2，可知事务2持有了3把锁，分别是主键id为1317、1417、1517的数据行，等待的是1616。
看到这里我们就已经发现了，事务1持有1616等待1517，事务2持有1517等待1616，所以形成了一个死锁。此时MySQL的处理方法是回滚持有锁最少的事务，并且JDBC会抛出我们前面的MySQLTransactionRollbackException回滚异常。
总结
这个死锁在排查的时候其实非常不好排查，如果你不知道MySQL的IndexMerge，那么在排查的时候其实是毫无头绪的，因为呈现在你面前的就只有一条非常简单的SQL，就算看死锁日志，也是一样的不明所以。
所以处理这类问题，更多的还是考验你的知识储备量和经验，只要遇到过一次，后面在写SQL的时候多加注意就好了！
到此这篇关于MySQL线上死锁分析实战的文章就介绍到这了,更多相关MySQL线上死锁分析内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。