岁末年初，似乎是跟死锁杠上了。
从一次线上Mysql死锁引发的血案开始，到使用BeanUtils进行属性复制的坑，以为这个问题终于可以告一段落。没想到，眼看着就要过年了，还不太平，这两天又连续收到好几次死锁的告警，仔细一看，还跟之前的情况不太一样。
死锁日志
有了之前分析死锁问题的经验，二话不说，首先联系DBA拿到了死锁日志如下(具体sql作了简化和脱敏处理)：
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 157863146893, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 13773245, OS thread handle 140251312789248, query id 55260458631 10.255.201.50 beauty updating
UPDATE `t1` SET `pay_status`=20 where `kdt_id`=111 and `order_no`='M1' and `pay_no`='P1' and `pay_status` in (10, 20, 98) limit 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3270 page no 32279 n bits 88 index idx_order_kdt of table `beauty`.`order_pay` trx id 157863146893 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 157863146894, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 13774200, OS thread handle 140251311183616, query id 55260458632 10.255.201.49 beauty Sending data
SELECT * from `t1` where `kdt_id`=111 and `order_no`='M1' and `pay_no`='P1' and `pay_status` in (30, 50) for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3270 page no 32279 n bits 88 index idx_order_kdt of table `beauty`.`order_pay` trx id 157863146894 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3270 page no 32954 n bits 112 index PRIMARY of table `beauty`.`order_pay` trx id 157863146894 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
从死锁日志可以非常清楚地看到：
事务1持有主键锁，等待二级索引idx_order_kdt上的锁，而事务2持有二级索引idx_order_kdt上的锁，等待主键锁，从而造成死锁。
结合业务场景，两个事务的执行顺序如下：
image.png
其中表t1的索引结构为：
PRIMARY KEY (`id`),
KEY `idx_order_kdt` (`order_no`,`kdt_id`),
KEY `idx_payno_kdt` (`pay_no`,`kdt_id`),
可以看到，表t1在order_no、pay_no这两个字段上都存在一个复合索引。
结合两个事务的where条件和t1索引，事务2持有idx_order_kdt锁等待主键锁比较好理解，那么现在的问题是：事务1是如何拿到主键锁的？
执行计划
为了回答这个问题，我们可以先看下事务1的执行计划，如下：
image.png
而这里似乎出现了一些新鲜的面孔😝
从type字段我们看到，事务1走了index_merge优化，而从key和Extra我们进一步发现，是idx_payno_kdt和idx_order_kdt两个二级索引发生了index_merge的intersect类型的优化。
什么是index_mrege
那么什么是index_merge呢？
简单来说，就是MySQL在分析执行计划时发现走单个索引的过滤效果都不是很好，于是对多个索引分别进行条件扫描，然后将多个索引单独扫描的结果进行合并的一种优化操作。合并的方式分为三种：intersection、union和sort_union，在我们的例子中就是对两个二级索引的过滤结果取交集。有关index_merge的介绍详见index_merge简介
到这里，原因似乎就很清晰了。
事务1因为发生了index_merge，先后走了idx_payno_kdt、idx_order_kdt两个索引，因此加锁顺序是idx_payno_kdt -> PK -> idx_order_kdt。
事务2只会走idx_order_kdt这一个索引，加锁顺序是idx_order_kdt -> PK。
看到没有，两个事务可能会发生循环等待，构成了发生死锁的条件。
因为发生index_merge可能引发的死锁，实际上是MySQL的一个bug: https://bugs.mysql.com/bug.php?id=77209
消除死锁
既然找到了原因，那这个问题解决起来也就简单了。既然死锁的起因是因为发生了index_merge，那么只要杜绝事务可以走两个索引的可能性不就可以了吗？因此大体来说，有以下五种可选方案：
- 一般来说，发生了index_merge，说明索引设计得不够合理。具体到我们这个例子，就可以删除idx_payno_kdt、idx_order_kdt这两个索引，同时增加一个kdt_id, pay_no, order_no三个字段构成的复合索引，这样一来事务1就只会走这一个索引；
- 看idx_payno_kdt、idx_order_kdt是否都是必须的，视情况删掉其中一个索引；
- 修改sql，在我们的业务场景中，有kdt_id和pay_no其实就可以定位出一条数据，不需要order_no，在事务1的update语句中去掉where条件的order_no即可；
- 修改事务1为根据主键进行更新；
- 关闭index_merge优化；
结合我们具体的业务场景，我们最终选择了方案4，问题得以解决。
什么场景下会走index_merge优化？
问题虽然是解决了，但有个问题还是困扰了我很久。那就是这次发生死锁的两个事务相关的代码逻辑，已经有一个多月没有发生任何变动，而且我也跟DBA确认过，公司数据库底层最近也没有发生任何变更。那么到底是什么原因导致了这里突然会走index_merge优化从而造成死锁呢？
就在写这篇文章的时候，我突然想起来这两天有对表t1的索引结构作过变更：
变更之前的索引是这样的：
PRIMARY KEY (`id`),
KEY `idx_kdt_order` (`kdt_id`,`order_no`),
KEY `idx_payno_kdt` (`pay_no`,`kdt_id`),
变更之后是这个样子的：
PRIMARY KEY (`id`),
KEY `idx_order_kdt` (`order_no`,`kdt_id`),
KEY `idx_payno_kdt` (`pay_no`,`kdt_id`),
也就是将idx_kdt_order改成了idx_order_kdt，交换了kdt_id和order_no两个字段在索引中的顺序。
当初改这个索引的目的其实很简单，只是想根据order_no查询时也能走索引，加快查找速度。事实上，正常来说，修改这个索引顺序，对我们事务1这个sql语句来讲也不会有什么影响，两种索引条件下，事务1都有同时走order_no、pay_no这两个字段索引的可能性。
那是不是只要改了索引中的字段顺序就一定会走index_merge呢？也不是，或者说在我们这个场景中是。因为笔者在自己本地数据库测试的时候，在同样的表结构、索引结构下，并没有走index_merge优化。
从目前公开的资料来看，index_merge似乎还是一个玄学，它只在特定场景、特定数据量下才会发生，但具体什么是个什么法还说不清楚，所以最好的办法还是利用上面提到的5种方式，不给MySQL任何走index_merge的机会。
如何避免死锁？
死锁场景千千万，那么日常开发中，怎样才能更好地避免死锁呢？结合自己这一个多月来的踩坑经验，总结下来大概有以下几点是我们需要注意的：
- 根据业务场景，对表添加区分度较高的索引
- 确保不同事务之间以相同的顺序对索引进行加锁
- 尽量避免大事务，事务拿到锁后尽快释放，小事务发生锁竞争的概率更小
- 结合索引和业务SQL，尽量避免index_merge
更多技术文章，咱们公众号见，我在公众号里等你~
image.png
网友评论