CODEWITHSUNDEEP
javajdbctransactions
Trying
Trying

Reputation: 14278

nested transaction in JDBC

Suppose think of a scenario:

function a(){
      create savepoint s1
      insert x1
      insert x2
      b()
      insert x4
     if we get an error rollback to s1
     else commit.
}

function b(){
      create savepoint s2
      insert x3
     if we get an error rollback to s2
     else commit
}

My question is suppose my b() passes with no error and and there is a problem while inserting x4 so i fired rollback. So my question whether it will also revert the things which are inserted as part of the b() i.e. x3.

Upvotes: 0

Views: 878

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

Method b() shouldn't commit at all, the commit() should only be allowed after everything is done.

You should modify your scenario to:

function a(){
    create savepoint s1
    insert x1
    insert x2
    b()
    insert x4
    if we get an error rollback to s1
    else commit.
}

function b(){
    create savepoint s2
    insert x3
    if we get an error rollback to s2
}

The only difference is the remove of the commit branch in b(). The Connection will maintain savepoint s2 until the rollback to s1 or the commit.

Be aware that if function a() itself is also part of a larger transaction, the else commit there should be removed as well. If a() is the start of the transaction, then you should just rollback (not rollback to s1).

Response to comment

If you want these to be independently callable, or composable, then I'd suggest you use some form of a transaction manager that you pass into the method call.

Say something along the lines of

interface TransactionManager() {
    void commit();

    void rollback();

    TransactionManager subTransaction();
}

class RootTransactionManager() implements TransactionManager {
    private final Connection connection;

    RootTransactionManager(Connection connection) {
        this.connection = connection;
    }

    public void commit() {
        connection.commit();
    }

    public void rollback() {
        connection.rollback();
    }

    public TransactionManager subTransaction() {
        return new SubTransactionManager(this);
    }

    Connection getConnection() {
        return connection;
    }
}

class SubTransactionManager implements TransactionManager() {
    private final RootTransactionManager manager;
    private final SavePoint savePoint;

    SubTransactionManager (RootTransactionManager manager) {
        this.manager = manager;
        savePoint = manager.getConnection().setSavePoint();
    }

    public void commit() {
        // do nothing (semantics of Connection.releaseSavePoint not useful for nesting)
    }

    public void rollback() {
        connection.rollback(savePoint);
    }

    public TransactionManager subTransaction() {
        return new SubTransactionManager(manager);
    }
}

You can use this to implement your methods as:

void someOtherMethod() {
    Connection connection; // = ...
    a(new RootTransactionManager(connection));
}

void a(TransactionManager tm) {
    try {
        // do stuff
        b(tm.subTransaction);
        // do stuff
        tm.commit();
    } catch (SQLException ex) {
        tm.rollback();
    }
}

void b(TransactionManager tm) {
    try {
        // do stuff
        tm.commit();
    } catch (SQLException ex) {
        tm.rollback();
    }
}

This is just a quick sketch and I might come up with something completely different if I actually took more time for it.

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30849

It will revert the things before the last save point i.e. Insert X1, X2 and b()'s modifications will not be reverted.

Ideally, to maintain the atomicity of transaction, we should not issue commit statements in between it. Commit() should only be issued if all the operations get completed successfully. However, if something fails, rollback should be issued (typically in catch block).

Upvotes: 0

Related Questions