mysql排障方法_MySQL DeadLock故障排查过程

【环境】

版本号：5.6.21

隔离级别：REPEATABLE READ

【问题描述】

接到监控报警，有一个线上的应用DeadLock报错，每15分钟会准时出现，报错统计如下图：

登录Mysql服务器查看日志：

大致一看，更新同一索引的同一行，应该是一个Block，报TimeOut的错才对，怎么会报DeadLock?

【初步分析】

先分析下(2) TRANSACTION，TRANSACTION 32231892482。

等待的锁信息为：

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eea14; asc

持有的锁信息为：

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eeac4; asc

再先分析下(1) TRANSACTION，TRANSACTION 32231892617。

等待的锁信息为：

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eeac4; asc

于是可以画出的死锁表，两个资源相互依赖，造成死锁：TRANSACTIONHoldWait3223189261753454b80000000007eea1453454b80000000007eeac43223189248253454b80000000007eeac453454b80000000007eea14

让我们再看一下explain结果：

可以看到 EXTRA 列：

Using intersect(column5_index,idxColumn6)

从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

【模拟与验证】

根据以上初步分析，猜测应该就是intersect造成的，于是在测试环境模拟验证，开启2个session模拟死锁：

时间序列Session1Session21Begin；2UPDATE TestTable SET Column2 = sysdate() Column4 = 0 AND Column5 = 47 AND Column6 = ‘SEK

执行成功，影响7行3Begin；4UPDATE TestTable SET Column2 = sysdate(),Column4 = 0 AND Column5 = 485 AND Column6 = ‘SEK’；

被Blocking5UPDATE TestTable SET Column2 = sysdate(),Column4 = 0 AND Column5 = 485 AND Column6 = ‘SEK’；

执行成功ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

依据以上信息可以发现Session2虽然被Block了，但也获取了一些Session1在时间序列5时所需资源的X锁，可以再开启一个查询select count(Column5) from TestTable where Column5 = 485，设置SET TRANSACTION ISOLATION LEVEL SERIALIZABLE，去查询Column5 = 485的行，观察锁等待的信息：

可以看到Session2，trx_id 103006阻塞了trx_id 421500433538672，而trx_id 421500433538672 requested_lock也正好是lock_data: 485, 8317620。由此可见Session2虽然别block了，但是还是获取到了Index column5_index相关的锁。被Block是因为intersect的原因，还需要idxColumn6的锁，至此思路已经清晰，对整个分配锁的信息简化一下，如下表格(请求到的锁用青色表示，需获取但未获取到的锁用红色表示)：

时间点Session1Session21477 SEK2485 SEK3485 SEK死锁发生

可以看到485 SEK这两个资源形成了一个环状，最终发生死锁。

【解决方法】

最佳的方法是添加column5和Column6的联合索引。

我们环境当时的情况发现Column6的筛选度非常低，就删除了Column6的索引。

10:55左右删除索引后，报错没有再发生：