最近新项目上线,在压测和发布生产都出现了好几种死锁情况,分析一二
死锁日志
日志一:
日志二:
日志三:
这三个死锁日志特别的有意思,都是同一条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 死锁案例分析