Problems with Spring JDBC Transaction Management

[Marten Deinum]

How are you accessing your SpringJDBCDaoBean? Does it get injected? Do you create a new instance? Can you post the calling code?

[dejanp]

For XA you need JTA, XA capable datasource, XA capable driver and XA capable database.

You are using HibernateTransactionManager with DriverManagerDataSource and that can't work. HibernateTransactionManager has nothing to do with JTA, you need org.springframework.transaction.jta.JtaTransaction Manager instead. XA DataSource you usually get by JNDI lookup of the server-side datasource.

If you need only one database why do you need JTA?

[Surya1234]

I am using the spring to generate the SpringJDBC dao object using IOC. The code for that is

factory = new ClassPathXmlApplicationContext(
				"transaction-management.xml");
		personDAO = (PersonDAO) factory.getBean("personDAO");
		springJdbcDAO = (ISpringJdbcDAO) factory.getBean("springJdbcDAO");

I am trying the XA transactions not for the same database but two different databases. I am not using HibernateTransaction Manager for that.
What i meant earlier was, the open source JTA implementations like JBossTS dont seem to rollback transactions. They would not even rollback transactions of hibernate on a single database/datasource.
I am now trying with Websphere Application Server support for JTA using jndi.

[Marten Deinum]

The above code snippet explains WHY everything is happening. It is happening the way you configured it. Each call to a dao is in a SEPARATE transaction. You need to execute it in 1 transaction. The configuration above wouldn't even work with a JTA Transactionmanager as you already noticed.

What is happening! (assuming the code below)

factory = new ClassPathXmlApplicationContext("transaction-management.xml");
personDAO = (PersonDAO) factory.getBean("personDAO");
springJdbcDAO = (ISpringJdbcDAO) factory.getBean("springJdbcDAO");

personDAO.someMethod();
springJdbcDAO.someJdbcMethod();

Before executing the someMethod a transaction is started, when that is finished the transaction is committed. When someJdbcMethod is executed a transaction is started when finished the transaction will be committed.


Schematically

<start transaction>
personDAO.someMethod();
<commit transaction>
<start transaction>
springJdbcDAO.someJdbcMethod();
<commit transaction>

This behavior doesn't change regardless of which TransactionManager (or TransactionManagers) you use. It is in your way of coding which is wrong.

Create a Service which wrappes those to calls and make the Service transactional and for that use the JTATransactionManger (because you have different XA DataSources).

Code

public interface SomeService {
   void processPerson();
}

public class SomeServiceImpl implements SomeService {

  private PersonDAO personDAO;
  private SpringJdbcDAO springJdbcDAO;

  public void processPerson() {
    personDAO.someMethod();
    springJdbcDAO.someJdbcMethod();
  }  


  public void setPersonDAO(PersonDAO personDAO) {
    this.personDAO=personDAO;
  }

  public void setSpringJdbcDAO(SpringJdbcDAO springJdbcDAO) {
    this.springJdbcDAO=springJdbcDAO;
  }


}

