jta transaction not rolling back

[seth]

Hi,

We are using Oracle 8, Hibernate 3.05, Spring 2.6 and JOTM 2.0.10 on a stand alone application.
Our transaction consists in the following steps:

1.a Write to dataSource table1
1.b Write to dataSource table2
2. Write to dataSource2
3. Update to dataSource table1

That order is needed and each write depends on the results of the previous one.
We use Hibernate Template for all DAOs that connect to dataSource, but for dataSource2 we are using a JdbcDaoSupport that calls a BatchSQLUpdate(dataSource2). We cannot use Hibernate on dataSource2.
The problem is that, apparently, we are unable to make both dataSources to participate in the transaction. So, at the time we purposely generate an error condition to test the transaction.rollback on steps 2 or 3 the databases do not rollback. Bellow is our applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">


<beans>

<bean id="dataSource" class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
   <property name="transactionManager" ref="jotm" />
   <property name="driverName" value="${db.jdbc.driver}" />
   <property name="url" value="${db.url}" />     
   <property name="user" value="${db.user}"/>
   <property name="password" value="${db.pass}"/>
</bean>

<bean id="dataSource2" class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
   <property name="transactionManager" ref="jotm" />
   <property name="driverName" value="${db.jdbc.driver}" />
   <property name="url" value="${db2.url}" />      
   <property name="user" value="${db2.user}"/>
   <property name="password" value="${db2.pass}"/>
</bean>	
	
 <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource"><ref bean="dataSource"/></property>
	<property name="mappingResources">
            <list>
                <!-- model definitions ... -->
            </list>
        </property>
        <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
			<prop key="hibernate.show_sql">true</prop>
			<prop key="hibernate.use_sql_comments">false</prop>
			<prop key="hibernate.jdbc.batch_size">0</prop>
			<prop key="hibernate.jdbc.use_streams_for_binary">true</prop>
            <prop key="hibernate.cache.use_query_cache">true</prop>
          	<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
          	<prop key="hibernate.connection.autocommit">false</prop>
        </props>
        </property>
		<property name="jtaTransactionManager"><ref bean="jotm"/></property>
		<property name="useTransactionAwareDataSource"><value>true</value></property>	
    </bean>
	 <!-- DAOs definitions ... -->
	<bean id="myHibernateDAO" class="my.company.dao.MyHibernateDAOHibernate">
        <property name="sessionFactory"><ref local="sessionFactory"/></property>
    </bean>  
        ...
	<bean id="myJDBCDAO" class="my.company.dao.MyJDBCDAOJdbc">
        <property name="dataSource"><ref bean="dataSource2"/></property>		
    </bean>	
    
	
	<bean id="jotm" class="org.springframework.transaction.jta.JotmFactoryBean">
				<property name="defaultTimeout"><value>${jotm.defaultTimeout}</value></property>	
	</bean>
	
    <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
        <property name="userTransaction">
            <ref local="jotm"/>			
        </property>	
					
    </bean>
	
	<bean id="reportService" parent="baseTransactionProxy">
		<property name="target">
        	<bean class="my.company.service.MyServiceImpl">        
        		<property name="myHibernateDAO"><ref bean="myHibernateDAO"/></property>
			...
        		<property name="myJDBCDAO"><ref bean="myJDBCDAO"/></property>
        			
			</bean>
		</property>     	       
    </bean>

	<bean id="baseTransactionProxy" abstract="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"> 
        <property name="transactionManager"><ref bean="transactionManager"/></property> 
        <property name="transactionAttributes"> 
            <props> 
				<prop key="*">PROPAGATION_REQUIRED,-DataAccessException,-SQLException</prop> 
	    </props> 
        </property> 
        <property name="proxyTargetClass"> 
			<value>true</value> 
		</property> 
    </bean> 
	
	<bean id="openSessionInViewInterceptor"
      class="org.springframework.orm.hibernate3.support.OpenSessionInViewInterceptor">
      <property name="sessionFactory">
         <ref bean="sessionFactory" />
      </property>      
   </bean>	

</beans>

Hope someone could help us out, since we are not familiar of how to config spring to handle distributed transactions.

Any advice is greatly appreciated.
Thanks.

[seth]

Here are the relevant parts of the code:

