前段时间遇到一个问题,有个事务内的代码,但是显然没有全部回滚,导致业务事务不完整;error message如下
org.springframework.dao.CannotAcquireLockException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
如上显示,遇到的MySQLTransactionRollbackException,触发原因是“锁等待超时”,从mysql connector中抛出,抛出位置的代码如下,可以看到
#SQLError.class
public static SQLException createSQLException(String message, String sqlState, int vendorErrorCode, boolean isTransient, Throwable cause,
ExceptionInterceptor interceptor) {
try {
SQLException sqlEx = null;
if (sqlState != null) {
if (sqlState.startsWith("08")) {
if (isTransient) {
sqlEx = new SQLTransientConnectionException(message, sqlState, vendorErrorCode);
} else {
sqlEx = new SQLNonTransientConnectionException(message, sqlState, vendorErrorCode);
}
} else if (sqlState.startsWith("22")) {
sqlEx = new SQLDataException(message, sqlState, vendorErrorCode);
} else if (sqlState.startsWith("23")) {
sqlEx = new SQLIntegrityConstraintViolationException(message, sqlState, vendorErrorCode);
} else if (sqlState.startsWith("42")) {
sqlEx = new SQLSyntaxErrorException(message, sqlState, vendorErrorCode);
//40开头的sqlstate都会抛出这个异常
} else if (sqlState.startsWith("40")) {
sqlEx = new MySQLTransactionRollbackException(message, sqlState, vendorErrorCode);
} else if (sqlState.startsWith("70100")) {
sqlEx = new MySQLQueryInterruptedException(message, sqlState, vendorErrorCode);
} else {
sqlEx = new SQLException(message, sqlState, vendorErrorCode);
}
} else {
sqlEx = new SQLException(message, sqlState, vendorErrorCode);
}
if (cause != null) {
try {
sqlEx.initCause(cause);
} catch (Throwable t) {
// we're not going to muck with that here, since it's an error condition anyway!
}
}
return runThroughExceptionInterceptor(interceptor, sqlEx);
} catch (Exception sqlEx) {
SQLException unexpectedEx = new SQLException(
"Unable to create correct SQLException class instance, error class/codes may be incorrect. Reason: " + Util.stackTraceToString(sqlEx),
MysqlErrorNumbers.SQL_STATE_GENERAL_ERROR);
return runThroughExceptionInterceptor(interceptor, unexpectedEx);
}
}
到这里有几个疑问,1.什么情况下会造成lock wait timeout?2.为什么lock timeout为什么事务不全部回滚?3.为什么这里的异常命名用MySQLTransactionRollbackException?
1.什么情况下会造成lock wait timeout?
在mysql官网可以看得到关于lock wait timeout相关的配置如下,由于一行数据被另一个事务锁定,导致当下的事务一直等待,直至超时!(当然锁等待超时一般是不太容易的,要么是超时时间设置的太短,要么是代码逻辑上出现了死锁)
2.为什么lock timeout为什么事务不全部回滚?
继续往下看,可以知道mysql服务队锁等待超时的情况,不会回滚整个事务,如果需要锁等待超时回滚整个事务,需要配置参数innodb_rollback_on_timeout为true;(事实上现在的持久层框架,在遇到异常时,可以做到帮我们回滚整个事务,如spring的@Transactional注解)
3.为什么这里的异常命名用MySQLTransactionRollbackException?
我当初第一次看这个异常的名字,第一感觉是事务回滚过程中产生的异常,事实上,并不是。我翻阅相关文档,并没有任何解释,而且代码上也没有相关的注释,暂时只能不管他了!
Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction