DataSourceTransactionManager closes JDBC connection on doBegin failure but leaves it attached to transaction object [SPR-12280]

[spring-projects-issues]

Simon Kitching opened SPR-12280 and commented

DataSourceTransactionManager.doBegin first allocates a connection from the datasource and stores it in the DataSourceTransactionObject. It then does some other work; if any of this other work throws an exception then that connection is released but is not removed from the DataSourceTransactionObject. The result is a misleading "java.sql.SQLException: Connection is closed" exception later.

This problem is present in current trunk code, and at least as far back as 3.0.7 (the version I'm using):

• https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/datasource/DataSourceTransactionManager.java

In my particular case, this line in DataSourceTransactionManager.doBegin is throwing an exception:
Integer previousIsolationLevel = DataSourceUtils.prepareConnectionForTransaction(con, definition);
which causes the catch clause in the same method to run.

Of course if I resolve the initial problem that causes prepareConnectionForTransaction to fail ("SET TRANSACTION must be first statement of transaction" on oracle), then this issue will no longer occur. However the presence of misleading "connection closed" errors makes diagnosis of the real problem more difficult..

This problem seems similar to/related to the following existing issues:

• JDBC Connection is not released due to incorrectly defined catch [SPR-10755] #15381
• Restoring previous isolation level causes side effects on Oracle [SPR-2090] #6783
• LDAP-179

---

Affects: 3.2.11, 4.0.7, 4.1 GA

Issue Links:

• JDBC Connection is not released due to incorrectly defined catch [SPR-10755] #15381 JDBC Connection is not released due to incorrectly defined catch

Backported to: 4.0.8, 3.2.12

[spring-projects-issues]

Juergen Hoeller commented

Where do you get the "java.sql.SQLException: Connection is closed" exception from later on? The current code assumes that the newly built ConnectionHolder won't be used anymore after a doBegin failure, and I suppose that assumption is flawed... I'm just wondering where exactly the ConnectionHolder is showing up again.

Juergen

[spring-projects-issues]

Juergen Hoeller commented

DataSourceTransactionManager resets the ConnectionHolder on doBegin failure now. This will be available in the upcoming 4.1.2 snapshot; please give it a try! This is also scheduled for a backport to 4.0.8 and 3.2.12.

Juergen

[spring-projects-issues]

Simon Kitching commented

Thanks very much.

The case in which I encounter this is unusual: using a TransactionSynchronization.afterCompletion() callback to try to execute some SQL. If within this callback I don't call PlatformTransactionManager.getTransaction with a PROPAGATION_NEW, then the "Connection is closed" occurs. If I do try PROPAGATION_NEW, then a java.lang.IllegalStateException: No value for key [org.apache.ibatis.session.defaults.DefaultSqlSessionFactory@2b3efe3e] bound to thread occurs as the AbstractPlatformTransactionManager tries to suspend the current transaction.

I know this is a weird case : trying to perform SQL from an "afterCompletion" callback at which point the current transaction context is partially but not completely destructed. However I have the requirement that if a particular transaction rolls back then some other rows must be inserted into the database (error status). Still working on how to get this functioning somehow :-(

However it looks to me like the following would also have triggered this problem before your patch (not actually tried..):

• start a NEW transaction
• call a method which starts a SUPPORTS transaction (ie create a new transaction object that shares the existing SQL connection) which then fails somewhere in the middle of doBegin thus causing the SQL connection to be released.
• in the caller, try to perform some SQL - isn't there then a valid transaction-object whose ConnectionHolder holds a closed connection?

[spring-projects-issues]

Juergen Hoeller commented

Aligned with DataSourceTransactionManager, I've revised all of our transaction manager implementations for consistent cleanup after doBegin failure. Also, JmsTransactionManager explicitly closes the JMS Session in that case now, right before closing the JMS Connection.

Juergen