Spring Transaction Manager: Rollback doesnt work

Question

I wish to execute few insert queries within a transaction block where if there is any error all the inserts will be rolled back.

I am using MySQL database and Spring TransactionManager for this. Also the table type is InnoDB

I have done my configuration by following the steps mentioned here.

Following is my code (for now only one query)

TransactionDefinition def = new DefaultTransactionDefinition();
TransactionStatus status = null;

status = transactionManager.getTransaction(def);
jdbcTemplate.execute(sqlInsertQuery);
transactionManager.rollback(status);

Spring config xml:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource">
        <ref bean="dataSource" />
    </property>
</bean>

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

Datasource config:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property name="url" value="${jdbc.url}" />
    <property name="username" value="${jdbc.username}" />
    <property name="password" value="${jdbc.password}" />
    <property name="initialSize" value="${jdbc.initialSize}" />
    <property name="maxActive" value="${jdbc.maxActive}" />
    <property name="minIdle" value="${jdbc.minIdle}" />
    <property name="maxIdle" value="${jdbc.maxIdle}" />
    <property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
    <property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
    <property name="testOnReturn" value="${jdbc.testOnReturn}" />
    <property name="validationQuery" value="${jdbc.validationQuery}" />
    <property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
    <!--<property name="removeAbandoned" value="true"/> <property name="removeAbandonedTimeout" 
        value="10"/> <property name="logAbandoned" value="false"/> -->
    <property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}" />
</bean>

This code works perfectly fine and the record gets inserted. But the rollback doesnt work! It executes the rollback statement without any error but to no effect.

Can anyone guide me where am I going wrong?

Answer 1

It appears that the issues is your datasource is not set to have autocommit off.

<property name="defaultAutoCommit" value="false"/>

Give that a try. I have never used the TransactionManager outside of a proxy so I am not sure if there any other gotchas using it directly like this, but I would recommend you look at either AOP transactions or the convience AOP proxy annotation @Transactional just because it is more common.