Transaction with Spring JDBC template

[annuk]

Hi,

I want to implement Transaction Management with Spring JDBC Template.
I used transaction advice, Annotation Driven transaction approach but didn't get any results.

I have two insert statements, i want them both to be completed.
It performs insertion on one table, when goes to perform insertion on second table, an exception is thrown. I want the transaction to rollback on this exception.

My transaction advice is as follows:

 <tx:advice id="addTxAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="addTest" propagation="REQUIRED" rollback-for="java.lang.Exception"/>
		</tx:attributes>
	</tx:advice>

	<aop:config>
		<aop:pointcut id="addAOP"
			expression="execution(* com.test.DaoImplementation.*(..))" />
		<aop:advisor advice-ref="addTxAdvice"
			pointcut-ref="addAOP" />
	</aop:config>

But didn't get any results.

Tried using the annotation approach:

@Transactional(propagation=Propagation.REQUIRED,rollbackForClassName="Exception",isolation=Isolation.READ_COMMITTED,rollbackFor=Exception.class)
public int addMethod()throws Exception
{
insert1();
insert2();
return 1;
}

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    	<property name="dataSource" ref="dataSource"/>
  	</bean> 
  	
  	<tx:annotation-driven transaction-manager="transactionManager"/>

Got the following in the debug logs:

DEBUG 2012-07-18 17:09:20,255 [http-8080-1] (AbstractBeanFactory.java:242) (doGetBean) - Returning cached instance of singleton bean 'transactionManager'
DEBUG 2012-07-18 17:09:20,261 [http-8080-1] (AbstractPlatformTransactionManager.java:365) (getTransaction) - Creating new transaction with name [com.test.dao.Dao.addMethod]: PROPAGATION_REQUIRED,ISOLATION_READ_COMMITTED; '',-java.lang.Exception,-Exception
DEBUG 2012-07-18 17:09:20,261 [http-8080-1] (DriverManagerDataSource.java:162) (getConnectionFromDriver) - Creating new JDBC DriverManager Connection to [jdbc:mysql://url:3306/local_db]
DEBUG 2012-07-18 17:09:20,275 [http-8080-1] (DataSourceTransactionManager.java:204) (doBegin) - Acquired Connection [com.mysql.jdbc.JDBC4Connection@125ee49] for JDBC transaction
DEBUG 2012-07-18 17:09:20,281 [http-8080-1] (DataSourceUtils.java:187) (prepareConnectionForTransaction) - Changing isolation level of JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] to 2
DEBUG 2012-07-18 17:09:20,284 [http-8080-1] (DataSourceTransactionManager.java:221) (doBegin) - Switching JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] to manual commit
 INFO 2012-07-18 17:09:20,286 [http-8080-1] (?:?) (add) - In DaoImplementation / addMethod()
DEBUG 2012-07-18 17:09:20,287 [http-8080-1] (JdbcTemplate.java:880) (batchUpdate) - Executing SQL batch update [INSERT INTO mapping(NAME, DOC_NO,ALLOCATE_FLAG, CREATED_BY) VALUES(?, ? ,?, ?)]
DEBUG 2012-07-18 17:09:20,288 [http-8080-1] (JdbcTemplate.java:569) (execute) - Executing prepared SQL statement [INSERT INTO mapping(NAME, DOC_NO,ALLOCATE_FLAG, CREATED_BY) VALUES(?, ? ,?, ?)]
DEBUG 2012-07-18 17:09:20,289 [http-8080-1] (JdbcUtils.java:362) (supportsBatchUpdates) - JDBC driver supports batch updates
 INFO 2012-07-18 17:09:20,297 [http-8080-1] (?:?) (add) - In DaoImplementation / insertQuery 
DEBUG 2012-07-18 17:09:20,298 [http-8080-1] (AbstractPlatformTransactionManager.java:843) (processRollback) - Initiating transaction rollback
DEBUG 2012-07-18 17:09:20,299 [http-8080-1] (DataSourceTransactionManager.java:279) (doRollback) - Rolling back JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@125ee49]
DEBUG 2012-07-18 17:09:20,300 [http-8080-1] (DataSourceUtils.java:213) (resetConnectionAfterTransaction) - Resetting isolation level of JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] to 4
DEBUG 2012-07-18 17:09:20,300 [http-8080-1] (DataSourceTransactionManager.java:322) (doCleanupAfterCompletion) - Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@125ee49] after transaction
DEBUG 2012-07-18 17:09:20,301 [http-8080-1] (DataSourceUtils.java:332) (doReleaseConnection) - Returning JDBC Connection to DataSource

It is showing initiating rollback, but it is not rolling back.

What may be the issue?
Please help.

Thanks in advance.
Regards,
Annuk

[Marten Deinum]

You are using MySQL make sure you use table types that support transactions, if your tables are MyISAM tables those aren't transactional.

[annuk]

Hi Marten Deinum,

You are using MySQL make sure you use table types that support transactions, if your tables are MyISAM tables those aren't transactional.

The tables were MyISAM.Changed it to INNODB and it worked.
Thanks.
Regards,
Annuk.