MySQL DeadLock故障排查过程 (2)

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

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \G;
*************************** 1. row ***************************
waiting_trx_id: 103006
waiting_thread: 36
waiting_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'
blocking_trx_id: 103003
blocking_thread: 37
blocking_query: NULL
*************************** 2. row ***************************
waiting_trx_id: 421500433538672
waiting_thread: 39
waiting_query: select count(Column5) from TestTable where Column5 = 485
blocking_trx_id: 103006
blocking_thread: 36
blocking_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits \G;
*************************** 1. row ***************************
requesting_trx_id: 103006
requested_lock_id: 103006:417:1493:859
blocking_trx_id: 103003
blocking_lock_id: 103003:417:1493:859
*************************** 2. row ***************************
requesting_trx_id: 421500433538672
requested_lock_id: 421500433538672:417:749:2
blocking_trx_id: 103006
blocking_lock_id: 103006:417:749:2
2 rows in set, 1 warning (0.00 sec)

mysql> select * from INNODB_LOCKS \G;
*************************** 1. row ***************************
lock_id: 103006:417:1493:859
lock_trx_id: 103006
lock_mode: X
lock_type: RECORD
lock_table: test.TestTable
lock_index: idxColumn6
lock_space: 417
lock_page: 1493
lock_rec: 859
lock_data: 'SEK', 8262738
*************************** 2. row ***************************
lock_id: 103003:417:1493:859
lock_trx_id: 103003
lock_mode: X
lock_type: RECORD
lock_table: test.TestTable
lock_index: idxColumn6
lock_space: 417
lock_page: 1493
lock_rec: 859
lock_data: 'SEK', 8262738
*************************** 3. row ***************************
lock_id: 421500433538672:417:749:2
lock_trx_id: 421500433538672
lock_mode: S
lock_type: RECORD
lock_table: test.TestTable
lock_index: column5_index
lock_space: 417
lock_page: 749
lock_rec: 2
lock_data: 485, 8317620
*************************** 4. row ***************************
lock_id: 103006:417:749:2
lock_trx_id: 103006
lock_mode: X
lock_type: RECORD
lock_table: test.TestTable
lock_index: column5_index
lock_space: 417
lock_page: 749
lock_rec: 2
lock_data: 485, 8317620
4 rows in set, 1 warning (0.00 sec)

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

时间点 Session1 Session2
1   477 SEK    
2     485 SEK  
3   485 SEK   死锁发生  

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

【解决方法】

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

我们环境当时的情况发现Column6的筛选度非常低，就删除了Column6的索引。
10:55左右删除索引后，报错没有再发生：