Atomikos: data not getting saved when using PostgreSQL

Question

I have encountered a strange issue while using Atomikos.

I have a small test application (Spring + Hibernate). It uses two different data sources which I need to test JTA functionality on a non Java EE container (Tomcat in my case).

When I use MySQL as a database everything gets saved without a problem. But when I switch to PostgreSQL data are not getting saved to the database.

What is interesting that IF I'm not using @Transactional and manually begin and commit transactions - everything works fine. But when using @Transactional - data are not getting saved. I can see that hibernate_sequence table DOES gets updated in the database (numbers are increased), and only the data itself is not. And all this despite the fact that I see in Atomikos logs that data was committed etc. Again the SAME code works fine with MySQL. This issue occurs only when using PostgreSQL.

I have tested it both on PostgreSQL version 9.4 (64bit on Linux) and 9.5 (64bit on Windows 10) and using different PostgreSQL JDBC driver versions (both JDBC4 and JDBC3).

Atomikos versions tested: 4.0.4 (latest as of now) and 3.9.3.
Spring version: 4.0.9.
Hibernate version: 3.6.10.Final.

NOTE
I need those exact Spring and Hibernate versions to emulate the working application I need Atomikos to integrate to.

---

Configuration and code samples

Here is my spring.xml configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
                        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
                        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

    <tx:annotation-driven />
    <tx:jta-transaction-manager />

    <context:component-scan base-package="com.byteslounge.spring.tx.service.impl" />
    <context:component-scan base-package="com.byteslounge.spring.tx.servlet" />
    <context:component-scan base-package="com.byteslounge.spring.tx.entity" />

    <bean id="sessionFactory1" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="annotatedClasses">
            <list>
                <value>com.byteslounge.spring.tx.entity.TableOne</value>
            </list>
        </property>
        <property name="dataSource" ref="dataSource1" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.connection.autocommit">false</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>

                <prop key="hibernate.current_session_context_class">jta</prop>
                <prop key="javax.persistence.transactionType">jta</prop>
                <prop key="hibernate.transaction.factory_class">com.atomikos.icatch.jta.hibernate3.AtomikosJTATransactionFactory </prop>
                <prop key="hibernate.transaction.manager_lookup_class">com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup</prop>
            </props>
        </property>
    </bean>
    <bean id="dataSource1" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
        <property name="uniqueResourceName" value="DataSource1" />
        <property name="xaDataSource" ref="dataBase1" />
        <property name="maxPoolSize" value="20" />
        <property name="minPoolSize" value="10"/>
    </bean>
    <bean id="dataBase1" class="org.postgresql.xa.PGXADataSource" lazy-init="true">
        <property name="user" value="tester1" />
        <property name="password" value="123456" />
        <property name="serverName" value="localhost" />
        <property name="portNumber" value="5432" />
        <property name="databaseName" value="test_db1" />
        <property name="url" value="jdbc:postgresql://localhost:5432/test_db1" />
    </bean>


    <bean id="sessionFactory2" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="annotatedClasses">
            <list>
                <value>com.byteslounge.spring.tx.entity.TableTwo</value>
            </list>
        </property>
        <property name="dataSource" ref="dataSource2" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.connection.autocommit">false</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>

                <prop key="hibernate.current_session_context_class">jta</prop>
                <prop key="javax.persistence.transactionType">jta</prop>
                <prop key="hibernate.transaction.factory_class">com.atomikos.icatch.jta.hibernate3.AtomikosJTATransactionFactory </prop>
                <prop key="hibernate.transaction.manager_lookup_class">com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup</prop>
            </props>
        </property>
    </bean>
    <bean id="dataSource2" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
        <property name="uniqueResourceName" value="DataSource2" />
        <property name="xaDataSource" ref="dataBase2" />
        <property name="maxPoolSize" value="20" />
        <property name="minPoolSize" value="10"/>
    </bean>
    <bean id="dataBase2" class="org.postgresql.xa.PGXADataSource" lazy-init="true">
        <property name="user" value="tester2" />
        <property name="password" value="123456" />
        <property name="serverName" value="localhost" />
        <property name="portNumber" value="5432" />
        <property name="databaseName" value="test_db2" />
        <property name="url" value="jdbc:postgresql://localhost:5432/test_db2" />
    </bean>

    <!-- Atomikos -->
    <bean id="atomikosTransactionManager" class="com.atomikos.icatch.jta.UserTransactionManager" init-method="init" destroy-method="close">
        <property name="forceShutdown" value="false" />
    </bean>
    <bean id="atomikosUserTransaction" class="com.atomikos.icatch.jta.UserTransactionImp">
        <property name="transactionTimeout" value="3000" />
    </bean>
    <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager" depends-on="atomikosTransactionManager,atomikosUserTransaction">
        <property name="transactionManager" ref="atomikosTransactionManager" />
        <property name="userTransaction" ref="atomikosUserTransaction" />
        <property name="allowCustomIsolationLevels" value="true" />
    </bean>


    <!-- DAO -->
    <bean id="tableOneDao" class="com.byteslounge.spring.tx.dao.impl.TableOneDaoImpl">
        <property name="sessionFactory" ref="sessionFactory1" />
    </bean>
    <bean id="tableTwoDao" class="com.byteslounge.spring.tx.dao.impl.TableTwoDaoImpl">
        <property name="sessionFactory" ref="sessionFactory2" />
    </bean>

