Transaction Management rollback with iBatis

[jfrompai]

Hi,
I'm trying to rollback transactions using spring declarative transaction management with iBatis.
I have set up a "facade" bean, which accesses a DAO (paysService).
In this DAO, I wrote a simple function : insertupdate, which is supposed to update an attribute of a record
AND to make an insert violating the Primary Key. This should raise a DataAccessException.
I should normally see no change in my database (oracle 8i), since both orders are in the same transaction and I set the -Exception flag on the "insert*" property.
Nevertheless, my changes are committed. I also tried with -DataAccessException, still no better.
When I test with a simple "java application" (see code below), my update is committed !
When I test with org.springframework.test package (JUnit test), transactions are rolled back OK, which is normal, according to the test package spec.
I seem to have a configuration problem, but I can't find where (I've seen several topics in the data access forum, and I tried to use all the solutions provided... without any luck).
Can anyone help me ?
Thanks,
JF.

XML Spring Configuration :

<beans>
	<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>/com/kiabi/config/jdbc.properties</value>
			</list>
		</property>
	</bean>

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName"><value>$&#123;jdbc.driverClassName&#125;</value></property>
		<property name="url"><value>$&#123;jdbc.url&#125;</value></property>
		<property name="username"><value>$&#123;jdbc.username&#125;</value></property>
		<property name="password"><value>$&#123;jdbc.password&#125;</value></property>
	</bean>

	<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
		abstract="true">
		<property name="transactionManager"><ref bean="transactionManager"/></property>
		<property name="transactionAttributes">
			<props>
				<prop key="insert*">PROPAGATION_REQUIRED,-Exception</prop>
				<prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>
				<prop key="delete*">PROPAGATION_REQUIRED,-Exception</prop>
				<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
			</props>
		</property>
	</bean>

	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource"><ref local="dataSource"/></property>
	</bean>

	<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="configLocation"><value>/com/kiabi/config/sqlmap_config.xml</value></property>
	</bean>


	<bean id="facade" parent="baseTransactionProxy">
		<property name="target">
			<bean class="com.kiabi.entreprise.impl.facadeImpl">
				<property name="paysService"><ref bean="paysService"/></property>
			</bean>
		</property>
	</bean>

<bean id="paysService" class="com.kiabi.entreprise.impl.PaysServiceImpl">
		<property name="dataSource"><ref bean="dataSource"/></property>
		<property name="sqlMapClient"><ref bean="sqlMapClient"/></property>
</bean>
</beans>

DAO function :

		public boolean insertupdate(String strCodePays) throws DataAccessException {
			KPays p=new KPays();
			p.setCodePays(strCodePays);
			KPays pays=this.getPays(p);
			pays.setLibPays("TEST");
			getSqlMapClientTemplate().update("updateKPays",pays) ;
			getSqlMapClientTemplate().insert("insertKPays",pays) ;
			return true;
		}

