Oracle Error on creating new Batch Job [BATCH-2475]

[spring-projects-issues]

Peter Schäfer opened BATCH-2475 and commented

When launching a Batch Job, the following exception is thrown:
2016-02-18 03:20:01.686 ERROR 16635 --- [nio-8082-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.CannotSerializeTransactionException: PreparedStatementCallback; SQL [INSERT into 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
] with root cause

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

See attached stack trace.

The error is hard to reproduce.
We tried the following work-around:
<batch:job-repository id="jobRepository"
...isolation-level-for-create="READ_COMMITTED"
... />

with little success.

---

Affects: 3.0.6

Reference URL: https://stackoverflow.com/questions/22364432/spring-batch-ora-08177-cant-serialize-access-for-this-transaction-when-running

Attachments:

• StackTrace.log (10.31 kB)

6 votes, 11 watchers

[spring-projects-issues]

Peter Schäfer commented

Hello Guys,

would you please answer this bug report.
I pretty sure I´m not the only one having this problem...

Best, Peter

[spring-projects-issues]

Michael Minella commented

What additional information can you provide about the use case? I see jobs are being launched from servlets. What more can you tell me about the app and how transactions are handled within the app?

[spring-projects-issues]

Peter Schäfer commented

Spring Batch is running as a standalone application.
Jobs are launched through Spring Batch Admin,
or by http requests like this:

curl -Ld 'jobParameters=...' http://...:8080/jobs/SomeJob.json

Transactions are handled in a standard fashion, with method annotations @Transactional.
However, the error occurs before the job is launched.

Here are a few excerpts from our configuration file:

<aop:aspectj-autoproxy proxy-target-class="true" />
<context:annotation-config />

...
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="..." />
<property name="url" value="..." />
<property name="username" value="..." />
<property name="password" value="..." />
</bean>
　
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
p:dataSource-ref="dataSource" p:persistenceUnitName="...">
<property name="jpaVendorAdapter">
<bean p:generateDdl="false" p:showSql="false"
class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
</property>
<property name="persistenceXmlLocation" value="classpath:/META-INF/batch/persistence.xml" />
</bean>
...
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
p:entityManagerFactory-ref="entityManagerFactory" />
<tx:annotation-driven proxy-target-class="true" />
...

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

[spring-projects-issues]

Michael Minella commented

Where are you using @Transactional as it's known to not play well with batch jobs?

[spring-projects-issues]

Peter Schäfer commented

Usually with ItemReaders and ItemWriters, like:

@Override
@Transactional
public void open(Serializable checkpoint) { ...

@Override
@Transactional
public void writeItems(List<Object> items) throws Exception { ...

@Override
@Transactional
public void close() throws Exception { ...

What would be a better way of handling transactions?

[spring-projects-issues]

Michael Minella commented

Spring Batch handles the transactions automatically so you don't need to do anything. Using @Transactional on batch artifacts like that interferes with our transaction handling. Please remove them and see if that addresses your issue.

[spring-projects-issues]

Peter Schäfer commented

I'll give it a try ...

Thanks.

[spring-projects-issues]

Peter Schäfer commented

Tried to remove all @Transactional annotations.
The problem still persists...

The error is easy to reproduce on a newly set-up repository
(with an Oracle 12 DB).

Some people recommend to modify the "initrans" settings - no success, however.

[spring-projects-issues]

Peter Schäfer commented

None of the recommended work-arounds works reliably.

The problem can be reproduced on newly created database schemes,
and it somehow disappears after a number of unsuccessful attempts.
Which is not very satisfactory :-(

Any more ideas?

[spring-projects-issues]

Sebastian Droeppelmann commented

We circumvented the error by using two datasources, one for spring batch related commits, one for the actual batch process. Then the error went away. It has to do something with the oracle serialization (who would have guessed :P ) But I found more accounts on this problem (SO) and also encountered it myself. The above mentioned way was the only reliable way to fix this.

[spring-projects-issues]

Mz commented

@sdroeppel I tried this solution and it works randomly. Do i need just change the datasource for jobrepository or i need to change it for jobExplorer too? Does it need to have separate transaction manager too?

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

[spring-projects-issues]

Kun liu commented

We are bugged by the same error at the moment. Any updates?

[spring-projects-issues]

Yavdhesh commented

I am stuck with the same problem

 

@Bean
public JobRepository createJobRepository() throws Exception {
	LOG.info("maayne aapana JobRepository  maay");
	LOG.info("Job Repository being created and isolationLevelForCreate is set to = " + isolationLevelForCreate);
	JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
	factory.setDataSource(primaryDataSource());
	factory.setTransactionManager(transactionManager());
	LOG.info("Ye transaction manager object hai" + transactionManager());
	factory.setIsolationLevelForCreate(isolationLevelForCreate);
	factory.setMaxVarCharLength(1000);
	factory.setDatabaseType("ORACLE");
	return factory.getObject();
}

[spring-projects-issues]

Raj Kumar Gupta commented

I have got workaround for this issue.

Follow below step.

1. [enter link description here][Schema Creation Script] manually create table in your database.
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.