如何在Spring Batch中使用Hsqldb解决此SQLTransactionRollbackException问题? [英] How can I resolve this SQLTransactionRollbackException with Hsqldb in Spring Batch?

问题描述

我正在开发一个Spring Batch应用程序，该应用程序需要定期执行作业.这是我的配置文件的一个片段，用于设置用于事务处理的内存(hsqldb)数据库.

I'm working on a Spring Batch application that needs to execute jobs periodically. Here's a fragment of my configuration file that sets up the in-memory (hsqldb) database used for transaction handling.

@Bean
public SimpleJobLauncher simpleJobLauncher() {
    SimpleJobLauncher jl = new SimpleJobLauncher();
    try {
        jl.setJobRepository(jobRepository());
    }
    catch (Exception e) {
        System.err.println("Failed to create job repository");
    }
    return jl;
}

@Bean
public JobRepositoryFactoryBean jobRepositoryFactoryBean() {
    JobRepositoryFactoryBean fb = new JobRepositoryFactoryBean();
    fb.setDataSource(dataSource());
    fb.setTransactionManager(transactionManager());
    return fb;
}

@Bean
public PlatformTransactionManager transactionManager() {
    return new DataSourceTransactionManager(dataSource());
}

@Bean
public DataSource dataSource() {
    return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.HSQL)
            .addScript("classpath:/org/springframework/batch/core/schema-hsqldb.sql")
            .build();
}

我的意图是使用作为Spring一部分分发的'schema-hsqldb.sql'脚本初始化用于管理事务的Hsql数据库.但是，当我的定期任务第一次执行时，我得到了一个冗长的堆栈跟踪，这表明将作业状态序列化到数据库时出现了问题.

My intention is to use the 'schema-hsqldb.sql' script distributed as part of Spring to initialize the Hsql database that is used to manage transactions. However, when my periodic task executes for the first time, I get a lengthy stack trace which suggests a problem serializing the job state to the database.

org.springframework.dao.ConcurrencyFailureException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; transaction rollback: serialization failure; nested exception is java.sql.SQLTransactionRollbackException: transaction rollback: serialization failure
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:69)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:975)
    at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:115)
    at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:135)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy26.createJobExecution(Unknown Source)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:125)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy28.run(Unknown Source)
    at acme.tools.batch.etl.schedulers.ETLQueueChecker.checkQueue(ETLQueueChecker.java:36)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:65)
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
    at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLTransactionRollbackException: transaction rollback: serialization failure
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:916)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:909)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
    ... 46 more
Caused by: org.hsqldb.HsqlException: transaction rollback: serialization failure
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)

有趣的是，该作业的后续调用不会发生该异常.

Interestingly, the exception doesn't occur on subsequent invocations of the job.

我猜测是发生序列化错误，因为第一次尝试进行序列化时hsql模式未正确配置.之所以不会在随后的检查中发生，可能是因为(a)事务管理者厌恶地放弃了，不再尝试处理事务，或者(b)数据库有时间采取行动了.一起，现在可以存储序列化信息.

I am guessing that the serialization error occurs because the hsql schema is not properly configured at the time that the first attempt to serialize is made. The fact that it doesn't occur on subsequent checks could be either because (a) the transaction manager has given up in disgust and isn't trying to handle transactions any more, or (b) the database has had time to get its act together and is now able to store the serialized information.

在情况(b)中，我正在处理比赛条件；在(a)情况下，初始化不足.

In case (b), I'm dealing with a race condition; in case (a), with insufficient initialization.

任何熟悉Spring Batch的人都可以告诉我这是哪两个(或提出其他解释)，并给我一些解决该问题的提示吗?

Can anyone familiar with Spring Batch tell me which of these two it is (or suggest an alternative explanation), and give me some hints on resolving the issue?

推荐答案

我不相信使用EmbeddedDatabaseBuilder允许HSQLDB所需的选项与Spring Batch一起正常工作.具体来说，您需要使用MVCC交易模式.代替使用EmbeddedDatabaseBuilder，如下配置常规的DataSource(注意URL):

I don't believe that using the EmbeddedDatabaseBuilder allows the options needed for HSQLDB to work correctly with Spring Batch. Specifically, you need to use the MVCC transaction mode. Instead of using EmbeddedDatabaseBuilder configure a regular DataSource as follows (note the URL):

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:testdb;sql.enforce_strict_size=true;hsqldb.tx=mvcc" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

这篇关于如何在Spring Batch中使用Hsqldb解决此SQLTransactionRollbackException问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！