Spring managed Transaction results in closed connection
<p>I had an application that connects to multiple databases.  Everything worked just fine until I tried to add Spring Transaction Management to one of the database connections.  </p>

<p>Ever since I added a @Transactional annotation to the service method, the database connection will work correctly for the first managed transaction.  However, after that transaction completes, it looks like the actual database connection closes but the logical connection object thinks the connection is still open.  The result is that any subsequent database calls using that connection fail with a "Closed Connection" error.</p>

<p>To put it another way, these scenarios all work:</p>

<ul>
<li>Any sequence of read or write operations without @Transactional</li>
<li>The @Transactional flow, the first time</li>
<li>The exact same flow except with the @Transactional attribute removed, repeated any number of times</li>
</ul>

<p>The problem only occurs after the application runs a method with the @Transactional annotation.  It does not matter if the transaction commits successfully or rolls back.</p>

<p>See below for detailed classes and logs and stuff.  They have been anonymized for SO, so any typos are just typos.</p>

<p>The first time I hit the database, everything works perfectly.  This is what I see in the logs:</p>

<pre><code>DEBUG 26 Sep 2016 13:15:00,659 () interceptor.RestServiceLoggingInterceptor preHandle 48 - Received request at path /service/trial/tmr/submit:
DEBUG 26 Sep 2016 13:15:01,259 () datasource.DataSourceTransactionManager getTransaction 367 - Creating new transaction with name [com.example.service.shipping.createShipment]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; 'shippingTxManager',-java.lang.Exception
DEBUG 26 Sep 2016 13:15:01,260 () datasource.DataSourceTransactionManager doBegin 206 - Acquired Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=94, lastUsedAgoInSec=94, creationTimeAgoInSec=94}] for JDBC transaction
DEBUG 26 Sep 2016 13:15:01,265 () datasource.DataSourceTransactionManager doBegin 223 - Switching JDBC Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=94, lastUsedAgoInSec=94, creationTimeAgoInSec=94}] to manual commit
DEBUG 26 Sep 2016 13:15:01,268 () transaction.SpringManagedTransaction openConnection 86 - JDBC Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=94, lastUsedAgoInSec=94, creationTimeAgoInSec=94}] will be managed by Spring
&lt;snip a bunch of select, insert, and update statements&gt;
DEBUG 26 Sep 2016 13:15:02,223 () datasource.DataSourceTransactionManager processCommit 759 - Initiating transaction commit
DEBUG 26 Sep 2016 13:15:02,223 () datasource.DataSourceTransactionManager doCommit 269 - Committing JDBC transaction on Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=95, lastUsedAgoInSec=95, creationTimeAgoInSec=95}]
DEBUG 26 Sep 2016 13:15:02,271 () datasource.DataSourceTransactionManager doCleanupAfterCompletion 327 - Releasing JDBC Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=95, lastUsedAgoInSec=95, creationTimeAgoInSec=95}] after transaction
DEBUG 26 Sep 2016 13:15:02,271 () datasource.DataSourceUtils doReleaseConnection 327 - Returning JDBC Connection to DataSource
</code></pre>

<p>This is what I see in the logs on subsequent tries:</p>