A manager class loads the spring applicationContext, sets the timeOut for the userTransaction and calls the service to excute the distributed transaction.

 private void init()
    {
        try 
        {      
            _springLoader = new SpringLoader();
            
            setJtaTransactionManager((JtaTransactionManager)_springLoader.getBean("transactionManager"));            
            _logger.debug("myManager.getJtaTransactionManager: "+getJtaTransactionManager()); 
                        
            setUserTransaction((UserTransaction) getJtaTransactionManager().getUserTransaction());
            _logger.debug("getUserTransaction(): "+getUserTransaction());
            
        } 
        catch (Exception e) 
        {
            _logger.error("Exception of type :" + e.getClass().getName()
                    + " has been thrown");
            _logger.error("Exception message :" + e.getMessage(), e);
        }
        
    }
    
    
    public void run()
    throws ReportException
    {
        try
        {
            MyService myService = (MyService) _springLoader.getBean("myService");
            //seth:
            //Need to override the defaultTimout(60 sec.) in userTransaction
            //to avoid timing out while running the whole operation 
            getUserTransaction().setTransactionTimeout(Integer.parseInt(_props.getProperty(my.company.util.Constants.JOTM_TIMEOUT)));
            myService.runDistributedTransaction();
        }
        catch (Exception e)
        {
            _logger.error("Error ");
            throw new ReportException(e);
        }
        
       
    }

The service class will call the daos to perform the writes, we expect that if everything goes well, all dataSources will commit (what indeed happens). But, in case something goes wrong on any of the writes, all dataSources will rollback (which does not happen)

public void runDistributedTransaction()
    throws ServiceException

    {
        _logger.debug("runDistributedTransaction Called");

        try
        {
            _logger.debug("transaction begin");

           //The executeTransaction method
           //performs all writes to both dataSources
            executeTransaction();
           
            }
           

        }
        catch (Exception e)
        {
          
            _logger.error("Error: " + e.getMessage(), e);
            throw new ServiceException(e);
        }

Is there any spring guru that could help us out, please?

[seth]

We ran a test case in which we purposely change the userName of the dataSource2, but the writes in dataSource (one) were commited.
The spring log follows:

...
2006-03-09 11:50:33,462 DEBUG RuleBasedTransactionAttribute:119 - Applying rules to determine whether transaction should rollback on my.company.service.ServiceException: my.company.service.ServiceException: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot get connection for URL jdbc:oracle:thin:@myhost2:1521:mydb2 : ORA-01017: invalid username/password; logon denied

2006-03-09 11:50:33,463 DEBUG RuleBasedTransactionAttribute:137 - Winning rollback rule is: null
2006-03-09 11:50:33,464 DEBUG RuleBasedTransactionAttribute:143 - No relevant rollback rule found: applying superclass default
2006-03-09 11:50:33,465 DEBUG TransactionInterceptor:298 - my.company.service.MyServiceImpl.runDistributedTransaction threw throwable [my.company.service.ServiceException: my.company.service.ServiceException: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot get connection for URL jdbc:oracle:thin:@myhost2:1521:mydb2 : ORA-01017: invalid username/password; logon denied
] but this does not force transaction rollback
2006-03-09 11:50:33,466 DEBUG JtaTransactionManager:653 - Triggering beforeCommit synchronization
2006-03-09 11:50:33,470 DEBUG SessionFactoryUtils:882 - Flushing Hibernate Session on transaction synchronization
2006-03-09 11:50:33,893 DEBUG TransactionSynchronizationManager:137 - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@19abd2b] for key [StandardXADataSource:
     connection count=<0>
     number of dead connection=<0>
     dead lock max wait=<300000>
     dead lock retry wait=<10000>
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     driver name=<oracle.jdbc.driver.OracleDriver>
     number of *free* connections=<0>
     max con=<0>
     min con=<50>
     prepared stmt cache size=<16>
     transaction manager=<org.objectweb.jotm.Current@19836ed>
     xid connection size=<0>
StandardConnectionPoolDataSource:
     master prepared stmt cache size=<0>
     prepared stmt cache size =<16>
StandardDataSource:
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     url=<jdbc:oracle:thin:@myhost:1521:mydb>
     user=<db.user>
CoreDataSource :
     debug =<false>
     description =<null>
     login time out =<60>
     user =<db.user>
     verbose =<false>
] bound to thread [main]
2006-03-09 11:50:34,203 DEBUG TransactionSynchronizationManager:137 - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@19abd2b] for key [StandardXADataSource:
     connection count=<0>
     number of dead connection=<0>
     dead lock max wait=<300000>
     dead lock retry wait=<10000>
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     driver name=<oracle.jdbc.driver.OracleDriver>
     number of *free* connections=<0>
     max con=<0>
     min con=<50>
     prepared stmt cache size=<16>
     transaction manager=<org.objectweb.jotm.Current@19836ed>
     xid connection size=<0>
StandardConnectionPoolDataSource:
     master prepared stmt cache size=<0>
     prepared stmt cache size =<16>
StandardDataSource:
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     url=<jdbc:oracle:thin:@myhost:1521:mydb>
     user=<db.user>
CoreDataSource :
     debug =<false>
     description =<null>
     login time out =<60>
     user =<db.user>
     verbose =<false>
] bound to thread [main]
2006-03-09 11:50:34,430 DEBUG JtaTransactionManager:669 - Triggering beforeCompletion synchronization
2006-03-09 11:50:34,431 DEBUG TransactionSynchronizationManager:185 - Removed value [org.springframework.orm.hibernate3.SessionHolder@18e18a3] for key [org.hibernate.impl.SessionFactoryImpl@1f38fc6] from thread [main]
2006-03-09 11:50:34,437 DEBUG SessionFactoryUtils:785 - Closing Hibernate Session
2006-03-09 11:50:34,445 DEBUG TransactionSynchronizationManager:137 - Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@19abd2b] for key [StandardXADataSource:
     connection count=<0>
     number of dead connection=<0>
     dead lock max wait=<300000>
     dead lock retry wait=<10000>
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     driver name=<oracle.jdbc.driver.OracleDriver>
     number of *free* connections=<0>
     max con=<0>
     min con=<50>
     prepared stmt cache size=<16>
     transaction manager=<org.objectweb.jotm.Current@19836ed>
     xid connection size=<0>
StandardConnectionPoolDataSource:
     master prepared stmt cache size=<0>
     prepared stmt cache size =<16>
StandardDataSource:
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     url=<jdbc:oracle:thin:@myhost:1521:mydb>
     user=<db.user>
CoreDataSource :
     debug =<false>
     description =<null>
     login time out =<60>
     user =<db.user>
     verbose =<false>
] bound to thread [main]
2006-03-09 11:50:34,447 DEBUG TransactionSynchronizationManager:185 - Removed value [org.springframework.jdbc.datasource.ConnectionHolder@19abd2b] for key [StandardXADataSource:
     connection count=<0>
     number of dead connection=<0>
     dead lock max wait=<300000>
     dead lock retry wait=<10000>
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     driver name=<oracle.jdbc.driver.OracleDriver>
     number of *free* connections=<0>
     max con=<0>
     min con=<50>
     prepared stmt cache size=<16>
     transaction manager=<org.objectweb.jotm.Current@19836ed>
     xid connection size=<0>
StandardConnectionPoolDataSource:
     master prepared stmt cache size=<0>
     prepared stmt cache size =<16>
StandardDataSource:
     driver=<oracle.jdbc.driver.OracleDriver@1522de2>
     url=<jdbc:oracle:thin:@myhost:1521:mydb>
     user=<db.user>
CoreDataSource :
     debug =<false>
     description =<null>
     login time out =<60>
     user =<db.user>
     verbose =<false>
] from thread [main]
2006-03-09 11:50:34,448 DEBUG DataSourceUtils:276 - Returning JDBC Connection to DataSource
2006-03-09 11:50:34,579 DEBUG JtaTransactionManager:493 - Initiating transaction commit
2006-03-09 11:50:34,706 DEBUG JtaTransactionManager:693 - Triggering afterCompletion synchronization
2006-03-09 11:50:34,707 DEBUG TransactionSynchronizationManager:265 - Clearing transaction synchronization

