oracle Spring Batch ORA-08177：运行单个作业时无法序列化此事务的访问，SERIALIZED 隔离级别

Spring Batch ORA-08177: can't serialize access for this transaction when running single job, SERIALIZED isolation level

提问by padis

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

我在 Spring Batch 中的 JobRepository 上使用 SERIALIZED 隔离级别收到此异常：

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

; 嵌套异常是 java.sql.SQLException: ORA-08177: 无法序列化此事务的访问

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.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.

当只运行一项工作时，没有其他并行。当我将 JobRepository 的隔离级别更改为 ISOLATION_READ_COMMITTED 时，异常消失了。

What is the reason of this exception?

这个异常的原因是什么？

回答by Luca Basso Ricci

From official doc - 4.3.1

来自官方文档 - 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.

该方法的默认隔离级别是 SERIALIZABLE，它非常激进：READ_COMMITTED 也能正常工作；如果两个进程不太可能以这种方式发生冲突，则 READ_UNCOMMITTED 会很好。但是，由于对 create* 方法的调用非常短，所以只要数据库平台支持，SERIALIZED 不太可能引起问题。

回答by ingchristianreyes

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

我遇到了同样的问题，在 jobRepository 级别有效隔离是关键，这是一个对我有用的代码示例：

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

回答by M.P. Korstanje

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.

使用序列化事务时，您需要根据Oracle Docs增加表上的 initrans 参数。要处理序列化事务，这需要 3 个或更多。

alter table BATCH_.... INITRANS 3

回答by grbonk

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

我们已经尝试将 INI_TRANS 提升到 100，但我们仍然遇到问题

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

我发现这篇文章建议将 ROWDEPENDENCIES 添加到表的创建中。

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

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

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

对于 INI_TRANS 和现在 ROWDEPENDENCIES 的我来说，Serialized 的异常已经消失了。

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.

更新：事实证明这不是一个完美的解决方案。我们确实在夜间发生了一个此 SERIALIZED 异常事件。现在好多了，因为我们在一次失败之前运行了 100 次，但似乎使用 ROWDEPENDENCIES 还不是一个完整的解决方案。

回答by Raj Kumar Gupta

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_EXECUTIONand BATCH_JOB_EXECUTION_PARAMStable. (don't forget to commit)
3. Error solved. enjoy.

1. 在您的数据库中手动创建表（链接）。
2. 在BATCH_JOB_INSTANCE,BATCH_JOB_EXECUTION和BATCH_JOB_EXECUTION_PARAMS表中插入一些虚拟记录。（不要忘记提交）
3. 错误已解决。请享用。

回答by Ramesh Elworthy

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

将 database.maximumPoolSize 大小从 3 增加到 5 解决了该错误。

回答by Bajal

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

我能够通过添加如下所示的 isolationLevelForCreate 来解决此错误：

<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>