Mysql死锁引起的事务未回滚问题 Deadlock found when trying to get lock； try restarting transaction

飞熊 • 2023年8月20日下午4:32 • 技术随笔 • 阅读 266

MySQL异常，但是事务却没有回滚，事务前半部分执行的SQL在数据库能看到，日志信息如下

org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.jzy.kaisafax.trade.invest.dao.TrdInvestRepayEntityMapper.updateByExampleSelective-Inline
### The error occurred while setting parameters
### SQL: UPDATE trd_investrepay  SET loanRepayId = ? WHERE (  loanId = ? and period = ? )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:421)
	at com.sun.proxy.$Proxy27.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:270)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
	at com.sun.proxy.$Proxy30.updateByExampleSelective(Unknown Source)
	at com.sxjr.common.base.service.BaseService.updateByExample(BaseService.java:77)
	at sun.reflect.GeneratedMethodAccessor1054.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)

从日志上能很明显的看到出现了死锁，出现了死锁为什么之前已经执行了的SQL没有回滚呢，带着这个问题去了解了一下MySQL的事务回滚，发现有一个参数很重要 innodb_rollback_on_timeout,先查看一下数据的设置：

show VARIABLES like 'innodb_rollback_on_timeout'

可以看到这个变量是OFF

查看了一下MySQL的官网对这个参数的解释，大概就是说在MySQL 5.6&5.7中默认值为OFF，当InnoDB默认情况下仅回滚事务超时的最后一条语句。如果innodb_rollback_on_timeout值为ON，则事务超时后将导致InnoDB中止并回滚整个事务。到这儿问题大概就清晰了，因为这个参数，所以这个事务只回滚了最后出现死锁的那条SQL，将这个值改成ON，再测试发现一切正常。

修改方式为：my.ini 文件

innodb_rollback_on_timeout=on