DeadlockLoserDataAccessException in Spring Batch

Question

I am struggling trying to find a solution to this and I'm hoping that someone out there can help.

We have a Spring/Hibernate/Wicket/Tomcat webapp. We use Spring Batch for executing jobs in the background. Some execute every minute and check a database table in an external system to see if there are new records. So there are several jobs (maybe 8 or so) that are executing on some fixed interval. A couple of those jobs we have to do some manual queries to ensure there isn't a second one running at the same time.

The problem is that we are intermittently getting dead lock exceptions when Spring Batch is trying to update the job execution status, or some other framework state in the database which is SQL Server. The job state is then hung in whatever status it was in at that moment. So the jobs that ensure they only have one instance running at a time end up never running because it appears there is a job instance still running.

I'm considering moving to an in memory hsqldb database for the Spring Batch JobRepository only however, that may come with it's own set of problems so I'd like to at least see what others have done to address this.

EDIT Also one thing I'm unsure about is whether retry logic will handle such a thing. I know it works for user code inside of steps but I'm unsure whether the database activity the framework performs between steps will be handled with retry logic. If someone could clarify that I would appreciate it.

I'll post my stacktrace and spring config below. We are using spring-batch 3.0.7-RELEASE, spring-core 4.2.6.RELEASE. Thanks in advance for the help!

@Configuration
@EnableScheduling //Enables the @Scheduled annotation
@EnableBatchProcessing
public class BatchConfig implements BatchConfigurer
{
    @Autowired 
    private PlatformTransactionManager transactionManager;

    @Autowired
    private JobRepository jobRepository;

    @Autowired
    private DataSource dataSource;

    private @Value("${batch.maxPoolSize}") String maxPoolSize;
    private @Value("${batch.corePoolSize}") String corePoolSize;
    private @Value("${batch.queueCapacity}") String queueCapacity;

    @Bean
    public JobOperator jobOperator() throws Exception
    {
        SimpleJobOperator jobOperator = new SimpleJobOperator();
        jobOperator.setJobExplorer(getJobExplorer());
        jobOperator.setJobRepository(getJobRepository());
        jobOperator.setJobRegistry(jobRegistry());
        jobOperator.setJobLauncher(getJobLauncher());
        return jobOperator;
    }

    @Primary
    @Bean
    @Override
    public JobLauncher getJobLauncher() throws Exception
    {
        SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
        jobLauncher.setJobRepository(getJobRepository());
        jobLauncher.setTaskExecutor(asyncJobTaskExecutor());//Needed for launching jobs from webapp
        return jobLauncher;
    }

    @Bean
    public ThreadPoolTaskExecutor asyncJobTaskExecutor()
    {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setMaxPoolSize(Integer.valueOf(maxPoolSize));
        executor.setCorePoolSize(Integer.valueOf(corePoolSize));
        executor.setQueueCapacity(Integer.valueOf(queueCapacity));
        return executor;
    }

    @Bean
    public JobLauncher syncJobLauncher() throws Exception
    {
        SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
        jobLauncher.setJobRepository(getJobRepository());
        jobLauncher.setTaskExecutor(new SyncTaskExecutor());//Needed for launching jobs from quartz if you want to ensure more than one job doesn't execute at a time
        return jobLauncher;
    }

    @Bean
    public JobRegistry jobRegistry()
    {
        return new MapJobRegistry();
    }

    @Bean
    @Override
    public JobExplorer getJobExplorer() throws Exception
    {
        JobExplorerFactoryBean jobExplorerFactoryBean = new JobExplorerFactoryBean();
        jobExplorerFactoryBean.setDataSource(this.dataSource);
        jobExplorerFactoryBean.afterPropertiesSet();
        return jobExplorerFactoryBean.getObject();
    }

    @Override
    public JobRepository getJobRepository() throws Exception
    {
        JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
        factory.setDataSource(dataSource);
        factory.setTransactionManager(transactionManager);
        factory.afterPropertiesSet();
        return  factory.getObject();
    }

