春季批量DeadlockLoserDataAccessException
春季批量DeadlockLoserDataAccessException
提问于 2017-05-31 21:22:05
我正努力寻找解决这一问题的办法，我希望有人能帮上忙。
我们有一个Spring/Hibernate/Wicket/Tomcat。我们在后台使用Spring批处理来执行作业。有些人每分钟执行一次，并检查外部系统中的数据库表，以查看是否有新记录。因此，有几个作业(可能有8个左右)在某个固定的时间间隔上执行。这些作业中，我们需要执行一些手动查询，以确保没有第二个作业同时运行。
问题是，当Spring批处理试图更新作业执行状态或数据库中的其他框架状态(即Server )时，我们会间歇性地获得死锁异常。工作状态被挂在那个时刻的任何状态中。因此，确保一次只运行一个实例的作业最终永远不会运行，因为似乎仍然有一个作业实例在运行。
我正在考虑只为Spring迁移到一个内存中的hsqldb数据库，但是，这可能伴随着它自己的一组问题，所以我想至少看看其他人做了什么来解决这个问题。
编辑还有一件事我不确定，那就是重试逻辑是否会处理这样的事情。我知道它适用于步骤中的用户代码，但我不确定框架在各个步骤之间执行的数据库活动是否会使用重试逻辑来处理。如果有人能澄清我会很感激的。
我将在下面发布我的堆栈跟踪和spring配置。我们使用的是弹簧批3.0.7发行版，弹簧核心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回答 1
Stack Overflow用户
发布于 2019-01-04 19:47:26
我会回答我自己的问题，因为有几个人问过我做了什么来解决这个问题。我不是在同一家公司工作，所以我无法访问代码，但以下是我记得做过的事情的总结。
创建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批处理开发人员能够提供一个现成的解决方案，或者至少提供一些关于如何避免它的更好的指导方针。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44294878
复制相关文章
相似问题
腾讯云开发者