[Outlaw]

Hi Seth,

I am facing a similar problem to yours, did you manage to find a solution to the problem?

Any help would be much appreciated.

Outlaw

[Costin Leau]

As long as the datasource is aware of the JTA transaction things should work out. I think in your case the datasource (i.e. XaPool/XaDatasource) are not properly implementing the contract.
Try their forum and geronimo (though it doesn't have a lot of documentation but you can find some nice examples on this forum and on Jencks site).
You could also try Arjuna (now part of Jboss as Jboss TX I think). Again, the problem is not with the TM but with the datasource actually - but by investigating the other TMs you'll also find information about getting some proper DS in place.
It's not a straight solution but in the open source world, I'm not aware of a one-stop solution for JTA.

[seth]

No, we didn't. Neither we were able to find help.
It appears to us that all the jta stuff is still in prototype stage and most people talk about things they read but that have never used in true production environments.
So, we dropped that approach an now we do everything manually. We lost around 2 months of work on this. I won't recommend it to use yet. Maybe we could revisit this problem next year, to see if the apis have evolved more.

[tnc]

I have done something similar with Oracle, Hibernate, JOTM and XAPool. The problem is not within the transaction manager but with XAPool. The OracleXADataSource they provide does not actually work - it is full of bugs -and the StandardXADataSource is not a true XA datasource, it just tries to emulate 2 phase commit. Unfortunately it does not do this very well and fails to rollback when errors occur.

If you do not need connection pooling then do not use XAPool and use oracle's Xa datasource directly as this provides full XA functionality.

[Juergen Mayrbaeurl]

I have done something similar with Oracle, Hibernate, JOTM and XAPool. The problem is not within the transaction manager but with XAPool. The OracleXADataSource they provide does not actually work - it is full of bugs -and the StandardXADataSource is not a true XA datasource, it just tries to emulate 2 phase commit. Unfortunately it does not do this very well and fails to rollback when errors occur.

If you do not need connection pooling then do not use XAPool and use oracle's Xa datasource directly as this provides full XA functionality.

I had the same problems and could solve them (at least I think so). See http://forum.springframework.org/showthread.php?t=32063.
I'm not using JOTM but Geronimo's transaction manager (from jencks).

Juergen