How can I resolve this SQLTransactionRollbackException with Hsqldb in Spring Batch?

Question

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();
}

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.

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.

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

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?

Vantage

Answer 1

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>

Concession

Answer 2

If you still want to use the EmbeddedDatabaseBuilder you can add this to your SQL:

SET DATABASE TRANSACTION CONTROL MVCC;

other options are:

SET DATABASE TRANSACTION CONTROL { LOCKS | MVLOCKS | MVCC }

See here for the docs

Eggers

Answer 3

In my case, Aigeec's asnwer didn't work and because I'm loading some sql scripts with the helper methods from EmbeddedDatabaseBuilder , following Michael Minella's solution would requiere extra work and perhaps the need to add an additional dependency to execute said sql script.

To get around this, I added in application.yml a property say maxThreads and in the JobConfig.class, where I'm using the SimpleFlow with a SimpleAsyncTaskExecutor that is the cause of this issue, I am importing said property with @Value("${maxThreads:1}") such that in a normal run environment, the SimpleAsyncTaskExecutor.setConcurrencyLimit(int maxThreads) uses the value set in application.yml and in the test environment it falls back to the specified default of 1, thus no longer causing the java.sql.SQLTransactionRollbackException.

Krebs