Savepoint on JDBC

Question

I have a JDBC code where there are multiple Savepoints present; something like this:

1st insert statement
2nd insert statement
savepoint = conn.setSavepoint("S1");
1st insert statement
2nd update statement
savepoint = conn.setSavepoint("S2");
1st delete statement
2nd delete statement
savepoint = conn.setSavepoint("S3");
1st insert statement
2nd delete statement
savepoint = conn.setSavepoint("S4");

Now in the catch block, I am catching the exception and checking whether the Savepoint is null or not; if yes then rollback the entire connection else rollback till a Savepoint. But I am not able to understand till which Savepoint shall I roll back.

Will it be fine if I change all the savepoint names to "S1" ? In that case how will I understand how many till Savepoint did work correctly?

Please advise how to understand until what Savepoint the work was performed correctly?

Porringer

Answer 1

Would view this as multiple transactions. Hence you could handle this with multiple try/ catch blocks. You also seem to be overwriting the savepoint objects hence it would be not feasible to rollback.

More info. JDBC also supports to set save points and then rollback to the specified save point. The following method could be used to define save points.

SavePoint savePoint1 = connection.setSavePoint();

Rollback a transaction to an already defined save point using rollback call with an argument.

connection.rollback(savePoint1);

Reference. https://www.stackstalk.com/2014/08/jdbc-handling-transactions.html

Ilanailangilang

Answer 2

In such cases, I've found out the tricky part is to make sure you commit the transaction only if all inserts succeed, but rollback all updates if any insert fails. I've used a savepoint stack to handle such situations. The highly simplified code is as follows:

A connection wrapper class:

public class MyConnection {
    Connection conn;
    static DataSource ds;
    Stack<Savepoint> savePoints = null;

    static {
        //... stuff to initialize datasource.
    }

    public MyConnection() {
        conn = ds.getConnection();
    }

    public void beginTransaction() {
        if (savePoints == null) {
            savePoints = new Stack<Savepoint>();
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        } else {
            savePoints.push(conn.setSavepoint());
        }
    }

    public void commit() throws SQLException {
        if (savePoints == null || savePoints.empty()) {
            conn.commit();
        } else {
            Savepoint sp = savePoints.pop();
            conn.releaseSavepoint(sp);
        }
    }

    public void rollback() throws SQLException {
        if (savePoints == null || savePoints.empty()) {
            conn.rollback();
        } else {
            Savepoint sp = savePoints.pop();
            conn.rollback(sp);
        }
    }

    public void releaseConnection() {
        conn.close();
    }
}

Then you can have various methods that may be called independently or in combination. In the example below, methodA may be called on its own, or as a result of calling methodB.

public class AccessDb {

    public void methodA(MyConnection myConn) throws Exception {
        myConn.beginTransaction();
        try {
            // update table A
            // update table B
            myConn.commit();
        } catch (Exception e) {
            myConn.rollback();
            throw e;
        } finally {

        }
    }

    public void methodB(MyConnection myConn) throws Exception {
        myConn.beginTransaction();
        try {
            methodA(myConn);
            // update table C
            myConn.commit();
        } catch (Exception e) {
            myConn.rollback();
            throw e;
        } finally {

        }
    }
}

This way, if anything goes wrong, it rolls back fully (as a result of the exception handling), but it will only commit the entire transaction instead of committing a partially completed transaction.

Libbielibbna