CODEWITHSUNDEEP
javapostgresql
Selva
Selva

Reputation: 839

Nested transactions - Rollback scenario

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.

Upvotes: 10

Views: 18535

Answers (4)

MAbraham1
MAbraham1

Reputation: 1768

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

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.

Upvotes: 0

Vineet Reynolds
Vineet Reynolds

Reputation: 76709

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

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

Support for Nested transactions in 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.

From the Java Transaction API 1.1 specification:

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.

Support for Nested transactions in JDBC

JDBC 3.0 introduces the Savepoint class, 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.

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.

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.

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.

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.

Upvotes: 11

Ashish Patil
Ashish Patil

Reputation: 808

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.

Upvotes: 0

Mastermnd
Mastermnd

Reputation: 172

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()
{
 //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();
  }
}

Upvotes: 0

Related Questions