【作者】
刘博：携程技术保障中心数据库高级经理，主要关注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 readmysql tables in use 3, locked 3LOCK 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 updatingUPDATE TestTableSET column1 = 1,Column2 = sysdate(),Column3= '026'Column4 = 0AND column5 = 485AND 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 waitingRecord lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 3; hex 53454b; asc SEK;;1: len 8; hex 80000000007e1452; asc ~ R;;*** (2) TRANSACTION:TRANSACTION 102972, ACTIVE 26 sec starting index readmysql tables in use 3, locked 3219 lock struct(s), heap size 24784, 2906 row lock(s), undo log entries 7MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updatingUPDATE TestTableSET Column1 = 1,Column2 = sysdate(),Column3 = '026'Column4 = 0AND Column5 = 485AND 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 XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: 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 32231892482 lock_mode X locks rec but not gap waitingRecord lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 3; hex 53454b; asc SEK;;1: len 8; hex 80000000007eea14; asc ~ ;; |
