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).

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.

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.