mysql deadlock的排查

1. 查看 mysql里关于Deadlock的日志

命令： mysql> show engine innodb status \G;

2. 分析dead lock的日志

写道

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-14 13:51:03 7f3efdceb700
*** (1) TRANSACTION:
TRANSACTION 280219276, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 2936, 8 row lock(s), undo log entries 6
MySQL thread id 375, OS thread handle 0x7f3efdb65700, query id 75220097 ded405.zylon.net 193.*.*138 root updating
update article set version=16 where id=46182 and version=15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21 page no 786 n bits 136 index `PRIMARY` of table `sils`.`article` trx id 280219276 lock_mode X locks rec but not gap waiting
Record lock, heap no 55 PHYSICAL RECORD: n_fields 32; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 280219277, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
17 lock struct(s), heap size 2936, 8 row lock(s), undo log entries 6
MySQL thread id 440, OS thread handle 0x7f3efdceb700, query id 75220111 ded405.zylon.net 193.*.*.138 root updating
update article set version=16 where id=46182 and version=15
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21 page no 786 n bits 136 index `PRIMARY` of table `sils`.`article` trx id 280219277 lock mode S locks rec but not gap
Record lock, heap no 55 PHYSICAL RECORD: n_fields 32; compact format; info bits 0

mysql默认只会记录最后一次产生deadlock时的日志， 着重查看发生冲突的两个transaction里的对mysql的操作。

3. 结合tomcat里关于deadlock的堆栈信息 分析哪个流程出错。 （必要时，需要将hibernate操作时的sql语句都打印出来一并分析）。

 

未完待续。。。