JDBC transaction rollback failure on closed connection

Question

Is it possible that if the connection to a db resource is lost during commit, which might lead to failed commit followed by failed rollback, the transaction is partially committed ? This appears to be happening in my case but wanted better clarity on this from the community. HibernateTransactionManager and Oracle are being used with JDBC drivers.

Answer 1

Adding an answer since I could find why partial commits were happening. The jdbc connection pool in use had property set on it to remove abandoned connections. On removal of such connections from the pool, close() would be called on such connections which would lead to commit of transactions which were using those connections at that time(not abandoned from application perspective). A solution would be to intercept such close calls and rollback the connection or handle appropriately

Answer 2

No (barring a serious bug in Oracle or unless we are talking about a distributed transaction).

If a connection between the client and the database is lost, it is possible that the database never received the request to commit the transaction. In that case, once the database discovers that the client is dead (which may take some time), the transaction will be rolled back. If the connection is lost, it is possible that the database successfully commits but the client never gets the notification that the commit was successful.

If we're talking about a distributed transaction, it is possible that the transaction remains in doubt on one (or more) of the distributed nodes. If that's the case, the transaction would appear in dba_2pc_pending on the nodes where the transaction is still pending. But it wouldn't be partially committed on any node.

If I had to guess, if you're seeing something that is "partially committed", I'd wager that the problem is that your transaction boundaries aren't correct and that you have some code somewhere that is committing (implicitly or explicitly) where you don't expect it.