postgresql 嵌套事务 - 回滚场景

Nested transactions - Rollback scenario

提问by Selva

A(){
    con.begin;
    .........
    .........
    B();
    ........
    ........(con.rollback;)
    con.commit;
    }

    B{
    con.begin;
    .......
    .......
    con.commit;
    }

In the above code, I begin a new DB transaction at A(). It executes some transaction successfully. After that B() starts executing and it also executes some transaction successfully and now the control returns to A(). At this point some exception occurs and I do a rollback. I would like to know whether the transaction which succeeded in B() will rollback or not.

在上面的代码中，我在 A() 处开始了一个新的数据库事务。它成功地执行了一些事务。之后 B() 开始执行并且它也成功地执行了一些事务，现在控制返回到 A()。此时发生了一些异常，我进行了回滚。我想知道在 B() 中成功的事务是否会回滚。

回答by Vineet Reynolds

The short answer, no. The long answer is as follows.

简短的回答，不。长答案如下。

Support for nested transactions in Java depends on various variables at play.

Java 中对嵌套事务的支持取决于各种变量。

Support for Nested transactions in JTA

支持 JTA 中的嵌套事务

First and foremost, if you are using JTA, it is upto to the Transaction Manager to support nested transactions. Any attempt to begin a transaction may result in a NotSupportedException being thrown by a Transaction Manager (that does not support nested transactions) if there is an attempt to start a new transaction in a thread that is already associated with a transaction.

首先也是最重要的，如果您使用 JTA，则由事务管理器来支持嵌套事务。如果尝试在已与事务关联的线程中启动新事务，则任何开始事务的尝试都可能导致事务管理器（不支持嵌套事务）抛出 NotSupportedException。

From the Java Transaction API 1.1 specification:

来自 Java Transaction API 1.1 规范：

3.2.1 Starting a Transaction

The TransactionManager.begin method starts a global transaction and associates the transaction context with the calling thread. If the Transaction Manager implementation does not support nested transactions, the TransactionManager.begin methodthrowsthe NotSupportedException whenthe calling thread is already associated with a transaction.

3.2.1 开始交易

TransactionManager.begin 方法启动一个全局事务并将事务上下文与调用线程相关联。如果事务管理器实现不支持嵌套事务，则当调用线程已与事务关联时，TransactionManager.begin 方法会抛出 NotSupportedException。

Support for Nested transactions in JDBC

支持 JDBC 中的嵌套事务

JDBC 3.0 introduces the Savepointclass, which is more or less similar to the concept of savepoints in the database. Savepoints have to be initialized using the Connection.setSavepoint()method that returns an instance of a Savepoint. One can roll back to this savepoint at a later point in time using the Connection.rollback(Savepoint svpt)method. All of this, of course, depends on whether you are using a JDBC 3.0 compliant driver that supports setting of savepoints and rolling back to them.

JDBC 3.0 引入了Savepoint类，它或多或少类似于数据库中保存点的概念。保存点必须使用返回保存点实例的Connection.setSavepoint()方法进行初始化。可以在稍后使用Connection.rollback(Savepoint svpt)方法回滚到此保存点。当然，所有这些都取决于您是否使用支持设置保存点和回滚到它们的 JDBC 3.0 兼容驱动程序。

Impact of Auto-Commit

自动提交的影响

By default, all connections obtained are set to auto-commit, unless there is a clear deviation on this front by the JDBC driver. This feature, if enabled, automatically rules out the scope of having nested transactions, for all changes made in the database via the connection are committed automatically on execution.

默认情况下，所有获得的连接都设置为自动提交，除非 JDBC 驱动程序在这方面有明显的偏差。如果启用此功能，则会自动排除嵌套事务的范围，因为通过连接在数据库中所做的所有更改都会在执行时自动提交。