Transactions should be on your service

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


	<bean id="personDAO" class="util.txmanagement.dao.PersonDAOImpl">
		<property name="sessionFactory" ref="mysessionFactory"/>
	</bean>

	<bean id="springJdbcDAO" class="util.txmanagement.daojdbc.SpringJdbcDAO" />

    <bean id="someService" class="util.txmanagement.service.SomeServiceImpl">
      <property name="personDAO" ref="personDAO"/>
      <property name="springJdbcDAO" ref="springJdbcDAO"/>
    </bean>

	<aop:config>
		<aop:pointcut id="serviceMethod" expression="execution(* util.txmanagement..*ServiceImpl.*(..))" />
		<aop:advisor advice-ref="txAdvice" pointcut-ref="serviceMethod" />
	</aop:config>

	<tx:advice id="txAdvice" transaction-manager="txManager">
		<tx:attributes>
			<tx:method name="check*" propagation="REQUIRED"
				isolation="DEFAULT" />
			<tx:method name="get*" read-only="true" rollback-for="BusinessException" />
			<tx:method name="*" />
		</tx:attributes>
	</tx:advice>

	<bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
		<property name="sessionFactory" ref="mysessionFactory" />
	</bean>

	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close">
		<property name="driverClassName" value="com.ibm.db2.jcc.DB2Driver"/>
		<property name="url" value="jdbc:db2://localhost:50001/testdb2"/>
		<property name="username" value="db2admin"/>
		<property name="password" value="Pass123$$"/>
	</bean>

	<bean id="mysessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="dataSource"/>
		<property name="configLocation" value="hibernate.cfg.xml"/>
		<property name="configurationClass" value="org.hibernate.cfg.AnnotationConfiguration"/>
		<property name="hibernateProperties">
			<props>
				<prop key="hibernate.dialect">org.hibernate.dialect.DB2Dialect</prop>
				<prop key="hibernate.hbm2ddl.auto">update</prop>
				<prop key="hibernate.connection.release_mode">on_close</prop>
			</props>
		</property>
	</bean>

</beans>

The configuration above works with the HibernateTransactionManager but if you need transactions spanning multiple DataSources you need a JTATransactionManager.

I strongly suggest you read up on transactions and TransactionManagement in Spring, also try to understand the proxing mechanism in Spring and what is executed/added when.

[Surya1234]

The above code snippet explains WHY everything is happening. It is happening the way you configured it. Each call to a dao is in a SEPARATE transaction. You need to execute it in 1 transaction. The configuration above wouldn't even work with a JTA Transactionmanager as you already noticed.
.

Mdeinum,
May be i didnt explain the problem correctly. The client code you saw was a snippet from my junit test case. Which was designed to test both hibernate and jdbc transactions individually. And it does that obviously in different methods. And the transactions i am refering to are either pure jdbc or pure hibernate transactions. Meaning to say, when i say transaction was not rolled back i mean a call to a jdbc dao which executes jdbc DML commands in one method and throws an exception at the end of method. As shown below

public void checkInValidJdbcTransaction() {
	factory = new ClassPathXmlApplicationContext("testSpringJdbc.xml");


	// ----------- INSERT operation

	// getting the insert worker object, and also preparing the object to be
	// inserted
	insertObjectTest = (InsertObject) factory.getBean("insertObjectTEST");
	setValueList.add(ID);
	setValueList.add(NAME);
	// inserting the object
	insertObjectTest.addObject(setValueList);

	// ----------- END OF INSERT--------------

	
	// ----------- UPDATE operation

	// preparing for updation
	updateObjectTest = (UpdateObject) factory.getBean("updateObjectTEST");
	setValueList = new ArrayList<String>();
	conditionList = new ArrayList<String>();
	setValueList.add(UPDATEDNAME);
	conditionList.add(ID);
	updateObjectTest.updateObject(setValueList, conditionList);
	throw new BusinessException();

}

Logically speaking the above mentioned inserts and updates should be rolled back once the exception is thrown.
Please note, even if i remove the hibernate section from my application I am still not able to rollback the jdbc transactions. I am in no way combining the hibernate transactions with jdbc transactions or vice versa in one single transaction.

[dejanp]

Logically speaking the above mentioned inserts and updates should be rolled back once the exception is thrown.

No. As far as I can tell the exception probably does nothing at all, unless you are starting a transaction somewhere before you execute that method.

[Marten Deinum]

Next time please post code which is correct and shows us what you are trying to do.

As dejanp mentioned your code does exactly what you tell it to do it commits after the insert and the update. The exception you throw is nice but it doesn't rollback anything because everythin is already committed.