Java Application Test function :

		ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext(
				new String[] { 
						"/com/kiabi/config/environnementTest.xml", 
						"/com/kiabi/config/dataSource.xml",
						"/com/kiabi/config/services.xml"
						});
		BeanFactory factory = (BeanFactory) appContext;
		facade ml= (facade)factory.getBean("facade");
		PaysService kp=(PaysService) ml.getPaysService();
		
	try {
		kp.insertupdate("FR");
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

Spring JUnit Test function :

public void testInsertUpdate() {
	ps=(PaysService)this.getFacade().getPaysService();
 	try {
		ps.insertupdate("FR");
	} catch (DataAccessException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	assertNotNull("not exists", ps);
	}

}

[frozenquest]

Post the DDL you used to create the relating database objects.

Ensure you are creating the PK integrity on the table to which your inserting.

[jfrompai]

Hi, thanks for your reply.
Here is my DDL :

SQL Code :

CREATE TABLE K_PAYS
(
  CODE_PAYS    VARCHAR2(3)                      NOT NULL,
  LIB_PAYS     VARCHAR2(20)                     NOT NULL,
  CODE_DOUANE  NUMBER(3)                        NOT NULL,
  TYPE_PAYS    VARCHAR2(3)                      NOT NULL,
  CODE_DEVISE  VARCHAR2(3)
)
TABLESPACE &1
NOCACHE
NOPARALLEL;

CREATE UNIQUE INDEX KSCPAY_PK ON K_PAYS
(CODE_PAYS)
TABLESPACE &1
NOPARALLEL;


ALTER TABLE K_PAYS ADD (
  CONSTRAINT KSCPAY_PK PRIMARY KEY (CODE_PAYS)
    USING INDEX 
    TABLESPACE &1);

As you can see, there's nothing to it.
I made a simple jsp to make a CRUD test on this simple table. All went well.
In fact, all my DAO functions work well : they insert, update or delete if everything's OK with the database and throw an exception if (for example) a database constraint is violated.
The problem really is when I try to call one function that is OK (update, for example) and one that I make deliberately wrong (violating a PK). In this case, the first command (update) is committed even if I told the transactionManager to rollback on exceptions.
Regards,
JF.

[frozenquest]

I can't immediately see what the problem with your context is. One thing I'm not familiar with is the "-Exception" you have in your propagation rules. I've never seen that used before so I would take that out.

The other thing I would think about doing is changing your transactionManager to a transaction interceptor:

<!-- transaction Interceptor -->
<bean id="jtaTransactionInterceptor" class="org.springframework.transaction.interceptor.TransactionInterceptor">
    <property name="transactionManager">
      <value>org.springframework.transaction.jta.JtaTransactionManager</value>
	</property>
	<property name="transactionAttributes">
		<props>
			<prop key="insert*">PROPAGATION_REQUIRED</prop>
			<prop key="update*">PROPAGATION_REQUIRED</prop>
			<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
		</props>
	</property>
</bean>

And change your Impls to ProxyFactoryBeans (PFB) and inject the transaction:

<bean id="paysService" class="org.springframework.aop.framework.ProxyFactoryBean">
	<property name="proxyInterfaces"><value>com.kiabi.entreprise.impl.facadeImpl</value></property>
	<property name="target"><ref local="paysServiceTarget"/></property>
	<property name="interceptorNames">
		<list> 
			<!-- This list is ordered by importance of interception -->
			<value>jtaTransactionInterceptor</value> <!-- Transaction Manager -->
		</list>
	</property>
</bean> 

<bean id="paysServiceTarget" class="com.kiabi.entreprise.impl.PaysServiceImpl">
      <property name="dataSource"><ref bean="dataSource"/></property>
      <property name="sqlMapClient"><ref bean="sqlMapClient"/></property>
</bean>

This will allow you to define a transaction manager and reuse that manager in other beans. You will do a lookup on the PFB "paysService" which will allow for the transaction manager to intercept any method starting with what was defined in the <transactionAttributes>.

I'll continue to watch this thread so keep us updated. Let me know how it works.

Thanks,
Chris Rosenquest

[Colin Sampaleanu]

Hi,
I'm trying to rollback transactions using spring declarative transaction management with iBatis.
I have set up a "facade" bean, which accesses a DAO (paysService).
In this DAO, I wrote a simple function : insertupdate, which is supposed to update an attribute of a record
AND to make an insert violating the Primary Key. This should raise a DataAccessException.
I should normally see no change in my database (oracle 8i), since both orders are in the same transaction and I set the -Exception flag on the "insert*" property.
Nevertheless, my changes are committed. I also tried with -DataAccessException, still no better.
When I test with a simple "java application" (see code below), my update is committed !
When I test with org.springframework.test package (JUnit test), transactions are rolled back OK, which is normal, according to the test package spec.
I seem to have a configuration problem, but I can't find where (I've seen several topics in the data access forum, and I tried to use all the solutions provided... without any luck).
Can anyone help me ?
Thanks,
JF.

The behaviour you're seeing would appear to be completely normal. Keep in mind your test code is using the service object, which is not transactionally wrapped, instead of the facade, which is the object that is transactionally wrapped. Your exception would have to bubble up through the tx wrapper around the facade, for the transaction to be rolled back. Your test application would appear to actually not even be running with Spring transactions because of this, but rather auto-commit transactions provided by the datasource.

Frozenquest, your suggestion is actually not really relevant. TransactionProxyFactoryBean is just a specialization of ProxyFactoryBean. Using ProxyFactoryBean as you suggest will generally work, but there's never a case for using it over TransactionProxyFactoryBean, when you actuallly need to do tx wrapping, as it's more verbose.

Regards,

[jfrompai]