If you disable the auto-commit feature, and choose to explicitly commit and rollback transactions, then committing a transaction always commits all changes performed by a connection until that point in time. Note, that the changes chosen for commit cannot be defined by a programmer - all changes until that instant are chosen for commit, whether they have been performed in one method or another. The only way out is to define savepoints, or hack your way past the JDBC driver - the driver usually commits all changes performed by a connection associated with a thread, so starting a new thread (this is bad) and obtaining a new connection in it, often gives you a new transaction context.

如果禁用自动提交功能，并选择显式提交和回滚事务，则提交事务始终会提交连接执行的所有更改，直到该时间点为止。请注意，选择用于提交的更改不能由程序员定义 - 直到那个时刻的所有更改都被选择用于提交，无论它们是以一种方法还是另一种方法执行的。唯一的出路是定义保存点，或者绕过 JDBC 驱动程序 - 驱动程序通常提交由与线程关联的连接执行的所有更改，因此启动一个新线程（这很糟糕）并在其中获取一个新连接, 通常会给你一个新的事务上下文。

You might also want to check how your framework offers support for nested transactions, especially if you're isolated from the JDBC API or from starting new JTA transactions on your own.

您可能还想检查您的框架如何为嵌套事务提供支持，尤其是当您与 JDBC API 或自己启动新的 JTA 事务隔离时。

---

---

Based on the above description of how nested transaction support is possibly achieved in various scenarios, it appears that a rollback in your code will rollback all changes associated with the Connection object.

根据上面对如何在各种场景中实现嵌套事务支持的描述，代码中的回滚似乎将回滚与 Connection 对象关联的所有更改。

回答by Mastermnd

That looks like poor transaction management i'm afraid. It would be good if you handle the commits and rollbacks from the callers to A and B instead.

恐怕这看起来像是糟糕的交易管理。如果您改为处理从调用者到 A 和 B 的提交和回滚，那就太好了。

A()
{
 //business code A
 B();
 //more business code A
}

B()
{
  //business code B
}

DoA()
{
  try
  {
     con.begin();
     A();
     con.commit();
  }
  catch(Exception e)
  {
     con.rollback();
  }
}

DoB()
{
  try
  {
     con.begin();
     B();
     con.commit();
  }
  catch(Exception e)
  {
     con.rollback();
  }
}

回答by Ashish Patil

As per your code, in A() you are starting transaction. Then jump to B() where you start transaction again, which in turn will commit all previous transaction. Then at end of B(), transaction is explicitly committed. At this point, all your code is committed. Now the code return to A() and remaining code is processed. In case of exception, only this part after B() call will be rolled back.

根据您的代码，在 A() 您开始交易。然后跳转到您再次开始事务的 B() ，这反过来将提交所有先前的事务。然后在 B() 结束时，事务被显式提交。此时，您的所有代码都已提交。现在代码返回到 A() 并处理剩余的代码。在异常情况下，只有 B() 调用后的这部分将被回滚。

回答by MAbraham1

You can use Java.SQL's built-in SavePoint function in Postgres 8 and up.

您可以在 Postgres 8 及更高版本中使用 Java.SQL 的内置 SavePoint 函数。

Connection conn = null;
Savepoint save = null;
DatabaseManager mgr = DatabaseManager.getInstance();
try {
    conn = mgr.getConnection();
    proc = conn.prepareCall("{ call writeStuff(?, ?) }");

    //Set DB parameters
    proc.setInt(1, stuffToSave);
    proc.setString(2, moreStuff);

    //Set savepoint here:
    save = conn.setSavepoint();

    //Try to execute the query
    proc.execute();

    //Release the savepoint, otherwise buffer memory will be eaten
    conn.releaseSavepoint(save);

} catch (SQLException e) {
    //You may want to log the first one only.
    //This block will attempt to rollback
    try {
        //Rollback to the Savepoint of prior transaction:
        conn.rollback(save);
    } catch (SQLException e1) {
        e1.printStackTrace();
    }
}

When a SQL-exception occurs, the current transaction is rolled-back to the SavePoint, and the remaining transactions may occur. Without the roll-back, subsequent transactions will fail.

当SQL异常发生时，当前事务回滚到SavePoint，其余事务可能会发生。如果没有回滚，后续事务将失败。