前几天排查了一个死锁问题，最开始百思不得其解，因为发生死锁的两个事务是单语句事务，语句类型相同(where属性列相同，仅值不同)，而且语句都走了相同的索引，但最终确实发生了死锁。通过定位排查发现，问题的源头就是index_merge，死锁的原因也很普通，两个事务加锁顺序不同，并存在相互等待的情况。因为这个案例比较特殊，所以在此分享给大家。
拿到死锁问题，首先需要查看几个基本信息，包括死锁等待关系，表结构定义等。
通过show engine innodb status;命令可以获取innodb引擎中最近一次发生死锁的信息，信息如下
从死锁结果来看，我们很容易看到事务1持有 partner_id二级索引上的锁，等待PK索引上的锁；而事务2持有PK索引锁，等待partner_id二级索引上的锁，两个事务相互持有对方需要的锁资源，而无法往前推进，造成死锁。单从死锁信息来看，我们可能会比较疑惑，每个事务只有一个语句，为什么同样的语句，对二级索引和主键的加锁顺序会不同？
产生死锁的原因
首先我们来看看语句的执行计划，
语句的type是index_merge，Extra的信息是Using intersect(customerid,partnerid)，从而我们得知语句执行计划走了index_merge优化，单个语句通过两个索引(customerid,partnerid)来提取记录集合并取交集获得最终结果集。index_merge具体算法不在此展开，基本使用场景是语句包含多个查询条件，每个条件都单独存在索引，而单个条件的索引过滤度不高，组合起来过滤度比较高，这个时候就可能会走index_merge优化，使得单个SQL语句可以同时利用两个索引过滤。会不会与index_merge有关呢？
在index_merge的情况下，会导致二级索引与主键索引顺序不一致的情况吗？结合上面的死锁信息，我们得知死锁两个的二级索引key是0x698，而主键索引key是0x21747。我们看看到底是哪条记录的主键和二级索引发生了死锁，
可以看到0x21747对应的customer_id为151069，partner_id为1688，是不是感觉似曾相识，对的，第二个事务的语句查询条件就是这两个条件的组合。这说明，对于这条记录，第一个事务语句只有partnerid索引(1688)满足条件；对于第二个事务，customer_id和partner_id索引都满足条件。由于每个语句执行时都需要利用两个二级索引，假设先使用customer_id索引扫描，然后使用partner_id索引扫描，那么对于id为0x21747的记录，事务1的partner_id=1688满足条件，加partner_id锁，然后对对应的PK索引加锁；对于事务2，对customer_id= 151069加锁，对对应的PK索引加锁，然后对partner_id=1688索引加锁。那么对partner_id二级索引和PK主键索引在两个事务的上锁顺序是相反的，所以导致了死锁。对于id为0x21747记录：
表格第2步和第3步，两个事务的加锁顺序是相反的，导致了死锁发生。
前面啰啰嗦嗦讲了一个死锁案例的来龙去脉，但仅仅是产生死锁的一种情况。生产环境中，我们当然不需要死锁频繁发生，毕竟是需要部分事务回滚才能解锁的，下面介绍几个简单的原则，有助于减少死锁的发生。
1) 尽量按顺序加锁，从源头避免死锁
2）选择合适的隔离级别，隔离级别越高，并发冲突越激烈，实际场景Read-Committed就够用了
3）避免使用大事务，根据二段锁原则，只有事务结束(提交或回滚)才会释放锁，持有的锁越多，可能导致的冲突越大
4）为表添加合适的索引，避免走不到索引导致对每条记录都加锁
Original: https://www.cnblogs.com/cchust/p/6464188.html
Author: 天士梦
Title: index_merge引发的死锁排查
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/591740/
转载文章受原作者版权保护。转载请注明原作者出处！