Spring data - two different data sources on the same transaction causing abnormality

Question 1

Spring data - two different data sources on the same transaction causing abnormality

spring spring-data transactional atomikos transactionmanager

Urbanleg · Jan 16, 2014 · Viewed 12.3k times · Source

Answer

Answer

<tx:annotation-driven proxy-target-class="true" />

<tx:jta-transaction-manager
    transaction-manager="atomikosTransactionManager" />

<tx:annotation-driven transaction-manager="atomikosTransactionManager"
    proxy-target-class="true" />

<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="300" />
</bean>

<bean id="transactionManager"
    class="org.springframework.transaction.jta.JtaTransactionManager">
    <property name="transactionManager" ref="atomikosTransactionManager" />
    <property name="userTransaction" ref="atomikosUserTransaction" />
    <property name="allowCustomIsolationLevels" value="true" />
</bean>

Your configuration is flawed and contains duplication, which probably, confuses spring. Both the JtaTransactionManager as well as <tx:jta-transaction-manager /> create a JtaTransactionManager.

Next your <tx:annotation-driven > should point to a JtaTransactionManager but nog it points to a UserTransactionManager.

So first fix your configuration:

<tx:annotation-driven proxy-target-class="true" />

<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="300" />
</bean>

<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
    <property name="transactionManager" ref="atomikosTransactionManager" />
    <property name="userTransaction" ref="atomikosUserTransaction" />
    <property name="allowCustomIsolationLevels" value="true" />
</bean>

Assuming you are using a recent Spring 3.2 version instead of injecting the dataSource property inject the jtaDataSource on your LocalContainerEntityManagerFactoryBean, next your jpaProperties and jpaPropertiesMap can be cleaned up.

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="jtaDataSource" ref="xaDataSource" />
    <property name="packagesToScan" value="com.xxx.model" />
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.transaction.manager_lookup_class">com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup</prop>
        </props>
    </property>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect" />
            <property name="showSql" value="false" />
            <property name="generateDdl" value="false" />
        </bean>
    </property>
</bean>

The hibernate.connection properties are useless as you are injecting a Spring managed datasource. Due to the injection of the jtaDataSource the jpaProperty for javax.persistence.transactionType can be removed. The HibernateJpaVendorAdapter already sets the HibernateJpaDialect and you can use the databasePlatform property to specify the dialect.

Regarding your datasources you should be using the Atomikos wrappers for the datasources so that they are aware of Atomikos. For this use the AtomikosDataSourceBean instead of the xa provided datasource for your JDBC driver.

<bean id="xaDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean" init-method="init" destroy-method="close">
    <property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" />
    <property name="uniqueResourceName" value="xaDataSource"/>      
     <property name="xaProperties">
        <props>
            <prop key="url">${mysql_url}</prop>
            <prop key="user">root</prop>
            <prop key="password">xxx</prop>
        </props>
    </property>
</bean>

Finally you are using the BasicDataSource as a reference to hibernate whereas you should use the xa enabled (and wrapped) datasource.

Question 2

We are using atomikos JTA with spring and 2 different mysql datasources.

We got a problem,

When we use two different repositories (of 2 different datasources) on the same @Transactional method we get an error:

Caused by: java.sql.SQLException: Unable to enlist connection the transaction

This happens when the second repository is doing some action against the data source.

We believe it got something to do with the transaction manager (atomikos jta) that might have issues when 2 different data sources are involved in the same transaction.

here is the datasources xml:

    <bean
    class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

<bean id="xaDataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"
    lazy-init="true">
    <property name="pinGlobalTxToPhysicalConnection" value="true" />
    <property name="url" value="${mysql_url}" />
    <property name="user" value="root" />
    <property name="password" value="xxx" />
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.managed.BasicManagedDataSource">
    <property name="initialSize" value="10" />
    <property name="maxActive" value="100" />
    <property name="maxIdle" value="15" />
    <property name="minIdle" value="10" />
    <property name="timeBetweenEvictionRunsMillis" value="10000" />
    <property name="minEvictableIdleTimeMillis" value="60000" />
    <property name="validationQuery" value="/* ping */ SELECT 1" />
    <property name="testOnBorrow" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="xaDataSourceInstance" ref="xaDataSource" />
    <property name="transactionManager" ref="atomikosTransactionManager" />
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="300" />
</bean>

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="packagesToScan" value="com.xxx.model" />
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
        </props>
    </property>
    <property name="dataSource" ref="dataSource" />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="MYSQL" />
            <property name="showSql" value="false" />
            <property name="generateDdl" value="false" />
        </bean>
    </property>
    <property name="jpaPropertyMap">
        <map>
            <entry key="javax.persistence.transactionType" value="JTA" />
            <entry key="hibernate.transaction.manager_lookup_class"
                value="com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup" />
            <entry key="hibernate.connection.autocommit" value="false" />
        </map>
    </property>
