一次MySQL死锁的排查记录

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了一次MySQL死锁的排查记录，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3945字，纯文字阅读大概需要6分钟。

内容图文

前几天线上收到一条告警邮件，生产环境MySQL操作发生了死锁，邮件告警的提炼出来的SQL大致如下。

update pe_order_product_info_test
        set  end_time = '2021-04-30 23:59:59'
        where order_no = '111111111'
        and product_id = 123456
        and status in (1,2);
update pe_order_product_info_test
        set  end_time = '2021-04-30 23:59:59'
        where order_no = '222222222'
        and product_id = 123456
        and status in (1,2);      

是一条Update语句，定位了它的调用情况，发现Update的调用方只有一处，并且在Cat中看到一个小时的调用次数只有700多次，这个调用量基本与并发Update引起死锁无关了。

当时猜测了几种情况，这里Update进行操作时有其他业务方调用Select相关的接口，但是排查了那个时间点发生死锁应用的调用链，发现好像并没有其他会影响到Update的调用。

为了更进一步了解当时的情况，就联系了DBA老师，要了当时死锁发生时的日志，准备拿到日志之后大干一场，好好分析一下问题，结果...

DBA老师看了死锁日志直接点出了问题要害——index_merge索引合并。

1. 什么是索引合并

这是MySQL在5.1引入的优化技术，再此之前，一个表仅仅只能使用一个索引，但索引合并的引入，可以对同一张表使用多个索引分别进行条件扫描。

如果要拿索引合并index_merge与只使用一个索引做比较，那么拿上面那个update语句来做演示。

update pe_order_product_info_test
        set end_time = '2021-04-30 23:59:59'
        where order_no = '111111111'
        and product_id = 123456
        and status in (1,2);

只是用一个索引时，MySQL会选择一个最优的索引来使用，比如使用index_order_no，拿它来找出所有order_no为111111111的索引记录，从该索引上找到它的PRIMARY索引的id，然后回表找到对应的行数据，最后在内存中根据剩下的product_id和status条件来进行过滤。

但如果MySQL优化器觉得你如果只是用一个索引，拿出大量记录，然后再在内存中使用product_id和status过滤（并且符合该条件的记录值很少），这个第二步效率可能不高时，他就会使用索引合并进行优化。

如果使用索引合并去判断where条件时，那么它就会先通过index_order_no索引去找到PRIMARY索引的id，再通过index_product_id索引去找到PRIMARY索引的id，最后将两个id集合求交集，再回表找到行数据。(索引合并使用索引的顺序是不确定的)

2. 场景复现

在MySQL的Bug反馈文档中也有记录一个Bug #77209的记录，标注了索引合并引发死锁的情况。但是我按照它给出的repeat并不能重现索引合并的场景，在它的实例中早了600万随机数，我猜测可能是MySQL调高了索引合并的条件，将数据量增加到了1000万。

先来带大家复现一下当时的情况。

环境：MySQL 5.6.24

1. 创建一张测试表

   CREATE TABLE `a` (
     `ID` int  AUTO_INCREMENT PRIMARY KEY,
     `NAME` varchar(21),
     `STATUS` int,
     KEY `NAME` (`NAME`),
     KEY `STATUS` (`STATUS`)
   ) engine = innodb;
   

2. 导入数据，为了方便导入一些随机数据，需要先开启一个兼容性配置。

   set global show_compatibility_56=on;  
   

   开始导入随机数据。

   set @N=0;
   insert into a(ID,NAME,STATUS)
   select
   	@N:=@N+1,
   	@N%1600000, 
   	floor(rand()*4)
    from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c 
   LIMIT 10000000;
   

3. 测试

   update a set status=5 where rand() < 0.005 limit 1;
   explain UPDATE a SET STATUS = 2 WHERE NAME =  '1000000' AND STATUS = 5;
   

3. 为什么发生了死锁

直接上一副图，以及两个update事务的加锁流程。

可以看到在订单与产品这个模型中，Update事务一和Update事物二在product_id索引和primary索引上都存在交叉重合，这就导致了死锁的发生。

这就是本次死锁发生的原因所在了，解决方案有很多种，可以根据具体场景选择。

1. 删除某一个索引，这当然不是一个好办法
2. 关闭index_merge优化
3. 为查询条件增加联合索引，在本例中是product_id和order_no。

4. 最后

当然最后这些都是我个人的分析，DBA老师给的建议是直接上联合索引，网上关于索引合并的资料实在太少了，除了官方文档简单扯了扯，剩下的都是转载来转载去的博客，内容都一模一样，DBA老师也不写博客，所以我就只能按我上述这个思路理解了，如果网友有什么问题欢迎指出~

内容总结

以上是互联网集市为您收集整理的一次MySQL死锁的排查记录全部内容，希望文章能够帮你解决一次MySQL死锁的排查记录所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端