我正在努力寻找解决方案,希望有人可以提供帮助。
我们有一个 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