    @Bean
    public JobService jobService() throws Exception
    {
        SimpleJobServiceFactoryBean factory = new SimpleJobServiceFactoryBean();
        factory.setJobRepository(jobRepository);
        factory.setJobLauncher(getJobLauncher());
        factory.setJobLocator(jobRegistry());
        factory.setDataSource(dataSource);
        factory.setJobExplorer(getJobExplorer());
        factory.setTransactionManager(transactionManager);
        factory.afterPropertiesSet();
        return factory.getObject();
    }

    @Bean
    public JobListener jobListener()
    {
        return new JobListener();
    }

    @Override
    public PlatformTransactionManager getTransactionManager() throws Exception
    {
        return transactionManager;
    }
}

Here's an example error. It's not always in the exact same spot but this one seems to be the most prominent.

2017-05-28 02:35:00,975 ERROR [asyncJobTaskExecutor-5]  o.s.b.c.j.AbstractJob [AbstractJob.java:335] Encountered fatal error executing job
org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [UPDATE BATCH_JOB_EXECUTION set START_TIME = ?, END_TIME = ?,  STATUS = ?, EXIT_CODE = ?, EXIT_MESSAGE = ?, VERSION = ?, CREATE_TIME = ?, LAST_UPDATED = ? where JOB_EXECUTION_ID = ? and VERSION = ?]; Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is java.sql.SQLException: Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:645) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:932) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao.updateJobExecution(JdbcJobExecutionDao.java:224) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.repository.support.SimpleJobRepository.update(SimpleJobRepository.java:162) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at sun.reflect.GeneratedMethodAccessor625.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_40]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_40]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at com.sun.proxy.$Proxy75.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor625.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_40]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_40]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) ~[spring-aop-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at com.sun.proxy.$Proxy75.update(Unknown Source) ~[na:na]
    at org.springframework.batch.core.job.AbstractJob.updateStatus(AbstractJob.java:422) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:301) ~[spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135) [spring-batch-core-3.0.7.RELEASE.jar:3.0.7.RELEASE]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_40]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_40]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_40]
Caused by: java.sql.SQLException: Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368) ~[jtds-1.2.4.jar:1.2.4]
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820) ~[jtds-1.2.4.jar:1.2.4]
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258) ~[jtds-1.2.4.jar:1.2.4]
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632) ~[jtds-1.2.4.jar:1.2.4]
    at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584) ~[jtds-1.2.4.jar:1.2.4]
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546) ~[jtds-1.2.4.jar:1.2.4]
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:506) ~[jtds-1.2.4.jar:1.2.4]
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105) ~[c3p0-0.9.1.2.jar:0.9.1.2]
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:873) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:866) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:629) ~[spring-jdbc-4.2.6.RELEASE.jar:4.2.6.RELEASE]
    ... 33 common frames omitted

Publication

I'm surprised and disturbed by the lack of support from the spring community on this. My amqp questions always get responded to the first day. This makes me question my confidence in spring batch going forward. – Publication

Do you use page locks? I've had some problems with locks on informix which uses page locks for retrieving data. Page locks locks data which aren't accessed for performence reason... – Sigismund

@JoshChappelle Have you had any luck with this? – Samalla

I believe I created a JobRepository implementation that wrapped another repository and added retries for the specific exceptions I was getting. Havent had the problem with the job state since. – Publication

Answer 1

I'm going to answer my own question since a couple of people have asked what I did to fix this. I don't work for the same company so I don't have access to code but here's a summary of what I remember doing.

Create an implementation of JobRepository that wraps another JobRepository and provides retries for the specific exceptions that are causing problems.

Some pseudo code:

class RetryingJobRepository implements JobRepository
{
  private JobRepository delegate;

  public RetryingJobRepository(JobRepository delegate, RetryTemplate retryTemplate)
  {
    this.delegate = delegate;
  }

  public JobExecution createJobExecution(JobInstance jobInstance, JobParameters jobParameters, String jobConfigurationLocation)
  {
    retryTemplate.execute(context -> {
      delegate.createJobExecution(jobInstance, jobParameters, jobConfigurationLocation);
    });
  }

  //...Do the same pattern for other JobRepository methods here
}

You can probably do this with aspect oriented programming or with annotations. I prefer explicit code like above. But it's up to you. The idea is the same however you choose to implement it.

I hope this helps others. I also hope the spring batch developers provide a solution to this out of the box or at least some better guidelines on how to avoid it.

Publication