</bean>

<!-- shared data source -->

<bean id="shardXaDataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"
    lazy-init="true">
    <property name="pinGlobalTxToPhysicalConnection" value="true" />
    <property name="url" value="${shared_mysql_url}" />
    <property name="user" value="root" />
    <property name="password" value="xxx" />
</bean>

<bean id="shareddDataSource" class="org.apache.commons.dbcp.managed.BasicManagedDataSource">
    <property name="initialSize" value="10" />
    <property name="maxActive" value="100" />
    <property name="maxIdle" value="15" />
    <property name="minIdle" value="10" />
    <property name="timeBetweenEvictionRunsMillis" value="10000" />
    <property name="minEvictableIdleTimeMillis" value="60000" />
    <property name="validationQuery" value="/* ping */ SELECT 1" />
    <property name="testOnBorrow" value="true" />
    <property name="testWhileIdle" value="true" />
    <property name="xaDataSourceInstance" ref="shardXaDataSource" />
    <property name="transactionManager" ref="atomikosTransactionManager" />
    <property name="removeAbandoned" value="true" />
    <property name="removeAbandonedTimeout" value="300" />
</bean>

<bean id="sharedEntityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="packagesToScan" value="com.xxx.shared.model" />
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</prop>
        </props>
    </property>
    <property name="dataSource" ref="shareddDataSource" />
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="MYSQL" />
            <property name="showSql" value="false" />
            <property name="generateDdl" value="false" />
        </bean>
    </property>
    <property name="jpaPropertyMap">
        <map>
            <entry key="javax.persistence.transactionType" value="JTA" />
            <entry key="hibernate.transaction.manager_lookup_class"
                value="com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup" />
            <entry key="hibernate.connection.autocommit" value="false" />
        </map>
    </property>
</bean>

and the transaction.xml :

    <tx:annotation-driven proxy-target-class="true" />

<tx:jta-transaction-manager
    transaction-manager="atomikosTransactionManager" />

<tx:annotation-driven transaction-manager="atomikosTransactionManager"
    proxy-target-class="true" />

<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="300" />
</bean>

<bean id="transactionManager"
    class="org.springframework.transaction.jta.JtaTransactionManager">
    <property name="transactionManager" ref="atomikosTransactionManager" />
    <property name="userTransaction" ref="atomikosUserTransaction" />
    <property name="allowCustomIsolationLevels" value="true" />
</bean>

* EDIT (19.01.2014) *

So according to M. Deinum help we managed to make the error go away, but now there is no actuall transaction taking place (on exception for example there is no roll back)

Here is our new configuration:

data-source.xml:

    <bean id="xaDataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"
    lazy-init="true">
    <property name="pinGlobalTxToPhysicalConnection" value="true" />
    <property name="url" value="${mysql_url}" />
    <property name="user" value="root" />
    <property name="password" value="6918001" />
</bean>

   <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="jtaDataSource" ref="xaDataSource" />
  <property name="packagesToScan" value="com.xxx.model" />
  <property name="jpaProperties">
      <props>
         <prop key="hibernate.hbm2ddl.auto">update</prop>
         <prop     key="hibernate.transaction.manager_lookup_class">com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup        </prop>
     </props>
   </property>
 <property name="jpaVendorAdapter">
     <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
         <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect" />
         <property name="showSql" value="false" />
         <property name="generateDdl" value="false" />
     </bean>
 </property>
</bean>

 <bean id="shardXaDataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource"
    lazy-init="true">
    <property name="pinGlobalTxToPhysicalConnection" value="true" />
    <property name="url" value="${shared_mysql_url}" />
    <property name="user" value="root" />
    <property name="password" value="6918001" />
</bean>


 <bean id="sharedEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="jtaDataSource" ref="shardXaDataSource" />
