Sunday, November 13, 2011

Spring Batch: Deadlock Inserting Job Instances

I have been working on my first Spring Batch project and so far I'm a big fan. I think I'm pretty far along and the only real issue I've had is random occurrences of deadlock when the app tries to create instances of the same job simultaneously in the job database (in table BATCH_JOB_INSTANCE):

  2011-11-13 12:48:18,808 ERROR [AbstractStep:212] Encountered an error executing the step
org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback;
SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)];
Deadlock found when trying to get lock; try restarting transaction;
nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:265)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)

I'm using MySQL as the database backing the batch app. The Job Repository, I'm using seemed pretty stock and I never had given it much thought:

     <bean id="jobRepository"
class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"
p:dataSource-ref="dataSource"
p:transactionManager-ref="transactionManager"
p:lobHandler-ref="lobHandler"/>

One of the properties on the repository that can be set is isolationLevelForCache. I found that setting this value to ISOLATION_READ_UNCOMMITTED helped my deadlock issue.

     <bean id="jobRepository"
class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean"
p:dataSource-ref="dataSource"
p:transactionManager-ref="transactionManager"
p:isolationLevelForCache="ISOLATION_READ_UNCOMMITTED"
p:lobHandler-ref="lobHandler"/>

Whether or not this is the solution to use come production time is yet to be seen. For now, I surmise this works because the app is starting enough jobs at the same time, that the framework is ready to proceed with one before the insert to the database table is formally committed.

No comments:

Post a Comment