Behaviour of active transaction on connection close?

Question

If the close method is called and there is an active transaction, what will happen to active transactions? Will they be commited or rolled back?

Answer 1

Adding to the other answer, I tested the behavior on Oracle and SQL Server, the databases I'm currently working with.

MSSQL rolls back the transaction. This is what you'd intuitively expect.

Oracle on the other side, commits the transaction. This is documented in their JDBC Guide:

If the auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit COMMIT operation is run.

Sure, the JDBC spec gives you freedom to go either way, but I personally think that implicitly committing the transaction is a poor design choice. As an argument, consider the use case of a thread which is busy working on a long-ish transaction and is not responsive to a shutdown request. When the application eventually closes the connection pool, this will in turn close the connection, committing the incomplete transaction!

The moral of this story is that connection pool implementations must always call rollback() before closing a connection in manual commit mode. However this is not something that just comes to mind when implementing a connection pool. As an example, see PooledConnectionImpl from DBCP