insertObjectTest = (InsertObject) factory.getBean("insertObjectTEST");
	setValueList.add(ID);
	setValueList.add(NAME);
	// inserting the object
        <start transaction>
	insertObjectTest.addObject(setValueList);
        <commit transaction>
	// ----------- END OF INSERT--------------

	
	// ----------- UPDATE operation

	// preparing for updation
	updateObjectTest = (UpdateObject) factory.getBean("updateObjectTEST");
	setValueList = new ArrayList<String>();
	conditionList = new ArrayList<String>();
	setValueList.add(UPDATEDNAME);
	conditionList.add(ID);
        <start transaction>
	updateObjectTest.updateObject(setValueList, conditionList);
        <commit transaction>
	throw new BusinessException();

Also WHY oh WHY are you recreating an ApplicationContext on each method calll?! Inject your needed beans or retrieve them and don't create an new ApplicationContext each time. Code like that makes me wonder if you understand the basics of Spring and what the lifecycle of beans is. I suggest you read chapter 3 and chapter 9 of the reference guide.

If you still have problems please post a zip file which contains the testcase/config/code so that we can see and test what is missing/going wrong in your application. You can send it to mdeinum [at] gmail [dot] com.

[Surya1234]

Mdeinum and dejanp, thanks for the replies.
I put the

new ClassPathXmlApplicationContext("testSpringJdbc.xml");

in the example only from more readability point of view and make the function look more complete/independent. But in the actual Junit i am putting this in the setup() method. Done so to execute the various test scenarios independently. And i do know how cumbersome and heavy it would be if i were to create the factory object in each and every method.

I even tried to debug and locate the issue, I can see the proxy getting enabled. And this proxy leads into the spring api which first sets the autocommit as off and then executes the DML statements and when the desired exception is noticed/handled it rolls back the transaction by calling the connection.rollback(). Having said this, the end outcome is that the transaction is not rolled back. And when I check in the database i can see the record.
I may not be able to share the source files. But i can tell you, the Jdbc implementation is done using spring-jdbc's built in SQLUpdate, MappingSQLQuery, etc. The classes for jdbc implementation, which i instantiate internally extend the mentioned spring classes.

Please note,
The same logic works with the hibernate where the Session is kept open till the end of the transaction. And i thought the transactionManager keeps all the database connections open till the end of the transaction.

[Marten Deinum]

How Transactions are organized depends on the TransactionManager and the implementation used.

I still don't have the overview of your code and configuration, each example you posted doesn't give a accurate view of your situation, there is something missing each time or something you do different in the actual code.

The combination with Hibernate/Jdbc works, very well, we use it at numerous locations in our code without any problems. It must be something in your code/configuration.

So without a complete testcase or at least the actual configuration and problematic code I cannot help you any further.

[Surya1234]

Just wanted to add a piece of information;
the springjdbc implementation that i have works well programmatically.

transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus ts) {
				int i =0;
				try {
					insertObjectTest = (InsertObject) factory
							.getBean("insertObjectTEST");
					setValueList.add(SpringJdbcTest.ID);
					setValueList.add(SpringJdbcTest.NAME);
					// inserting the object
					insertObjectTest.addObject(setValueList);
					setValueList.clear();
					setValueList.add(SpringJdbcTest.ID2);
					setValueList.add(SpringJdbcTest.NAME2);
					// inserting the object
					insertObjectTest.addObject(setValueList);

					updateObjectTest = (UpdateObject) factory
							.getBean("updateObjectTEST");
					setValueList = new ArrayList<String>();
					conditionList = new ArrayList<String>();
					setValueList.add(SpringJdbcTest.UPDATEDNAME);
					conditionList.add(SpringJdbcTest.ID);
					updateObjectTest.updateObject(setValueList, conditionList);
					setValueList.clear();
					conditionList.clear();
					setValueList.add(SpringJdbcTest.UPDATEDNAME2);
					conditionList.add(SpringJdbcTest.ID2);
					updateObjectTest.updateObject(setValueList, conditionList);
					
					//throwing the exception to make the transaction fail.
					throw new RuntimeException("Transaction set to fail");

				} catch (Exception ex) {
					i=1;
					ts.setRollbackOnly();
				}
				// The execution should reach here, after the exception has been thrown and caught
				assertEquals(1, i);
				return null;
			}

		});