hibernate mysql死锁_一次MySQL死锁问题解决

一次MySQL死锁问题解决

一、环境

CentOS, MySQL 5.6.21-70, JPA

问题场景：系统有定时批量更新数据状态操作，每次更新上千条记录，表中总记录数约为500W左右。

二、错误日志

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions

严重: Lock wait timeout exceeded; try restarting transaction

2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions

警告: SQL Error: 1213, SQLState: 40001

2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions

严重: Deadlock found when trying to get lock; try restarting transaction

三、排查

Check InnoDB status for locks

mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables

mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions

mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;

Check l