JTA-Transactions - Mismatch between Spring and Hibernate connection release?

Question

Versions in use: Spring 4.1.6.RELEASE, Hibernate 4.3.10.Final, Atomikos 3.9.26

We are in the process of upgrading our main webapp to Hibernate 4. We mainly use HibernateTemplate and JdbcTemplate for access to multiple databases (DB2 and Oracle) with Atomikos as JTA-TransactionManager.

The problem: While using only HibernateTemplate or only JdbcTemplates in a single transaction works fine, using JdbcTemplate and HibernateTemplate together in one transaction causes StaleStateExceptions in certain cases.

Here is an example where the problem occurs - the code is wrapped in a TransactionalProxyFactoryBean with PROPAGATION_REQUIRED:

public class MyServiceImpl extends HibernateDaoSupport implements MyService {

  private static final Log log = LogFactory.getLog(MyServiceImpl.class);

  private JdbcTemplate jdbcTemplate;

  @Override
  public void execute() {
    // save new entity instance with HibernateTemplate
    MyEntity e = new MyEntity();
    e.setMyProperty("first value");
    getHibernateTemplate().save(e);

    // use JdbcTemplate to access DB
    String sql = "select * from my_table";
    getJdbcTemplate().query(sql, new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet rs) throws SQLException {
             // process rows
            }
         });

     // update entity instance with HibernateTemplate
     e.setMyProperty("second value");
     getHibernateTemplate().saveOrUpdate(e);

     // make sure the flush occurs immediately. This is needed in to demonstrate the problem. (Otherwise the property UPDATE would be cached and issued on commit, just after Spring closed the connection used for the JdbcTemplate and the problem would not show)
     getHibernateTemplate().flush();
  }

  public JdbcTemplate getJdbcTemplate() {
    return jdbcTemplate;
  }

  public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }
}

Our conclusions: The exception is basically caused by different ways HibernateTemplate and JdbcTemplate accquire and release the database connection.

The HibernateTemplate directly delegates to Hibernate which uses the connection release mode AFTER_STATEMENT (set by Spring if a JtaTransactionManager is provided). This causes Hibernate to get a connection from the Atomikos connection pool, perform the SQL and close its connection which doesn't close the physical connection but returns it to the connection pool.

The JdbcTemplate uses Spring's DataSourceUtils.getConnection(...) to get a connection from the Atomikos connection pool, performs the SQL and calls DataSourceUtils.releaseConnection(...) which itself doesn't call Connection.close(). The connection isn't closed by Spring in DataSourceUtils.releaseConnection(...) (and in consequence not returned to the connection pool) but bound to the thread for reuse in DataSourceUtils.getConnection(...).

So it seems as if in a JTA context, Spring teaches Hibernate to use connection release mode AFTER_STATEMENT (which is also recommeded by Hibernate for JTA) but behaves totally different in it's DataSourceUtils.

In detail, we tracked down the cause like following:

1. The StaleStateException is thrown because the UPDATE-Statement for setting "second value" at the entity does not affect any row in the database.
2. This is because the UPDATE-Statement happens on another connection than the INSERT-Statement.
3. This is because the original connection used by the INSERT-Statement is still considered in use by the connection pool.
4. This is because close() is never called on the first connection after it was used for the JdbcTemplate.
5. This is because DataSourceUtils.releaseConnection(...) which is called by the JdbcTemplate when finished doesn't call Connection.close() in a JTA-Transaction-Context.

Things we tried and failed at:

1. Make Hibernate use AFTER_TRANSACTION or ON_CLOSE as connection release mode - prevented by Spring as SpringJtaSessionContext with it's AFTER_STATEMENT is hardcoded.
2. Configure Spring close the DB connection on connection release.

What are we doing wrong?
Any configuration we forgot?
Is it a Spring/Hibernate problem at all or should the Atomikos connection pool behave differently by not waiting for a call to Connection.close() before making the connection available again?

Thanks a lot for your help!

