Lock wait timeout exceeded; try restarting transaction临时解决办法

错误日志

Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction### The error may involve com.zhonghe.userim.dao.mysql.CrmRoleDao.updateById-Inline### SQL: UPDATE crm_role  SET content_permission=?, name=?, `description`=?, self_apply_status=?,  updated_at=?  WHERE id=?; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transactionat org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:262)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)

遇到事务等待问题时，我们首先要做的是找到正在执行的事务。 ​​information_schema.INNODB_TRX​​ 表中包含了当前innodb内部正在运行的事务信息，这个表中给出了事务的开始时间

查看事务

-- 当前运行的所有事务
SELECT * from information_schema.innodb_trx;

sql

/*PROXY:10.0.6.249,d2fd98510040000*/ DELETE 
FROM
	crm_role_permission 
WHERE
	id IN (
		4749806,
		4749813,
		4749820,
		4749827,
		4749834,
		4749841,
		4749848,
		4749855,
		4749862,
		4749869,
		4749876,
		4749883,
		4749890,
		4749897,
		4749904,
		4749911,
		4749918,
		4749925,
		4749932,
		4749939,
		4749946,
		4749953,
		4749960,
		4749967,
		4749974,
		4749981,
		4749988,
		4749995,
		4750002,
		4750009,
		4750016,
		4750023,
		4750030,
		4750037,
		4750044,
		4750051,
		4750058,
		4750065,
		4750072,
		4750079,
		4750086,
		4750093,
		4750100,
		4750107,
		4750114,
		4750121,
		4750128,
		4750135,
		4750142,
		4750149,
		4750156,
		4750163,
		4750170,
		4750177,
		4750184,
		4750191,
		4750198,
		4750205,
		4750212,
		4750219,
		4750226,
		4750233,
		4750240,
		4750247,
		4750254,
		4750261,
		4750268,
		4750275,
		4750282,
		4750289,
		4750296,
		4750303,
		4750310,
		4750317,
		4750324,
		4750331,
		4750338,
	4750345,
	4750)

杀掉事物

-- kill 线程ID(trx_mysql_thread_id)
kill 8375425;

只是临时解决, 根本还是需要优化代码

参考博客

https://blog.51cto.com/u_6353447/5873555

https://blog.csdn.net/Boring_05/article/details/131169567