Implementing support for nested transactions using JDBC 3.0

Question

Hi all,

our legacy application uses JDBC 3.0. it supports transactions by implementing its own transaction manager that is capable of returning the same JDBC connection for each thread. The problem I recently discovered is that it doesn't support nested transactions: if a transaction is started inside an another transaction, then every SQLs running in the context of the inner transaction will be executed using the same db connection, and when it's commited or rolled back it will automatically commit or roll back all the changes starting from the outer transaction.

As far as I understand I can implement the support for nested transaction using JDBC 3.0 savepoints: whenever a nested transaction is started, I can set a new savepoint for the current connection. Afterward, if the nested transaction is rolled back, I will just rollback to this savepoint. If, on the other hand, it is commited, I will just do nothing. Only the commit of the most outer transaction will save the changes to the db.

Is this correct? Does this approach have any flaws? If yes, what are my possibilities?

Thanks.

Answer 1

Transactions are a little tricky and cannot really be viewed from a JDBC layer, but the underlying database itself. I will speak about Oracle from here on, as it is what I have the most experience with. In Oracle if you start a transaction, you can rollback to savepoints within the transaction but you can't commit using savepoints. So let's say I start a transaction and have three savepoints, A,B, and C. I can proceed happily forward and rollback to A, B, or C, but once you commit, you have started a new transaction and now A, B, and C are no longer valid. I hope this well help answer you question.

Answer 2

There could be dependencies in the code that expect the commit to be done rather than deferred (for example, if the isolation level is set to TRANSACTION_READ_COMMITTED).

Consider fixing your transaction manager to do the nested transaction on a separate connection.

UPDATE: It looks like the Spring framework uses SavePoints to provide nested transactions. My guess is that they just ignore the issue of isolation mode.

Answer 3

Hi,

You could try the nested transaction support in Atomikos TransactionsEssentials.

However, nested transactions in a DBMS are generally restricted in the following way:

-either your nested transactions share the same DB transaction, which allows shared data access at the cost of rollback granularity (you rollback the whole thing)

-or your nested transactions are mapped (by Atomikos) to different underlying DB transactions, at the cost of not allowing shared data access for hotspot data

This mismatch is due to ACID nature of database transactions. Eventually, all your DBMS access is bound to happen in such a database transaction.

If you want to wrap something up yourself, the savepoint approach you mention sounds promising - but then you would probably need to make sure to test it extensively.

Best Guy