Spring context for Hibernate and JTA configuration:

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">

    <property name="dataSource">
        <ref bean="dataSource" />
    </property>

    <property name="jtaTransactionManager" ref="transactionManager" />

    <property name="hibernateProperties">
        <props>
            <!-- Stripped down configuration for the toy project to reproduce the problem -->
            <prop key="hibernate.cache.use_second_level_cache">false</prop>
            <prop key="hibernate.dialect">com.company.DB2Dialect</prop>

            <!-- hibernate.transaction.factory_class and hibernate.transaction.jta.platform are implicitly set by setting the jtaTransactionManager property -->

            <!-- Properties wie normally use in production
            <prop key="hibernate.dialect">com.company.DB2Dialect</prop>
            <prop key="hibernate.cache.use_query_cache">false</prop>
            <prop key="hibernate.cache.use_second_level_cache">true</prop>
            <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>
            <prop key="hibernate.order_inserts">true</prop>
            <prop key="hibernate.order_updates">true</prop>
            <prop key="hibernate.generate_statistics">false</prop>
            <prop key="hibernate.use_outer_join">true</prop>
            <prop key="hibernate.jdbc.batch_versioned_data">true</prop>
            <prop key="hibernate.bytecode.use_reflection_optimizer">true</prop>
            <prop key="hibernate.jdbc.batch_size">100</prop> -->
        </props>
    </property>

    <property name="mappingLocations">
        <list>
            <value>classpath*:**/*.hbm.xml</value>
        </list>
    </property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.lookup.IsolationLevelDataSourceRouter"
    scope="singleton">
    <property name="targetDataSources">
        <map>
            <entry key="ISOLATION_REPEATABLE_READ" value="java:comp/env/jdbc/wawi_rr" />
            <entry key="ISOLATION_READ_UNCOMMITTED" value="java:comp/env/jdbc/wawi_ru" />
            <entry key="ISOLATION_READ_COMMITTED" value="java:comp/env/jdbc/wawi_rc" />
            <entry key="ISOLATION_SERIALIZABLE" value="java:comp/env/jdbc/wawi_s" />
        </map>
    </property>
    <property name="defaultTargetDataSource" value="java:comp/env/jdbc/wawi" />
</bean>


<bean id="transactionManager"
    class="org.springframework.transaction.jta.JtaTransactionManager">
    <property name="transactionManagerName">
        <value>java:comp/env/TransactionManager</value>
    </property>
    <property name="allowCustomIsolationLevels">
        <value>true</value>
    </property>
</bean>

Spring context for Service configuration:

<bean id="myService" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="target">
        <ref bean="myServiceTarget" />
    </property>
    <property name="transactionManager">
        <ref bean="transactionManager" />
    </property>
    <property name="transactionAttributes">
        <props>
            <prop key="*">PROPAGATION_REQUIRED,ISOLATION_DEFAULT</prop>
        </props>
    </property>
</bean>

<bean id="myServiceTarget" class="org.example.MyServiceImpl">
    <property name="sessionFactory" ref="sessionFactory" />
    <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

<bean id="myMBean" class="org.example.MyMBean">
    <property name="myService" ref="myService" />
</bean>

Stacktrace:

org.springframework.orm.hibernate4.HibernateOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
    at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:205)
    at org.springframework.orm.hibernate4.HibernateTemplate.doExecute(HibernateTemplate.java:343)
    at org.springframework.orm.hibernate4.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:308)
    at org.springframework.orm.hibernate4.HibernateTemplate.flush(HibernateTemplate.java:837)
    at org.example.MyServiceImpl.execute(MyServiceImpl.java:45)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy13.execute(Unknown Source)
    at org.example.MyMBean.execute(MyMBean.java:13)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:75)
    at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:279)
    at javax.management.modelmbean.RequiredModelMBean$4.run(RequiredModelMBean.java:1245)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at javax.management.modelmbean.RequiredModelMBean.invokeMethod(RequiredModelMBean.java:1239)
    at javax.management.modelmbean.RequiredModelMBean.invoke(RequiredModelMBean.java:1077)
    at org.springframework.jmx.export.SpringModelMBean.invoke(SpringModelMBean.java:90)
    at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.invoke(DefaultMBeanServerInterceptor.java:819)
    at com.sun.jmx.mbeanserver.JmxMBeanServer.invoke(JmxMBeanServer.java:801)
    at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1487)
    at javax.management.remote.rmi.RMIConnectionImpl.access$300(RMIConnectionImpl.java:97)
    at javax.management.remote.rmi.RMIConnectionImpl$PrivilegedOperation.run(RMIConnectionImpl.java:1328)
    at javax.management.remote.rmi.RMIConnectionImpl.doPrivilegedOperation(RMIConnectionImpl.java:1420)
    at javax.management.remote.rmi.RMIConnectionImpl.invoke(RMIConnectionImpl.java:848)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:322)
    at sun.rmi.transport.Transport$1.run(Transport.java:177)
    at sun.rmi.transport.Transport$1.run(Transport.java:174)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.Transport.serviceCall(Transport.java:173)
    at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:556)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:811)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:670)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:724)
Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
    at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:81)
    at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:73)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:63)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3281)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3183)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3525)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:159)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:465)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:351)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1258)
    at org.springframework.orm.hibernate4.HibernateTemplate$27.doInHibernate(HibernateTemplate.java:840)
    at org.springframework.orm.hibernate4.HibernateTemplate.doExecute(HibernateTemplate.java:340)
    ... 54 more