Atomikos Forum

MS SQL Server and XA transactions fails

I am running MS SQL server 2008 R2 and using Spring, Hibernate and Atomikos.

I can query the database and insert into the database, but as soon as I try to perform more than one action on the database within a transaction (for example, performing an insert into one table followed by a select on another table), the second action hangs and eventually fails with the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The function START: failed. No transaction cookie was returned

I've been stuck at this point for a couple of days now and I'm at a loss as to how to fix this.  Does anyone have any ideas?

Here's my configuration:

applicationContext.xml

<bean id="userTransactionService"  class=
  "com.atomikos.icatch.config.UserTransactionServiceImp" 
  init-method="init" destroy-method="shutdownForce">
    <constructor-arg>     
      <props>
        <prop key="com.atomikos.icatch.service">
          com.atomikos.icatch.standalone.UserTransactionServiceFactory
                </prop>
    <prop key="com.atomikos.icatch.enable_logging">false</prop>
        <prop key="com.atomikos.icatch.tm_unique_name">my-tm</prop>                
      </props>
    </constructor-arg> 
</bean>

<bean id="atomikosTransactionManager"     
  class="com.atomikos.icatch.jta.UserTransactionManager"
  init-method="init" destroy-method="close"
  depends-on="userTransactionService">
        <!-- IMPORTANT: disable startup because the userTransactionService above does this -->
        <property name="startupTransactionService" value="false"/>
        <property name="forceShutdown" value="false" />
    </bean>

    <bean id="atomikosUserTransaction"
      class="com.atomikos.icatch.jta.UserTransactionImp" 
      depends-on="userTransactionService">
        <property name="transactionTimeout" value="300" />
    </bean>

    <!--      Configure the Spring framework to use JTA transactions from Atomikos    -->
    <bean id="transactionManager"
      class="org.springframework.transaction.jta.JtaTransactionManager"
      depends-on="userTransactionService">
        <property name="transactionManager" ref="atomikosTransactionManager" />
        <property name="userTransaction" ref="atomikosUserTransaction" />
        <property name="allowCustomIsolationLevels" value="true"/>
        <property name="nestedTransactionAllowed" value="false"/>
    </bean>
   
    <tx:annotation-driven transaction-manager="transactionManager" />
       
    <bean id="persistenceUnitManager" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager"
      depends-on="userTransactionService">
        <property name="persistenceXmlLocation" value="classpath:META-INF/web-persistence.xml"/>
        <property name="dataSources">
            <map>
                <entry key="adminDataSource" value-ref="adminDataSource"/>
                <entry key="coreDataSource" value-ref="coreDataSource"/>
            </map>
        </property>
    </bean>   
 
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="adminEntityManagerFactory"
      depends-on="userTransactionService">
        <property name="persistenceUnitManager" ref="persistenceUnitManager"/>
        <property name="persistenceUnitName" value="${jpa.adminPersistenceUnitName}"/>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.transaction.manager_lookup_class">
                    com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup
                </prop>
            </props>
        </property>
    </bean>
 
    <bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="coreEntityManagerFactory"
      depends-on="userTransactionService">
        <property name="persistenceUnitManager" ref="persistenceUnitManager"/>
        <property name="persistenceUnitName" value="${jpa.corePersistenceUnitName}"/>
        <property name="jpaProperties">
            <props>
                <prop key="hibernate.transaction.manager_lookup_class">
                    com.atomikos.icatch.jta.hibernate3.TransactionManagerLookup
                </prop>
            </props>
        </property>
    </bean>

<bean id="parentDataSource"
          class="com.atomikos.jdbc.AtomikosDataSourceBean"
          init-method="init"
          destroy-method="close"
          abstract="true"
          depends-on="userTransactionService">
        <property name="xaDataSourceClassName" value="com.microsoft.sqlserver.jdbc.SQLServerXADataSource" />
        <property name="maxPoolSize" value="15"/>
        <property name="minPoolSize" value="8"/>
        <property name="testQuery" value="select count(*) from core.samples"/>
    </bean>
   
    <bean id="adminDataSource" parent="parentDataSource">
        <property name="uniqueResourceName" value="XA_ADMIN_DB"/>
        <property name="testQuery" value="select count(*) from admin.courses"/>
        <property name="xaProperties">
            <props>
                <prop key="user">test</prop>
                <prop key="password">mypwd</prop>
                <prop key="URL">jdbc:sqlserver://localhost:1433;databaseName=Test</prop>
            </props>
        </property>
    </bean>
   
    <bean id="coreDataSource" parent="parentDataSource">
        <property name="uniqueResourceName" value="XA_CORE_1_DB"/>
        <property name="xaProperties">
            <props>
                <prop key="user">test</prop>
                <prop key="password">mypwd</prop>
                <prop key="URL">jdbc:sqlserver://localhost:1433;databaseName=Test</prop>
            </props>
        </property>
    </bean>


persistence.xml

<persistence-unit name="sqlServerAdminPersistenceUnit" transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>adminDataSource</jta-data-source>
        <jar-file>WEB-INF/lib/dataAdmin-${pom.version}.jar</jar-file>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <property name="hibernate.connection.release_mode" value="auto"/>
        </properties>
    </persistence-unit>

    <persistence-unit name="sqlServerCorePersistenceUnit" transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>coreDataSource</jta-data-source>
        <jar-file>WEB-INF/lib/dataCore-${pom.version}.jar</jar-file>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
            <property name="hibernate.connection.charSet" value="UTF-8"/>
            <property name="hibernate.connection.release_mode" value="auto"/>
        </properties>
    </persistence-unit>
John A Meyer Send private email
Tuesday, August 06, 2013
 
 
Wouldn't you know it.  The moment I ask the question I figure out the answer.

According to one of the many links I used to configure this, I was supposed to set the hibernate.connection.release_mode to auto.  This is incorrect. 

When the release mode is set to auto and you are using JTA transactions, hibernate will release the connection after every statement.  If you have more than one statement within a transaction you are in trouble.

So, setting hibernate.connection.release_mode to after_transaction is the secret sauce.

Thanks
John A Meyer Send private email
Tuesday, August 06, 2013
 
 
Thanks for sharing!
Guy Pardon Send private email
Saturday, September 21, 2013
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics