title: Atomikos: data not getting saved when using PostgreSQL
body: <p>I have encountered a strange issue while using <a href="https://www.atomikos.com/" rel="nofollow noreferrer">Atomikos</a>.</p>
  
  <p>I have a small test application (Spring + Hibernate). It uses two different data sources which I need to test <a href="http://www.oracle.com/technetwork/java/javaee/jta/index.html" rel="nofollow noreferrer">JTA</a> functionality on a non Java EE container (Tomcat in my case). </p>
  
  <p>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.</p>
  
  <p>What is interesting that IF I'm not using <code>@Transactional</code> and manually begin and commit transactions - everything works fine. But when using <code>@Transactional</code> - data are not getting saved. I can see that <em>hibernate_sequence</em> 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.</p>
  
  <p>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 <a href="https://jdbc.postgresql.org/download.html" rel="nofollow noreferrer">driver versions</a> (both JDBC4 and JDBC3).</p>
  
  <p>Atomikos versions tested: <em>4.0.4</em> (latest as of now) and <em>3.9.3</em>.<br>
  Spring version: <em>4.0.9</em>.<br>
  Hibernate version: <em>3.6.10.Final</em>.</p>
  
  <p>NOTE<br>
  I need those exact  Spring and Hibernate versions to emulate the working application I need Atomikos to integrate to.</p>
  
  <hr>
  
  <h3>Configuration and code samples</h3>
  
  <p>Here is my <em>spring.xml</em> configuration file:</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
  
      &lt;tx:annotation-driven /&gt;
      &lt;tx:jta-transaction-manager /&gt;
  
      &lt;context:component-scan base-package="com.byteslounge.spring.tx.service.impl" /&gt;
      &lt;context:component-scan base-package="com.byteslounge.spring.tx.servlet" /&gt;
      &lt;context:component-scan base-package="com.byteslounge.spring.tx.entity" /&gt;
  
      &lt;bean id="sessionFactory1" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"&gt;
          &lt;property name="annotatedClasses"&gt;
              &lt;list&gt;
                  &lt;value&gt;com.byteslounge.spring.tx.entity.TableOne&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="dataSource" ref="dataSource1" /&gt;
          &lt;property name="hibernateProperties"&gt;
              &lt;props&gt;
                  &lt;prop key="hibernate.connection.autocommit"&gt;false&lt;/prop&gt;
                  &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.PostgreSQLDialect&lt;/prop&gt;
                  &lt;prop key="hibernate.show_sql"&gt;true&lt;/prop&gt;
                  &lt;prop key="hibernate.hbm2ddl.auto"&gt;update&lt;/prop&gt;
  
                  &lt;prop key="hibernate.current_session_context_class"&gt;jta&lt;/prop&gt;
                  &lt;prop key="javax.persistence.transactionType"&gt;jta&lt;/prop&gt;
                  &lt;prop key="hibernate.transaction.factory_class"&gt;com.atomikos.icatch.jta.hibernate3.AtomikosJTATransactionFactory &lt;/prop&gt;
                  &lt;prop key="hibernate.transaction.manager_lookup_class"&gt;com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
      &lt;bean id="dataSource1" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close"&gt;
          &lt;property name="uniqueResourceName" value="DataSource1" /&gt;
          &lt;property name="xaDataSource" ref="dataBase1" /&gt;
          &lt;property name="maxPoolSize" value="20" /&gt;
          &lt;property name="minPoolSize" value="10"/&gt;
      &lt;/bean&gt;
      &lt;bean id="dataBase1" class="org.postgresql.xa.PGXADataSource" lazy-init="true"&gt;
          &lt;property name="user" value="tester1" /&gt;
          &lt;property name="password" value="123456" /&gt;
          &lt;property name="serverName" value="localhost" /&gt;
          &lt;property name="portNumber" value="5432" /&gt;
          &lt;property name="databaseName" value="test_db1" /&gt;
          &lt;property name="url" value="jdbc:postgresql://localhost:5432/test_db1" /&gt;
      &lt;/bean&gt;
  
  
      &lt;bean id="sessionFactory2" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"&gt;
          &lt;property name="annotatedClasses"&gt;
              &lt;list&gt;
                  &lt;value&gt;com.byteslounge.spring.tx.entity.TableTwo&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
          &lt;property name="dataSource" ref="dataSource2" /&gt;
          &lt;property name="hibernateProperties"&gt;
              &lt;props&gt;
                  &lt;prop key="hibernate.connection.autocommit"&gt;false&lt;/prop&gt;
                  &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.PostgreSQLDialect&lt;/prop&gt;
                  &lt;prop key="hibernate.show_sql"&gt;true&lt;/prop&gt;
                  &lt;prop key="hibernate.hbm2ddl.auto"&gt;update&lt;/prop&gt;
  
                  &lt;prop key="hibernate.current_session_context_class"&gt;jta&lt;/prop&gt;
                  &lt;prop key="javax.persistence.transactionType"&gt;jta&lt;/prop&gt;
                  &lt;prop key="hibernate.transaction.factory_class"&gt;com.atomikos.icatch.jta.hibernate3.AtomikosJTATransactionFactory &lt;/prop&gt;
                  &lt;prop key="hibernate.transaction.manager_lookup_class"&gt;com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup&lt;/prop&gt;
              &lt;/props&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
      &lt;bean id="dataSource2" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close"&gt;
          &lt;property name="uniqueResourceName" value="DataSource2" /&gt;
          &lt;property name="xaDataSource" ref="dataBase2" /&gt;
          &lt;property name="maxPoolSize" value="20" /&gt;
          &lt;property name="minPoolSize" value="10"/&gt;
      &lt;/bean&gt;
      &lt;bean id="dataBase2" class="org.postgresql.xa.PGXADataSource" lazy-init="true"&gt;
          &lt;property name="user" value="tester2" /&gt;
          &lt;property name="password" value="123456" /&gt;
          &lt;property name="serverName" value="localhost" /&gt;
          &lt;property name="portNumber" value="5432" /&gt;
          &lt;property name="databaseName" value="test_db2" /&gt;
          &lt;property name="url" value="jdbc:postgresql://localhost:5432/test_db2" /&gt;
      &lt;/bean&gt;
  
      &lt;!-- Atomikos --&gt;
      &lt;bean id="atomikosTransactionManager" class="com.atomikos.icatch.jta.UserTransactionManager" init-method="init" destroy-method="close"&gt;
          &lt;property name="forceShutdown" value="false" /&gt;
      &lt;/bean&gt;
      &lt;bean id="atomikosUserTransaction" class="com.atomikos.icatch.jta.UserTransactionImp"&gt;
          &lt;property name="transactionTimeout" value="3000" /&gt;
      &lt;/bean&gt;
      &lt;bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager" depends-on="atomikosTransactionManager,atomikosUserTransaction"&gt;
          &lt;property name="transactionManager" ref="atomikosTransactionManager" /&gt;
          &lt;property name="userTransaction" ref="atomikosUserTransaction" /&gt;
          &lt;property name="allowCustomIsolationLevels" value="true" /&gt;
      &lt;/bean&gt;
  
  
      &lt;!-- DAO --&gt;
      &lt;bean id="tableOneDao" class="com.byteslounge.spring.tx.dao.impl.TableOneDaoImpl"&gt;
          &lt;property name="sessionFactory" ref="sessionFactory1" /&gt;
      &lt;/bean&gt;
      &lt;bean id="tableTwoDao" class="com.byteslounge.spring.tx.dao.impl.TableTwoDaoImpl"&gt;
          &lt;property name="sessionFactory" ref="sessionFactory2" /&gt;
      &lt;/bean&gt;
  
  &lt;/beans&gt;
  </code></pre>
  
  <p><br>
  Here is a code sample to illustrate how data is getting saved:</p>
  
  <pre class="lang-java prettyprint-override"><code>@Transactional(rollbackFor=Exception.class)
  public void persist(TableOne tableOne, TableTwo tableTwo) throws Exception {
      tableOneDao.save(tableOne);
      tableTwoDao.save(tableTwo);
  }
  </code></pre>
  
  <p>And the DAO classes have the same logic for saving:</p>
  
  <pre class="lang-java prettyprint-override"><code>@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();
              }
          }
      }
  
  }
  </code></pre>
  
  <p><br>
  And hear are the logs I get when executing data saving logic:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><br>
  I have also set the <a href="https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS" rel="nofollow noreferrer"><em>max_prepared_transactions</em></a> parameter in the <em>postgresql.conf</em> to be at least as large as the <em>max_connections</em> as it was recommended on the related Atomikos <a href="https://www.atomikos.com/Documentation/ConfiguringPostgreSQL" rel="nofollow noreferrer">documentation page</a> for PostgreSQL.</p>
  
  <hr>
  
  <p>Again I want to emphasize that the <strong>same code works fine with MySQL</strong>. Only when configuring this web application to use PostgreSQL this strange issue occurs.</p>
  
  <p>Anyone knows what might be a problem?</p>