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

拼搏现实的明天。 2023-02-24 15:22 18阅读 0赞

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'

![70][]

可以看到这个变量是OFF

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

**修改方式为：my.ini 文件**

innodb\_rollback\_on\_timeout=on  
![20200709180732476.png][]

[70]: /images/20230209/21c2a7d4413f49979f62c4eac7de5751.png
[20200709180732476.png]: /images/20230209/8f8597ed0b34452d9fd89dc29ca930b5.png