【作者】

刘博：携程技术保障中心数据库高级经理，主要关注Sql server和Mysql的运维和故障处理。

【环境】

版本号：5.6.21
隔离级别：REPEATABLE READ

【问题描述】

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

登录Mysql服务器查看日志：

mysql> show engine innodb status\G
 *** (1) TRANSACTION:
 TRANSACTION 102973, ACTIVE 11 sec starting index read
 mysql tables in use 3, locked 3
 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
 MySQL thread id 6, OS thread handle 140024996574976, query id 83 localhost us updating
 UPDATE TestTable
 SET column1 = 1,
 Column2 = sysdate(),
 Column3= '026’
 Column4 = 0
 AND column5 = 485
 AND column6 = 'SEK'
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_column6 of table test.TestTable trx id 102973 lock_mode X waiting
 Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 53454b; asc SEK;;
 1: len 8; hex 80000000007e1452; asc ~ R;;
 
 
*** (2) TRANSACTION:
 TRANSACTION 102972, ACTIVE 26 sec starting index read
 mysql tables in use 3, locked 3
 219 lock struct(s), heap size 24784, 2906 row lock(s), undo log entries 7
 MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updating
 UPDATE TestTable
 SET Column1 = 1,
 Column2 = sysdate(),
 Column3 = '026'
 Column4 = 0
 AND Column5 = 485
 AND Column6 = 'SEK'
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_Column6 of table test.TestTable trx id 102972 lock_mode X
 Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
 
Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 53454b; asc SEK;;
 1: len 8; hex 80000000007e1452; asc ~ R;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 601 page no 89642 n bits 1000 index idx_column6 of table test.TestTable trx id 32231