30

I am getting this exception with SERIALIZED isolation level on JobRepository in Spring Batch:

org.springframework.dao.CannotSerializeTransactionException: PreparedStatementCallback; SQL [INSERT into DATAFEED_APP.BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction

; nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:269)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:872)
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:105)
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:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy27.createJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:124)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:117)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy61.run(Unknown Source)

when runing only one job, nothing else in parallel. When I change isolation level for JobRepository to ISOLATION_READ_COMMITTED the exception is gone.

What is the reason of this exception?

padis
  • 2,314
  • 4
  • 24
  • 30

10 Answers10

9

From official doc - 4.3.1

The default isolation level for that method is SERIALIZABLE, which is quite aggressive: READ_COMMITTED would work just as well; READ_UNCOMMITTED would be fine if two processes are not likely to collide in this way. However, since a call to the create* method is quite short, it is unlikely that the SERIALIZED will cause problems, as long as the database platform supports it.

Luca Basso Ricci
  • 17,829
  • 2
  • 47
  • 69
  • When would it cause problem then? I am having then problem SERIALIZABLE – padis Mar 14 '14 at 20:40
  • SERIALIZABLE isolation level can (but non necessary) be the cause the problem; with ISOLATION_READ_COMMITTED all is fine. Sometimes SERIALIZABLE just doesn't work. – Luca Basso Ricci Mar 15 '14 at 00:23
  • 5
    JobRepositoryFactoryBean.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED") worked for me – leeor Aug 17 '15 at 18:17
  • As I know, Oracle does not support READ_UNCOMMITTED (or WITH(NOLOCK) in SQL Server). In fact, Oracle READ_COMMITTED is more like SQL Server Snapshot mode, if enabled. SQL Server, by default, still locks tables, while Oracle (or SQL Server in Snapshot mode) creates different row versions, where previous readers still see old versions already changed. SERIALIZABLE creates a lock on the whole table(s), also on Oracle. – Erik Hart Apr 05 '16 at 13:20
  • 1
    @leeor - Could you please let me know where did you added this line ? We're not using JobRepository and JobLauncher. All the jobs are running via Tivoli – Jeff Cook Aug 08 '18 at 10:38
8

I had the same problem, and effectively isolation in jobRepository level is the key, here is an example of code that works for me:

<batch:job-repository id="jobRepository"
    data-source="dataSource" transaction-manager="transactionManager"
    isolation-level-for-create="READ_COMMITTED" table-prefix="SB_" />
ingchristianreyes
  • 81
  • 1
  • 2
8

When using serialized transactions you need to increase the initrans parameter on the table per the Oracle Docs. To handle serialized transactions this needs to be 3 or more.

alter table BATCH_.... INITRANS 3
M.P. Korstanje
  • 10,426
  • 3
  • 36
  • 58
4

Had the same issue in a Spring Batch application (Spring Boot 2.3.3). The solution was:

  1. Remove @EnableTransactionManagement from a @Configuration class where datasources are configured. (@Transactional should also be removed.)

  2. Add the following to application.yaml

    batch:
      repository:
        isolationlevelforcreate: ISOLATION_READ_COMMITTED
Paulo Merson
  • 13,270
  • 8
  • 79
  • 72
3

We have tried jacking up INI_TRANS to 100 and we were still running into issues

I found this article that suggests adding ROWDEPENDENCIES to the creation of tables.

http://www.devx.com/dbzone/Article/41591?pf=true

For me with INI_TRANS & now ROWDEPENDENCIES the exceptions for Serialized have gone away.

Update: Turns out not to be a perfect solution. We did have one event of this SERIALIZED exception happen over night. Now that's much better as we had 100s of runs before a single failure but it appears that using ROWDEPENDENCIES isn't a yet a complete solution.

grbonk
  • 609
  • 6
  • 22
2

I have got a workaround for this issue.

Follow below step.

  1. Manually create table in your database (Link).
  2. insert some dummy records in BATCH_JOB_INSTANCE, BATCH_JOB_EXECUTION and BATCH_JOB_EXECUTION_PARAMS table. (don't forget to commit)
  3. Error solved. enjoy.
Jonathan Hult
  • 1,351
  • 2
  • 13
  • 19
Raj Kumar Gupta
  • 21
  • 3
1

I was able to resolve this error by adding isolationLevelForCreate like below:

<bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
        <property name="databaseType" value="ORACLE"/>
        <property name="dataSource" ref="dataSource" />
        <property name="transactionManager" ref="transactionManager" />
        <property name="isolationLevelForCreate" value="ISOLATION_READ_UNCOMMITTED"/>
    </bean>
Bajal
  • 5,487
  • 3
  • 20
  • 25
1

I was able to track down the issue to the use of "PRIMARY KEY" qualifier in the create table statements in combination with Oracle transaction isolation level "SERIALIZABLE". One can test this with this simple SQL script:

CREATE TABLE test1 (
    test_id NUMBER(1) NOT NULL PRIMARY KEY
);

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO test1 VALUES ( 1 );
COMMIT;

Result: ORA-08177 Error.

Root cause::

The problem is not related to Spring Batch code in particular but to the way Oracle is creating tables since Version 11.2 - see here for details: https://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2).

A detailed description of the general issue can be found here: https://asktom.oracle.com/pls/apex/asktom.search?tag=isolation-level-serialization

Solution I used:

Adding "SEGMENT CREATION IMMEDIATE" to the create clause above solved the problem:

CREATE TABLE test1 (
    test_id NUMBER(1) NOT NULL PRIMARY KEY
)
SEGMENT CREATION IMMEDIATE;

Correspondingly I added the same to all "CREATE TABLE" statements for the Spring Batch job repository and things worked fine.

Christoph Grimm
  • 11
  • 1
0

Increasing the database.maximumPoolSize size from 3 to 5 solved the error.

Ramesh Elworthy
  • 19
  • 4
  • How is pool size related to it ? – Yogesh Feb 15 '20 at 07:29
  • The same worked for me as well. This is a timing bug. I believe if you have enough threads in the pool, then the timing of issuing the queries will make the timing different enough that the error situation is missed. – anjanb May 22 '20 at 04:32
  • it worked for some time but then randomly out of the blue, it fails. Need a permanent solution. – anjanb Jun 18 '20 at 14:30
0

My case when there is something happen on the DB side, re-run have the same problem.

To resolve I clean up all the table below and re-run seems fine after that.

  • BATCH_JOB_EXECUTION
  • BATCH_JOB_EXECUTION_CONTEXT
  • BATCH_JOB_EXECUTION_PARAMS
  • BATCH_JOB_INSTANCE
  • BATCH_STEP_EXECUTION
  • BATCH_STEP_EXECUTION_CONTEXT
Kenneth Lee
  • 1