死锁分析

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

死锁日志

日志一:

日志二:

日志三:

这三个死锁日志特别的有意思,都是同一条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】上加锁,完全不符合死锁的循环等待特征

这张表上的索引

1
2
3
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锁,所以先获得了(user_id,tenant_id)锁,

事务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 死锁案例分析

公众号:码农戏码
欢迎关注微信公众号『码农戏码』