<pre><code>DEBUG 26 Sep 2016 13:23:38,235 () datasource.DataSourceTransactionManager getTransaction 367 - Creating new transaction with name [com.example.service.shipping.createShipment]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT; 'shippingTxManager',-java.lang.Exception
DEBUG 26 Sep 2016 13:23:38,236 () datasource.DataSourceTransactionManager doBegin 206 - Acquired Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=1, lastUsedAgoInSec=602, creationTimeAgoInSec=612}] for JDBC transaction
DEBUG 26 Sep 2016 13:23:38,236 () datasource.DataSourceTransactionManager doBegin 223 - Switching JDBC Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=1, lastUsedAgoInSec=602, creationTimeAgoInSec=612}] to manual commit
DEBUG 26 Sep 2016 13:23:38,236 () ShippingMapper.getShipmentInfo debug 142 - ==&gt;  Preparing: select * from shipments where shipment_id = ?
DEBUG 26 Sep 2016 13:23:38,271 () datasource.DataSourceTransactionManager processRollback 851 - Initiating transaction rollback
DEBUG 26 Sep 2016 13:23:38,272 () datasource.DataSourceTransactionManager doRollback 284 - Rolling back JDBC transaction on Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=1, lastUsedAgoInSec=602, creationTimeAgoInSec=612}]
DEBUG 26 Sep 2016 13:23:38,316 () datasource.DataSourceTransactionManager doCleanupAfterCompletion 327 - Releasing JDBC Connection [ConnectionHandle{url=jdbc:oracle:thin:@//example.com:1535/SCHEMA, user=user, debugHandle=null, lastResetAgoInSec=1, lastUsedAgoInSec=602, creationTimeAgoInSec=612}] after transaction
DEBUG 26 Sep 2016 13:23:38,317 () datasource.DataSourceUtils doReleaseConnection 327 - Returning JDBC Connection to DataSource
ERROR 26 Sep 2016 13:23:38,318 () impl.IntegrationServiceImpl submitTmr 235 - error:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: Connection is closed!
</code></pre>

<p>Here are the relevant classes.  I have omitted some things including controllers, model classes, mybatis mappers, and configurations for other databases.</p>

<p>Configuration</p>

<pre><code>@Configuration
@EnableTransactionManagement
public class AppConfig {

    @Bean(name = "shippingDataSource")
    public DataSource shippingDataSource() {
        BoneCPDataSource dataSource = new BoneCPDataSource();
        dataSource.setDriverClass("oracle.jdbc.OracleDriver");
        dataSource.setJdbcUrl("jdbc:oracle:thin:@//example.com:1535/SCHEMA");
        dataSource.setUsername("user");
        dataSource.setPassword("pass");
        return dataSource;
    }

    @Bean(name = "shippingTxManager")
    public PlatformTransactionManager TxManager(){
        return new DataSourceTransactionManager(shippingDataSource());
    }

    @Bean(name = "shippingSqlSessionFactoryBean")
    public SqlSessionFactoryBean shippingSqlSessionFactoryBean() {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(shippingDataSource());
        bean.setTypeAliasesPackage("com.example.shipping.model");
        bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
        return bean;
    }

    @Bean(name = "shippingSqlSession")
    public SqlSession shippingSqlSession() throws Exception {
        return shippingSqlSessionFactoryBean().getObject().openSession();
    }
}
</code></pre>

<p>Service Layer</p>

<pre><code>@Service("shippingService")
// Remove the "Transactional" annotation and everything else works.
@Transactional(rollbackFor=ShippingException.class, transactionManager = "shippingTxManager")
public class ShippingService {

    @Autowired 
    ShippingDao dao;

    // If any packages are not inserted successfully, throw an exception
    // and roll back the whole shipment.
    public void CreateShipment(Shipment shipment){
        try {
            dao.insertShipment(shipment);
            for(Package package : shipment.getPackages()) {
                dao.insertPackage(package);
            }
        } catch (Exception e) {
            throw new ShippingException(e);
        }
    }
}
</code></pre>

<p>Data Layer</p>

<pre><code>@Repository
public class ShippingDao {

    @Autowired
    @Qualifier("shippingSqlSession")
    private SqlSession session;

    public int insertShipment(Shipment shipment){
        return session.insert("insertShipment", shipment);
    }

    public int insertPackage(Package package){
        return session.insert("insertPackage", package);
    }
}
</code></pre>

<p>pom.xml</p>

<pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;

    &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
    &lt;artifactId&gt;example&lt;/artifactId&gt;
    &lt;groupId&gt;com.example&lt;/groupId&gt;
    &lt;version&gt;0.0.1-SNAPSHOT&lt;/version&gt;

    &lt;dependencies&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.springframework&lt;/groupId&gt;
            &lt;artifactId&gt;spring-core&lt;/artifactId&gt;
            &lt;version&gt;${spring.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.springframework&lt;/groupId&gt;
            &lt;artifactId&gt;spring-webmvc&lt;/artifactId&gt;
            &lt;version&gt;${spring.version}&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.springframework.ws&lt;/groupId&gt;
            &lt;artifactId&gt;spring-ws-core&lt;/artifactId&gt;
            &lt;version&gt;2.2.4.RELEASE&lt;/version&gt;
            &lt;exclusions&gt;
                &lt;exclusion&gt;
                    &lt;groupId&gt;org.springframework&lt;/groupId&gt;
                    &lt;artifactId&gt;spring-aop&lt;/artifactId&gt;
                &lt;/exclusion&gt;
            &lt;/exclusions&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.springframework&lt;/groupId&gt;
            &lt;artifactId&gt;spring-jdbc&lt;/artifactId&gt;
            &lt;version&gt;4.2.4.RELEASE&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.mybatis&lt;/groupId&gt;
            &lt;artifactId&gt;mybatis&lt;/artifactId&gt;
            &lt;version&gt;3.3.0&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;org.mybatis&lt;/groupId&gt;
            &lt;artifactId&gt;mybatis-spring&lt;/artifactId&gt;
            &lt;version&gt;1.2.2&lt;/version&gt;
        &lt;/dependency&gt;
        &lt;dependency&gt;
            &lt;groupId&gt;com.jolbox&lt;/groupId&gt;
            &lt;artifactId&gt;bonecp&lt;/artifactId&gt;
            &lt;version&gt;0.8.0.RELEASE&lt;/version&gt;
        &lt;/dependency&gt;
    &lt;/dependencies&gt;
&lt;/project&gt;
</code></pre>