今天之前的同事問我，之前我負責的系統(tǒng)出現(xiàn)了MySQL異常，但是事務卻沒有回滾，事務前半部分執(zhí)行的SQL在數(shù)據(jù)庫能看到，日志信息如下

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)

從日志上能很明顯的看到出現(xiàn)了死鎖，出現(xiàn)了死鎖為什么之前已經(jīng)執(zhí)行了的SQL沒有回滾呢，帶著這個問題去了解了一下MySQL的事務回滾，發(fā)現(xiàn)有一個參數(shù)很重要 innodb_rollback_on_timeout,先查看一下數(shù)據(jù)的設置：

show VARIABLES like 'innodb_rollback_on_timeout'

可以看到這個變量是OFF
查看了一下MySQL的官網(wǎng)對這個參數(shù)的解釋，大概就是說在MySQL 5.6&5.7中默認值為OFF，當InnoDB默認情況下僅回滾事務超時的最后一條語句。如果innodb_rollback_on_timeout值為ON，則事務超時后將導致InnoDB中止并回滾整個事務。到這兒問題大概就清晰了，因為這個參數(shù)，所以這個事務只回滾了最后出現(xiàn)死鎖的那條SQL，將這個值改成ON，再測試發(fā)現(xiàn)一切正常。

作者：接天居士
來源：CSDN
原文：https://blog.csdn.net/jtjs1989/article/details/81328634
版權聲明：本文為博主原創(chuàng)文章，轉(zhuǎn)載請附上博文鏈接！