Spring Batch 中的 DeadlockLoserDataAccessException

2024-01-11

我正在努力寻找解决方案,希望有人可以提供帮助。

我们有一个 Spring/Hibernate/Wicket/Tomcat Web 应用程序。我们使用 Spring Batch 在后台执行作业。有些每分钟执行一次并检查外部系统中的数据库表以查看是否有新记录。因此,有几个作业(可能有 8 个左右)以某个固定的时间间隔执行。对于其中的一些作业,我们必须进行一些手动查询,以确保不会同时运行第二个作业。

问题是,当 Spring Batch 尝试更新作业执行状态或数据库(SQL Server)中的某些其他框架状态时,我们会间歇性地遇到死锁异常。然后,作业状态将挂起,无论其当时处于什么状态。因此,确保一次只运行一个实例的作业最终永远不会运行,因为似乎有一个作业实例仍在运行。

我正在考虑仅为 Spring Batch JobRepository 迁移到内存中的 hsqldb 数据库,但这可能会带来它自己的一系列问题,所以我至少想看看其他人已经做了什么来解决这个问题。

EDIT我不确定的一件事是重试逻辑是否会处理这样的事情。我知道它适用于步骤内的用户代码,但我不确定框架在步骤之间执行的数据库活动是否将通过重试逻辑进行处理。如果有人能澄清我将不胜感激。

我将在下面发布我的堆栈跟踪和 spring 配置。我们使用 spring-batch 3.0.7-RELEASE、spring-core 4.2.6.RELEASE。先谢谢您的帮助!

@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;
    }
}

这是一个错误示例。它并不总是在完全相同的位置,但这一个似乎是最突出的。

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

我要回答我自己的问题,因为有几个人问我做了什么来解决这个问题。我不在同一家公司工作,所以我无法访问代码,但这里是我记得所做的事情的摘要。

创建 JobRepository 的实现,它包装另一个 JobRepository 并为导致问题的特定异常提供重试。

一些伪代码:

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
}

您可以通过面向方面的编程或注释来做到这一点。我更喜欢像上面这样的显式代码。但这取决于你。无论您选择实现什么,想法都是一样的。

我希望这对其他人有帮助。我还希望 Spring Batch 开发人员提供一个开箱即用的解决方案,或者至少提供一些关于如何避免它的更好的指南。

本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)

Spring Batch 中的 DeadlockLoserDataAccessException 的相关文章

随机推荐