eHarmony Engineering logo

Spring Batch and Oracle 11G

June 26, 2014

I am sure anyone working with Spring Batch and using Oracle as metadata store has encountered this issue. The problem I am talking about is the default Transaction Isolation level in Spring Batch (SERIALIZABLE), which is causing inconsistent behavior. (However it is fair to mention that this issue isn’t happening as frequently in 10G or earlier versions, and moving to Oracle 11G makes it inevitable.)
There are many good forum discussions on this topic, but I thought it would be good to have all of the different solution proposals discussed in one consolidated post, as well as share what actually worked for our use case.

Our Spring Batch Version is:

2.1.9.RELEASE

Exception stack trace that will most likely happen is the following:

org.springframework.dao.CannotSerializeTransactionException: PreparedStatementCallback;
SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction; nested exception is
java.sql.SQLException: ORA-08177: can't serialize access for this transaction

This actually means that Oracle enforced pessimistic / very restrictive locking on the Spring Batch tables, and any concurrent operation on these tables will most likely result in such exceptions.
Actually, to be more precise I should mention that this issue might still happen if there are nearly concurrent operations trying to modify the same data block in Oracle.
Note that “nearly concurrent” means that Oracle has insufficient history information to determine whether a row has been updated by a too recent transaction.

In order to fix this problem many people suggest increasing INITRANS on the Oracle side, however most likely that won’t fix the problem and the change needs to be performed in the Spring Batch client application, since in reality Oracle is doing whatever it is told to do, namely enforce an aggressive locking strategy for the SERIALIZABLE transaction isolation level.

Here is the Spring Batch application context with the required changes. We will go over each individual change.

<batch:job-repository id="jobRepository"
                data-source="springBatchDataSource"
                transaction-manager="txManager"
                isolation-level-for-create="READ_COMMITTED"
                table-prefix="BATCH_" />

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

<bean id="springBatchDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass"            value="${batch.jdbc.driver}"/>
    <property name="jdbcUrl"                value="${batch.jdbc.url}"/>
    <property name="user"                   value="${batch.jdbc.user}"/>
    <property name="password"               value="${batch.jdbc.password}"/>
    <property name="acquireRetryAttempts"   value="120"/>
    <property name="acquireRetryDelay"      value="10000"/>
</bean>

<!-- AOP to pointcut any Repository class -->
<!-- Ensures that the below transactional advice runs for any execution of an operation
defined by the Repository interface -->
<aop:config>
    <aop:pointcut id="allRepositoryMethods" expression="execution(* org.springframework.batch.core..*Repository+.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="allRepositoryMethods" />
</aop:config>

<tx:advice id="txAdvice" transaction-manager="txManager">
    <tx:attributes>
        <!-- advises all methods to use the default transaction settings and whatever is overridden here -->
        <tx:method name="*" isolation="READ_COMMITTED" />
    </tx:attributes>
</tx:advice>

1) First change is to ensure that the JobRepository is using isolation-level-for-create=”READ_COMMITTED”. Note that the default level is SERIALIZABLE.
As you can already guess from the property name, this change won’t be enough, since there will still be some operations for which Isolation Level won’t be overridden, hence the default level from the Transaction Manager will be used.

2) For the datasource you can use org.apache.commons.dbcp.BasicDataSource, however I highly recommend using com.mchange.v2.c3p0.ComboPooledDataSource, since it comes with “retry-with-delay” and many more additional features that will be of a huge help in case the database is out for some reason and you do not want to perform any manual operation.

3) Third, and another important change, is the Transaction Advice, since it will force transaction-manager=”txManager” on all Repository operations. Hence the READ_COMMITTED isolation level will be used for any operation.
Note: Having this change in place means that you might not need “isolation-level-for-create” setting, however I think that it is cleaner to have consistent isolation level throughout the object hierarchy in the first place.

These are all of the changes required to fix the issue.
Most likely you won’t need to perform any other change, such as increasing INITRANS property value in Oracle.