Spring TransactionManager - commit does not work

Question

I am trying to create Spring-based solution for running batch of SQL queries on MySQL 5.5 server. By "query" I mean any SQL statement that compiles, so the SQL batch job can contain for example several CREATE TABLE, DELETE and then INSERT statements.

I am using Spring Batch for this purpose.

I have transactionManager configured as follows.

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

and the dataSource:

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="${batch.jdbc.driver}" />
    <property name="url" value="${batch.jdbc.url}" />
    <property name="username" value="${batch.jdbc.user}" />  
    <property name="password" value="${batch.jdbc.password}" /> 
    <property name="maxIdle" value="10" />
    <property name="maxActive" value="100" />
    <property name="maxWait" value="10000" />
    <property name="validationQuery" value="select 1" />
    <property name="testOnBorrow" value="false" />
    <property name="testWhileIdle" value="true" />
    <property name="timeBetweenEvictionRunsMillis" value="1200000" />
    <property name="minEvictableIdleTimeMillis" value="1800000" />
    <property name="numTestsPerEvictionRun" value="5" />
    <property name="defaultAutoCommit" value="true" />
</bean>

My DAO class has the method configured with

@Transactional(propagation = Propagation.REQUIRES_NEW)

and I loop over a collection of the SQL statements calling the method with single SQL statement a time. The processing inside the method is as simple as:

simpleJdbcTemplate.getJdbcOperations().execute(sql);

I expected that when the DAO method completes, I would see the results in the DB. However, it seems like only when the Spring job execution completes the results become available in the DB.

I tried to do the commit inside my DAO method:

@Transactional(propagation = Propagation.REQUIRES_NEW)
private void executeSingleQuery(String sql) {
    PlatformTransactionManager transactionManager = (PlatformTransactionManager)context.getBean("transactionManager");


    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setPropagationBehavior(Propagation.REQUIRED.ordinal());

    TransactionStatus status = transactionManager.getTransaction(def);

    try {
        // execute your business logic here
        log.info("about to execute SQL query[" + sql + "]");
        simpleJdbcTemplate.getJdbcOperations().execute(sql);

    } catch (Exception e) {
        log.info("SQL query  was not committed due to exception and was marked for rollback");
        transactionManager.rollback(status);
    }

    transactionManager.commit(status);

    if (transactionManager.getTransaction(null).isRollbackOnly() 
            && transactionManager.getTransaction(null).isCompleted()) {
        log.info("SQL query commited!");
    } else {
        log.info("SQL query  was not committed due to: 1) the transaction has been marked for rollback " +
                "2) the transaction has not completed for some reason");
    }

    log.info("the query has completed");
}

I debugged the Spring code and saw that the commit I call from my DAO method is executed by TransactionTemplate (the flow reaches the line this.transactionManager.commit(status); and passes without exceptions)

I would appreciate any advice what should be done in order to make the DAO method to commit on every call (commit after every SQL statement it executes).

Grover

@Transactional annotation takes care of committing. In your code you don't need reference to transaction manager and commit the change explicitly, I suppose. – Seersucker

Answer 1

You cannot proxy private methods. i.e. The @Transactional you have here has no effect. Pull the method to your parent interface and it should work. Unless you have the proxyTargetClass setting enabled which is not recommended.

Halette

changed DAO method to public - same problem – Grover

pulling up the method to the parent interface helped the situation. Shukran! :) – Grover

Answer 2

When you call your executeSingleQuery() from within the same class you are not going via the proxy so the transactional annotation will have no effect.

You are mixing declarative and programmatic transactions, Presumably you want REQUIRES_NEW so you could remove the pointless @Transactional annotation and use Propagation.REQUIRES_NEW when setting up your DefaultTransactionDefinition.

Also, you might want to move transactionManager.commit(status) inside the try block, your current code rolls back, then attempts a commit when an Exception occurs.

Leisurely

Thanks, I upvote your answer because it was helpful (part of it). However I cannot accept 2 answers -and MadheTo was the 1st to answer... – Grover

Answer 3

We used the @Rollback(value = false) annotation and that fixed the issue you are facing.

Pledget