Java – Implementing support for nested transactions using JDBC 3.0

javajdbctransactions

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.

Best Answer

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.

Related Topic