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?

@Moles-JWS: I have updated my question with the datasource settings. Hope this helps — DarkKnightFan, Apr 26 '13 at 04:53

score 4 · Accepted Answer · answered Apr 26 '13 at 13:19

4

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.

answered Apr 26 '13 at 13:19

fpmoles

1,209
8
14

Thanks mate. This is working. But i have another question if you can answer it here. Can I handle this in the java code, wherein I explicitly set the autoCommit OFF, execute the statement, COMMIT/ROLLBACK and then again set autoCommit to ON. – DarkKnightFan Apr 26 '13 at 14:29
Sure you can, just get a handle to the dataSource from Spring, from its connection you can flip the AutoCommit bit, execute the code, then flip it back and move on. I struggle with why you would do this, but that is my perspective only. – fpmoles Apr 26 '13 at 15:27
Well I know this is not the best way to handle such situations but the place where I need to use transaction management is a one off case. For this I dont want to change the global datasource configuration already in place and getting used. Thanks for the answer. Will try it out. – DarkKnightFan Apr 29 '13 at 05:04

DarkKnightFan · Answer 2 · 2013-04-29T07:25:12.620

EDIT:

I was finally able to resolve this by doing the following:

dmlDataSource.setDefaultAutoCommit(false); //set autocommit to false explicitly.
Exception ex = (Exception)transactionTemplate.execute(new TransactionCallback() {
                public Object doInTransaction(TransactionStatus ts) {
                    try {
                        dmlJdbcTemplate.execute(sqlInsertQuery);
                        ts.setRollbackOnly();
                        dmlDataSource.setDefaultAutoCommit(true); // set autocommit back to true    
                        return null;
                    } catch (Exception e) {
                        ts.setRollbackOnly();
                        LOGGER.error(e);
                        dmlDataSource.setDefaultAutoCommit(true); // set autocommit back to true    
                        return e;
                    }
                }
            });

I am not using the transaction manager now. Using the trasactionTemplate and doing the following:

Exception ex = (Exception)transactionTemplate.execute(new TransactionCallback() {
                public Object doInTransaction(TransactionStatus ts) {
                    try {
                        dmlJdbcTemplate.execute(sqlInsertQuery);
                        ts.setRollbackOnly();                           
                        return null;
                    } catch (Exception e) {
                        ts.setRollbackOnly();
                        LOGGER.error(e);
                        return e;
                    }
                }
            });

after using @Moles-JWS's answer I am now able to rollback successfully. But I want to handle this only in this method and not change the global configuration of the datasource.

Can I do it here programmatically?

I am just thinking, won't dmlDataSource.setDefaultAutoCommit(false); affect your global DataSource and while this portion runs, all of your DB transaction from every module will have auto-commit set to false. — Shamim Hafiz - MSFT, May 31 '13 at 17:57
hmmm.... you have raised a good point @ShamimHafiz ... I will have to test this once... other alternative is I create a separate instance of jdbctemplate specifically for this function. So that the above changes will not have any impact on the rest of the code. — DarkKnightFan, Jun 03 '13 at 05:07

Spring Transaction Manager: Rollback doesnt work

2 Answers2