Hi there,
First, thanks for your pieces of advice. I studied them closely.
I finally got a solution : my needs were simple, so I did not use the JTA and sticked to my dbcp basic dataSource.
I already came across a thread talking about a default auto commit, but I did not know how to set it up on dbcp.
Yesterday, I saw this thread :
http://forum.springframework.org/viewtopic.php?p=6805http://forum.springframework.org/showthread.php?t=11134[/url]
So, I changed my configuration :
I modified my config this way :

	<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
		abstract="true">
		<property name="transactionManager"><ref bean="transactionManager"/></property>
		<property name="transactionAttributes">
			<props>
				<prop key="insert*">PROPAGATION_REQUIRED,-DataAccessException</prop>
				<prop key="update*">PROPAGATION_REQUIRED,-DataAccessException</prop>
				<prop key="delete*">PROPAGATION_REQUIRED,-DataAccessException</prop>
				<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
			</props>
		</property>
	</bean>

Adding the rollback on DataAccessException.
I also changed my datasource adding the default commit behaviour :

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName"><value>${jdbc.driverClassName}</value></property>
		<property name="url"><value>${jdbc.url}</value></property>
		<property name="username"><value>${jdbc.username}</value></property>
		<property name="password"><value>${jdbc.password}</value></property>
        <property name="defaultAutoCommit"><value>${jdbc.defaultAutoCommit}</value></property>
	</bean>

Of course, I had set jdbc.defaultAutoCommit=false in my configurer (jdbc.properties).
And it works :? !
My test program (not Junit test, but a standalone class containing a "main") performs the update, the insert that violates the PK constraint and rolls-it all back.

JF.

[zchen]

I have similar configuration using Spring and iBATIS as you, and is trying to run junit test. But it didn't rollback, and saw the following message in the log:

2006-02-02 16:55:33,828 DEBUG [org.springframework.transaction.interceptor.Transa ctionInterceptor] - Invoking commit for transaction on com.fci.common.useragent.dao.IDataFacade.getClient ListCount
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Initiating transaction commit
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Committing JDBC transaction on Connection [org.apache.commons.dbcp.PoolableConnection@edf389]
2006-02-02 16:55:33,828 DEBUG [org.springframework.transaction.support.Transactio nSynchronizationManager] - Removed value [org.springframework.jdbc.datasource.ConnectionHold er@fced4] for key [org.apache.commons.dbcp.BasicDataSource@4c47db] from thread [main]
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceUtil s] - Resetting read-only flag of JDBC Connection [org.apache.commons.dbcp.PoolableConnection@edf389]
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Releasing JDBC Connection [org.apache.commons.dbcp.PoolableConnection@edf389] after transaction
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceUtil s] - Returning JDBC Connection to DataSource
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Triggering beforeCompletion synchronization
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Initiating transaction rollback
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Rolling back JDBC transaction on Connection [org.apache.commons.dbcp.PoolableConnection@15fc40c]
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Triggering afterCompletion synchronization
2006-02-02 16:55:33,828 DEBUG [org.springframework.transaction.support.Transactio nSynchronizationManager] - Clearing transaction synchronization
2006-02-02 16:55:33,828 DEBUG [org.springframework.transaction.support.Transactio nSynchronizationManager] - Removed value [org.springframework.jdbc.datasource.ConnectionHold er@17200b4] for key [org.apache.commons.dbcp.BasicDataSource@16614e7] from thread [main]
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceTran sactionManager] - Releasing JDBC Connection [org.apache.commons.dbcp.PoolableConnection@15fc40c] after transaction
2006-02-02 16:55:33,828 DEBUG [org.springframework.jdbc.datasource.DataSourceUtil s] - Returning JDBC Connection to DataSource
2006-02-02 16:55:33,828 INFO [com.fci.common.useragent.dao.service.DataServiceTe st] - Rolled back transaction after test execution

It seems DataSourceTransactionManager called commit first, then call roll back.

Do you know where is it possibly wrong?

[eduardo]

jfrompai,

Could you please post more details of the solution that you have made work?

I am having the same problems you had.

Thanks,

Eduardo

[dkalcevich]

What if you try to change the Transaction Proxy to the following:

<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean"
abstract="true">
<property name="transactionManager"><ref bean="transactionManager"/></property>
<property name="transactionAttributes">
<props>
<prop key="insert*">PROPAGATION_REQUIRED,-org.springframework.dao.DataAccessException</prop>
<prop key="update*">PROPAGATION_REQUIRED,-org.springframework.dao.DataAccessException</prop>
<prop key="delete*">PROPAGATION_REQUIRED,-org.springframework.dao.DataAccessException</prop>
<prop key="*">PROPAGATION_REQUIRED,readOnly</prop>
</props>
</property>
</bean>

I added "-org.springframework.dao.DataAccessException" to the transaction Attributes.

Let me know if that helps.

Daniel