<property name="packagesToScan" value="com.xxx.shared.model" />
<property name="jpaProperties">
    <props>
        <prop key="hibernate.hbm2ddl.auto">update</prop>
        <prop key="hibernate.transaction.manager_lookup_class">com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup</prop>
    </props>
</property>
<property name="jpaVendorAdapter">
    <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect" />
        <property name="showSql" value="false" />
        <property name="generateDdl" value="false" />
    </bean>
</property>

transaction.xml:

 <?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:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
                    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
                    http://www.springframework.org/schema/tx
                    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<tx:annotation-driven proxy-target-class="true" /> 


 <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="300" />
 </bean>

 <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
 <property name="transactionManager" ref="atomikosTransactionManager" />
 <property name="userTransaction" ref="atomikosUserTransaction" />
 <property name="allowCustomIsolationLevels" value="true" />
</bean>

</beans>

* EDIT 01/20/2014 *

We managed to make it work but tons of warnings on startup:

   20 Jan 2014 15:14:16  WARN AbstractDataSourceBean - AtomikosDataSoureBean 'xaDataSource': poolSize equals default - this may cause performance problems!
20 Jan 2014 15:14:17  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:17  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:17  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:17  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:17  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:17  WARN JtaPlatformInitiator - HHH000427: Using deprecated    org.hibernate.transaction.TransactionManagerLookup strategy [hibernate.transaction.manager_lookup_class],        use newer org.hibernate.service.jta.platform.spi.JtaPlatform strategy instead    [hibernate.transaction.jta.platform]
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@4063a38e: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AbstractDataSourceBean - AtomikosDataSoureBean 'sharedXaDataSource': poolSize equals default - this may cause performance problems!
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN JtaPlatformInitiator - HHH000427: Using deprecated org.hibernate.transaction.TransactionManagerLookup strategy [hibernate.transaction.manager_lookup_class], use newer org.hibernate.service.jta.platform.spi.JtaPlatform strategy instead [hibernate.transaction.jta.platform]
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN AtomikosConnectionProxy - atomikos connection proxy for com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@3d00772f: WARNING: transaction manager not running?
20 Jan 2014 15:14:18  WARN EntityManagerFactoryRegistry - HHH000436: Entity manager factory name (default) is already registered.  If entity manager will be clustered or passivated, specify a unique value for property 'hibernate.ejb.entitymanager_factory_name'
20 Jan 2014 15:14:18  WARN UserTransactionServiceImp - No properties path set - looking for transactions.properties in classpath...
20 Jan 2014 15:14:18  WARN UserTransactionServiceImp - transactions.properties not found - looking for jta.properties in classpath...
20 Jan 2014 15:14:18  WARN UserTransactionServiceImp - Failed to open transactions properties file - using default values
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator     10.0.50.117.tm0050100104 with state COMMITTING
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator 10.0.50.117.tm0050100104 with state COMMITTING
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator 10.0.50.117.tm0050200104 with state COMMITTING
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator 10.0.50.117.tm0050200104 with state COMMITTING
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator 10.0.50.117.tm0050300104 with state COMMITTING
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator 10.0.50.117.tm0050300104 with state COMMITTING
20 Jan 2014 15:14:18  WARN CoordinatorImp - Local heuristic termination of coordinator 10.0.50.117.tm0050400104 with state COMMITTING

The new configuration is :

<bean id="xaDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean"
    init-method="init" destroy-method="close">
    <property name="xaDataSourceClassName">
        <value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value>
    </property>
    <property name="uniqueResourceName"><value>xaDataSource</value></property>      
     <property name="xaProperties">
        <props>
            <prop key="url">${mysql_url}</prop>
            <prop key="user">root</prop>
            <prop key="password">xxx</prop>
        </props>
    </property>
</bean>

<bean id="sharedXaDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean"
    init-method="init" destroy-method="close">
    <property name="xaDataSourceClassName">
        <value>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</value>
    </property>
    <property name="uniqueResourceName"><value>sharedXaDataSource</value></property>
    <property name="xaProperties">
        <props>
            <prop key="url">${shared_mysql_url}</prop>
            <prop key="user">root</prop>
            <prop key="password">xxx</prop>
        </props>
    </property>
</bean>

Spring data - two different data sources on the same transaction causing abnormality

Answer

Related questions