一次 MySQL 线上死锁分析实战

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《一次 MySQL 线上死锁分析实战》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

关键词：MySQL Index Merge

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

业务场景

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

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

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，假设

UPDATE test_table SET `name`="zhangsan" WHERE class_id = 10 AND teacher_id = 8;

如果没有 Index Merge 优化的时候，MySQL 查询数据的步骤如下：

• 根据 class_id 或 teacher_id （具体使用哪个索引由优化器根据实际数据情况自行判断，这里假设使用

  // 线程 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 的加锁步骤如下：

  

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

  解决方案

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

  方案一

  手动将一条 SQL 拆分成多条 SQL，在逻辑层做交集操作，阻止 MySQL 的憨憨优化行为，比如这里我们可以先根据

  SHOW ENGINE INNODB STATUS;

  

  在日志中，我们找到

  LATEST DETECTED DEADLOCK

  这一行，这里开始便是我们上次产生的死锁，接下来我们开始分析。

  通过第 29 行可以看到，事务 1 执行的 SQL 的条件是 class_id = 6 和

  teacher_id = 16 

  ，它目前持有了一个行锁，第 34~39 行是该行数据，34 行是主键的十六进制表示，我们转换为 10 进制即为 1616。同样的，看 45 行，其等待拿锁的是主键 id 1517 的数据。
  

  接下来用同样的方法分析事务 2，可知事务 2 持有了 3 把锁，分别是主键 id 为1317、1417、1517 的数据行，等待的是 1616 。

  看到这里我们就已经发现了，事务 1 持有 1616 等待 1517，事务 2 持有1517 等待 1616，所以形成了一个死锁。此时 MySQL 的处理方法是回滚持有锁最少的事务，并且 JDBC 会抛出我们前面的 MySQLTransactionRollbackException 回滚异常。

  总结

  这个死锁在排查的时候其实非常不好排查，如果你不知道 MySQL 的 Index Merge，那么在排查的时候其实是毫无头绪的，因为呈现在你面前的就只有一条非常简单的 SQL，就算看死锁日志，也是一样的不明所以。

  所以处理这类问题，更多的还是考验你的知识储备量和经验，只要遇到过一次，后面在写 SQL 的时候多加注意就好了！

  我是小桔，欢迎关注我的微信公众号，带你了解更多前后端知识。
  

  

  到这里，我们也就讲完了《一次 MySQL 线上死锁分析实战》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！