sql - 外部事务失败时回滚内部事务

Question

我正在使用 ORMLite 访问 Java 中的 h2 数据库。
为了执行交易，我使用静态方法 TransactionManager.callInTransaction .

在单个独立事务的情况下，这可以正常工作。但是，如果事务相互嵌套，即使外部事务失败，内部事务也会被提交。

在这段伪代码中:

OuterDatabaseTransaction
{
  Loop
  {
    InnerDatabaseTransaction
    {
      //Multiple update or create statements
      //One of the InnerDatabaseTransactions throws a random exception
    }
    //Alternatively the OuterDatabaseTransaction throws a random
    //exception but all commited InnerDatabaseTransactions should rollback still
  }
}

所以我期望的是，如果任何内部事务失败，外部事务也会失败。如果外部事务失败，则不会提交任何内部事务。
目前似乎每个内部事务都是单独提交的，例如不共享相同的 Savepoint作为外部交易。

更新 (谢谢否认)

查看跟踪显示以下内容

[TRACE] JdbcDatabaseConnection connection is closed returned false
[TRACE] JdbcDatabaseConnection connection autoCommit is true
[TRACE] JdbcDatabaseConnection connection set autoCommit to false
[DEBUG] TransactionManager had to set auto-commit to false
[TRACE] JdbcDatabaseConnection save-point sp14: id=0 name=ORMLITE15 set with name ORMLITE15
[DEBUG] TransactionManager started savePoint transaction ORMLITE15
[TRACE] JdbcDatabaseConnection connection is closed returned false
[TRACE] JdbcDatabaseConnection connection autoCommit is false
[TRACE] JdbcDatabaseConnection save-point sp15: id=0 name=ORMLITE16 set with name ORMLITE16
[DEBUG] TransactionManager started savePoint transaction ORMLITE16
[TRACE] JdbcDatabaseConnection connection is closed returned false
[TRACE] JdbcDatabaseConnection update statement is prepared and executed returning 1: <...>
[DEBUG] BaseMappedStatement update data with statement <...> changed 1 rows
[TRACE] BaseMappedStatement update arguments: <...>
[TRACE] JdbcDatabaseConnection connection is committed for save-point ORMLITE16
[DEBUG] TransactionManager committed savePoint transaction ORMLITE16
-> [ERROR] TransactionManager after commit exception, rolling back to save-point also threw exception
[TRACE] JdbcDatabaseConnection connection set autoCommit to true
[DEBUG] TransactionManager restored auto-commit to true
[TRACE] JdbcDatabaseConnection connection is closed returned false

进入源代码显示，在 OuterDatabaseTransaction 回滚期间，异常在以下函数内的 org.h2.engine.Session.java 中的 h2 源代码中抛出。然而，这背后的原因我还不明白。

private void checkCommitRollback() {
  if (commitOrRollbackDisabled && locks.size() > 0) {
    throw DbException.get(ErrorCode.COMMIT_ROLLBACK_NOT_ALLOWED);
  }
}

更新 2

发表 ORMLite Bug Report

Answer 1

找原因可以转跟踪 级别登录。工作嵌套事务应该产生这样的日志:

TRACE: connection supports save points is true
TRACE: save-point <...> set with name ORMLITE1
DEBUG: started savePoint transaction ORMLITE1
...
TRACE: save-point <...> set with name ORMLITE2
DEBUG: started savePoint transaction ORMLITE2
...
TRACE: connection is committed for save-point ORMLITE2
...
TRACE: save-point <...> set with name ORMLITE3
DEBUG: started savePoint transaction ORMLITE3
...
TRACE: save-point ORMLITE3 is rolled back
...
TRACE: save-point ORMLITE1 is rolled back

在这个例子中，ORMLITE1 是外部事务的保存点，ORMLITE2 和 ORMLITE3 是内部事务的保存点。第一个内部事务最初被提交，第二个事务回滚到 ORMLITE3 并导致外部事务回滚到 ORMLITE1，它反过来隐式取消了第一个内部事务。

但是如果你在日志中看到这个:

TRACE: connection supports save points is false

那么 JDBC 驱动程序不支持保存点，并且嵌套事务将不起作用。从理论上讲，这不应该发生，因为 H2 声明支持保存点。

如果你看到这个:

ERROR: after commit exception, rolling back to save-point also threw exception

然后由于某种原因回滚到保存点失败。检查您是否使用相同的 ConnectionSource用于外部和内部事务。

或者您在日志中发现导致问题的其他内容。此外，将日志附加到您的问题可能会有所帮助。我建议用真正的 Java 代码替换伪代码。

更新

这是您遇到的错误的官方描述:

COMMIT_ROLLBACK_NOT_ALLOWED = 90058

The error with code 90058 is thrown when trying to call commit or rollback inside a trigger, or when trying to call a method inside a trigger that implicitly commits the current transaction, if an object is locked. This is not because it would release the lock too early.

链接: http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90058

可能这会帮助您进一步找到问题的原因。对我来说，很难说没有看到你的代码。祝你好运!