</beans>

Here is a code sample to illustrate how data is getting saved:

@Transactional(rollbackFor=Exception.class)
public void persist(TableOne tableOne, TableTwo tableTwo) throws Exception {
    tableOneDao.save(tableOne);
    tableTwoDao.save(tableTwo);
}

And the DAO classes have the same logic for saving:

@Service
public class TableOneDaoImpl extends HibernateDaoSupport implements TableOneDao  {

    @Override
    public void save(TableOne tableOne) throws Exception {
        Session session = null;
        try {
            session = getSessionFactory().openSession();

            session.save(tableOne);
        } catch (Exception e) {
            throw new Exception("Could not save tableOne!", e);
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }

}

And hear are the logs I get when executing data saving logic:

13:48:58.521 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.imp.CompositeTransactionManagerImp - createCompositeTransaction ( 10000 ): created new ROOT transaction with id 192.168.50.67.tm147946973850200001
13:48:58.605 [http-bio-8080-exec-3] DEBUG org.hibernate.impl.SessionImpl - opened session at timestamp: 14794697385
13:48:58.610 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.imp.CompositeTransactionImp - registerSynchronization ( com.atomikos.icatch.jta.Sync2Sync@57e48ad3 ) for transaction 192.168.50.67.tm147946973850200001
13:48:58.610 [http-bio-8080-exec-3] DEBUG org.hibernate.jdbc.JDBCContext - successfully registered Synchronization
13:48:58.612 [http-bio-8080-exec-3] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
13:48:58.612 [http-bio-8080-exec-3] DEBUG org.hibernate.jdbc.ConnectionManager - opening JDBC connection
13:48:58.612 [http-bio-8080-exec-3] DEBUG com.atomikos.jdbc.AbstractDataSourceBean - AtomikosDataSoureBean 'DataSource1': getConnection()...
13:48:58.612 [http-bio-8080-exec-3] INFO  com.atomikos.jdbc.AbstractDataSourceBean - AtomikosDataSoureBean 'DataSource1': init...
13:48:58.613 [http-bio-8080-exec-3] DEBUG org.hibernate.SQL - select nextval ('hibernate_sequence')
Hibernate: select nextval ('hibernate_sequence')
13:48:58.616 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.imp.CompositeTransactionImp - addParticipant ( XAResourceTransaction: 3139322E3136382E35302E36372E746D313437393436393733383530323030303031:3139322E3136382E35302E36372E746D31 ) for transaction 192.168.50.67.tm147946973850200001
13:48:58.616 [http-bio-8080-exec-3] DEBUG com.atomikos.datasource.xa.XAResourceTransaction - XAResource.start ( 3139322E3136382E35302E36372E746D313437393436393733383530323030303031:3139322E3136382E35302E36372E746D31 , XAResource.TMNOFLAGS ) on resource DataSource1 represented by XAResource instance org.postgresql.xa.PGXAConnection@7b57a36a
13:48:58.617 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.imp.CompositeTransactionImp - registerSynchronization ( com.atomikos.jdbc.AtomikosConnectionProxy$JdbcRequeueSynchronization@ede1e016 ) for transaction 192.168.50.67.tm147946973850200001
13:48:58.617 [http-bio-8080-exec-3] DEBUG com.atomikos.jdbc.AtomikosConnectionProxy - atomikos connection proxy for Pooled connection wrapping physical connection org.postgresql.jdbc3g.Jdbc3gConnection@2a39bdf5: calling prepareStatement(select nextval ('hibernate_sequence'))...
13:48:58.627 [http-bio-8080-exec-3] DEBUG org.hibernate.id.SequenceGenerator - Sequence identifier generated: BasicHolder[java.lang.Integer[43]]
13:48:58.627 [http-bio-8080-exec-3] DEBUG org.hibernate.jdbc.AbstractBatcher - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
13:48:58.627 [http-bio-8080-exec-3] DEBUG org.hibernate.jdbc.ConnectionManager - aggressively releasing JDBC connection
13:48:58.627 [http-bio-8080-exec-3] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
13:48:58.627 [http-bio-8080-exec-3] DEBUG com.atomikos.jdbc.AtomikosConnectionProxy - atomikos connection proxy for Pooled connection wrapping physical connection org.postgresql.jdbc3g.Jdbc3gConnection@2a39bdf5: isClosed()...
13:48:58.627 [http-bio-8080-exec-3] DEBUG com.atomikos.jdbc.AtomikosConnectionProxy - atomikos connection proxy for Pooled connection wrapping physical connection org.postgresql.jdbc3g.Jdbc3gConnection@2a39bdf5: calling getWarnings...
13:48:58.627 [http-bio-8080-exec-3] DEBUG com.atomikos.jdbc.AtomikosConnectionProxy - atomikos connection proxy for Pooled connection wrapping physical connection org.postgresql.jdbc3g.Jdbc3gConnection@2a39bdf5: calling clearWarnings...
13:48:58.627 [http-bio-8080-exec-3] DEBUG com.atomikos.jdbc.AtomikosConnectionProxy - atomikos connection proxy for Pooled connection wrapping physical connection org.postgresql.jdbc3g.Jdbc3gConnection@2a39bdf5: close()...
13:48:58.627 [http-bio-8080-exec-3] DEBUG com.atomikos.datasource.xa.XAResourceTransaction - XAResource.end ( 3139322E3136382E35302E36372E746D313437393436393733383530323030303031:3139322E3136382E35302E36372E746D31 , XAResource.TMSUCCESS ) on resource DataSource1 represented by XAResource instance org.postgresql.xa.PGXAConnection@7b57a36a
13:48:58.628 [http-bio-8080-exec-3] DEBUG org.hibernate.event.def.AbstractSaveEventListener - generated identifier: 43, using strategy: org.hibernate.id.SequenceGenerator
13:48:58.638 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.jta.Sync2Sync - beforeCompletion() called on Synchronization: org.hibernate.transaction.synchronization.HibernateSynchronizationImpl@3aa81ddb
13:48:58.638 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.imp.CompositeTransactionImp - commit() done (by application) of transaction 192.168.50.67.tm147946973850200001
13:48:58.641 [http-bio-8080-exec-3] DEBUG com.atomikos.datasource.xa.XAResourceTransaction - XAResource.commit ( 3139322E3136382E35302E36372E746D313437393436393733383530323030303031:3139322E3136382E35302E36372E746D31 , true ) on resource DataSource1 represented by XAResource instance org.postgresql.xa.PGXAConnection@7b57a36a
13:48:58.643 [http-bio-8080-exec-3] DEBUG com.atomikos.icatch.jta.Sync2Sync - afterCompletion ( STATUS_COMMITTED ) called  on Synchronization: org.hibernate.transaction.synchronization.HibernateSynchronizationImpl@3aa81ddb

I have also set the max_prepared_transactions parameter in the postgresql.conf to be at least as large as the max_connections as it was recommended on the related Atomikos documentation page for PostgreSQL.

---

Again I want to emphasize that the same code works fine with MySQL. Only when configuring this web application to use PostgreSQL this strange issue occurs.

Anyone knows what might be a problem?

Answer 1

Solution

OK, mystery is solved! It turned out that I needed explicitly call flush() on the session object.

The following answer gave me a hint:
Hibernate not saving Object in the Database?

---

Example

Here is a working example (pay attention to session.flush() which must be called before closing a session):

@Service
public class TableOneDaoImpl extends HibernateDaoSupport implements TableOneDao  {

    @Override
    public void save(TableOne tableOne) throws Exception {
        Session session = null;
        try {
            session = getSessionFactory().openSession();

            session.save(tableOne);
            session.flush();
        } catch (Exception e) {
            throw new Exception("Could not save tableOne!", e);
        } finally {
            if (session != null) {
                session.close();
            }
        }
    }

}

And again, when using MySQL it works just fine without explicit flushing.

---

Interesting observation

The above mentioned test web application used pure Hibernate configuration and Hibernate's SessionFactory (i.e. without persistence.xml and JPA entityManager). But I also have the same test application configured to use JPA. And in that application everything also works without explicit flushing both with MySQL and PostgreSQL.