rollback db operations spanning db sessions on operation fail

[Abhishk Singh]

I need to call a db procedure 3 times. All 3 calls will be in 3 different db sessions. The requirement is such that if any of the 3 calls fail, all three transactions should roll back. The respective commits should happen only when all 3 procedures have executed successfully.

Already tried solutions:

Initiate 3 different connections inside a method. Call the procedure on those connections, and when one of them fails, call rollback on all connections. When the execution of 2nd call failed, I called rollback 1st connection. This resulted in a SocketException. I do not have the stacktrace available.

Using IBM WID and WPS: Encapsulated the db service calls in a single WPS server transaction and made 3 calls to database procedure. This resulted in partial commit, that is, the first call resulted in an insert inside database and did not rollback after the failure of 2nd call.

[K. Tsang]

For your IBM WID and WPS approach, do check the vendor doc for things like whether closing connection or flushing or moving to new DB call does a "auto-commit". I'm pretty sure such setting can be set temporarily (eg through coding) or permanently.

Also for each of the procedure, do not have commit, else it will commit after each procedure no matter what happens to the other procedures.

The commit level must be set outside of the 3 procedure calls (eg in server transaction or program explicitly stating tx.begin() .... tx.commit() )

If you haven't tried you may considered using JTA to create a transaction and calling the procedures inside:

Transaction tx = ... // get transaction
tx.begin();
try {
//call procedures 1,2,3
tx.commit();
} catch (Exception e) {
tx.rollback();
e.printStackTrace();
}

[Abhishk Singh]

I could do it using WID and WPS and got it verified.
JTA seems as a good alternative. Will verify it too. Thanks for the input.