search

spring-projects / spring-batch

Spring Batch is a framework for writing batch applications using Java and Spring
http://projects.spring.io/spring-batch/
Apache License 2.0
2.73k stars 2.35k forks source link

SQLTransactionRollbackException: transaction rollback: serialization failure #3904

Closed govardhanaraoganji closed 3 years ago

govardhanaraoganji commented 3 years ago

Team, I am facing the same issue in the reference(in the below). I have a job and I want to run at the same time with different contexts. So EOD, it will fulfill a different purpose but same logic. It is working fine when I schedule job at different times and also when I run individually.

I have tried the below ways,

with ISOLATION_READ_COMMITTED on my job repository
@transactional(isolation=Isolation.READ_COMMITTED) on my job and service.
Parsing the uniqueid in job params to make my job unique(after looking into JdbcJobInstanceDao).
Making my job bean as a prototype.

I am using the, spring-boot-starter-batch: 2.3.7.RELEASE spring-boot-starter-parent: 2.3.7.RELEASE hsqldb: 2.5.1

ImportScheduleConfiguration:

Which will schedule the job with multiple cron expressions.

ImportJobProcessingService:

Which will launch the job.

Ref of stack trace,

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:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:922)
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:120)
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:144)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:181)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy222.createJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:137)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy224.run(Unknown Source)
at com.job.ImportJobProcessingService.startBatchImport(ImportJobProcessingService.java:53)
at com.job.ImportScheduleConfiguration.lambda$configureTasks$1(ImportScheduleConfiguration.java:77)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:264)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
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 com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
... 42 common frames omitted
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.handleAbortTransaction(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 48 common frames omitted

Originally posted by @govardhanaraoganji in https://github.com/spring-projects/spring-batch/issues/1230#issuecomment-838711447

fmbenhassine commented 3 years ago

I have tried the below ways,

with ISOLATION_READ_COMMITTED on my job repository @transactional(isolation=Isolation.READ_COMMITTED) on my job and service. Parsing the uniqueid in job params to make my job unique(after looking into JdbcJobInstanceDao). Making my job bean as a prototype.

You did not mention if you have set hsqldb.tx=mvcc in your connection URL as mentioned by Michael in the issue you linked. So please give that a try and let us know (here is an example). Otherwise, please provide a minimal example that reproduces the problem to be able to investigate the issue in an efficient way.

govardhanaraoganji commented 3 years ago

@benas, I am using embedded job repository. So do I still need to provide the hsqldb.tx=mvcc? I assume, it will use the configuration to create a datasource(as mentioned in your example).

fmbenhassine commented 3 years ago

I don't know if hsqldb.tx=mvcc is the default or not so I would add it explicitly in the connection string to be sure. Michael has confirmed that there is no issue when using hsqldb.tx=mvcc with the isolation set to READ_COMMITTED on the job repository. So I can't really validate that this is a bug in Spring Batch without reproducing the problem.

So please ask your question on StackOverflow and provide a minimal complete example. If we validate the bug, then we will re-open this issue for further investigation. Thank you.

govardhanaraoganji commented 3 years ago

Thank you @benas for your support. After creating DataSource(with ...hsqldb.tx=mvcc), I got the Could not increment identity... error, So I have changed the isolation level to ISOLATION_REPEATABLE_READ.

I have done my final testing. I am able to trigger the job multiple times after using custom beans like DataSource(with ...hsqldb.tx=mvcc), JobRepository(with ISOLATION_REPEATABLE_READ) and JobLauncher.

Please consider below are the improvement,

govardhanaraoganji commented 3 years ago

@benas, I'd love to work on this feature if you gives me access for repo.

fmbenhassine commented 3 years ago

I don't see any feature to implement here. As a user, you need to add hsqldb.tx=mvcc to the connection string of your HSQLDB configuration and set the isolation level to ISOLATION_ READ_COMMITTED or ISOLATION_REPEATABLE_READ in your job repository (which solved your issue as you mentioned). There is no feature to implement in Spring Batch for that.

govardhanaraoganji commented 3 years ago

I totally agree and it resolved my issues but embedded HSQL datasource is not using the hsqldb.tx=mvcc (for example) and if we bring isolation levels into a configuration like spring.batch.isolation.level=ISOLATION_REPEATABLE_READ or etc. Framework will use the configuration to create a bean.