死锁分析

最近新项目上线，在压测和发布生产都出现了好几种死锁情况，分析一二

死锁日志

日志一：

日志二：

日志三：

这三个死锁日志特别的有意思，都是同一条SQL，但各种组合样的死锁都齐活了

日志一的两条sql，where里面的条件不同；

日志二和日志三sql完全一样，其实是两次调用(同一时刻并发调用)，调用条件不同，但在程序处理时这条SQL的where条件一样而已

分析

隔离级别RC

日志一，where里面的条件不同

第一个事务在等待RECORD LOCKS，锁模式为X model;位置在space id 428 page no 178

第二个事务持有RECORD LOCKS，锁模式为X model;位置也在space id 428 page no 178；等待的锁也是RECORD LOCKS，锁模式为X model，也在space id 428 page no 178

这个死锁很是奇怪，同一条sql，都是在索引【idx_tenant_user】上加锁，完全不符合死锁的循环等待特征

这张表上的索引

KEY `idx_collection_no` (`collection_no`) USING BTREE,KEY `idx_uniflag` (`invoice_uiq_flag`) USING BTREE,KEY `idx_tenant_user` (`tenant_id`,`user_id`) USING BTREE

通过explain查看此语句

索引上看使用idx_tenant_user,也与死锁日志一致，但怎么就死锁了呢？

【SHOW ENGINE INNODB STATUS;】只能显示最终发生死锁时的sql,并不能显示全部的sql，从程序上下文中寻找，发现点蛛丝马迹

事务2在T1时刻执行了一条根据id更新数据的sql，这条sql会在id聚簇索引上加X锁，还会在二级索引上加X锁，所以先获得了(userid,tenantid)锁，

事务1在T2时刻只能等待

事务2在T3时刻形成了循环等待，deadlock

日志二,sql完全一样

与前面解释，两个并发请求，入参不同，但到这个方法时，sql的条件是一样的

日志三，sql也完全一样

虽然与日志二的SQL一样，但死锁日志却不同

为什么同样的SQL，却得出各样的结果？时而collection_no索引在前，时而组合索引在前

苦思几日，在线上跑了下explain，发现了些问题

原来线上使用的index merge

有官网上有介绍：https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

MySQL在分析执行计划时发现走单个索引的过滤效果都不是很好，于是对多个索引分别进行条件扫描，然后将多个索引单独扫描的结果进行合并的一种优化操作。合并的方式分为三种：intersection、union和sort_union index merge 之 intersect,简单而言，index intersect merge就是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge.

正因为index merge,所以索引的真正执行顺序是不一样的，也就造成了各种表象

解决

建立联合索引

都使用主键更新

关掉参数index_merge_intersection=off，禁用index_merge功能

参照

MySQL 优化之 index_merge

一个 MySQL 死锁案例分析