1、程序中报错日志:
Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException:
Lock wait timeout exceeded; try restarting transaction;
Lock wait timeout exceeded; try restarting transaction;
nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException:
Lock wait timeout exceeded; try restarting transaction
2、查看未提交事务:
select * from information_schema.INNODB_TRX;
SELECT
a.id,a.user,a.host,b.trx_started,b.trx_query
FROM information_schema.processlist a RIGHT OUTER JOIN information_schema.innodb_trx b
ON a.id = b.trx_mysql_thread_id;
3、手动执行该update语句,失败,等待锁超时:
update orders set *** where id='79302e18a8e848ccb2323c48ca4ca349';
update orders set *** where id='aeff09dbbc1a4668bf959f675d536ece';
4、由于是生产,急需处理业务数据,所以临时解决方案,是先kill 掉,未能提交事务的线程,
kill trx_mysql_thread_id(上图中的trx_mysql_thread_id是10, 所以 执行 kill 10)
目前的解决方案,就是手动kill掉长期挂起,未提交的事务。