Executing multiple jobs at the same time

[hailspring]

I have a process, which starts two spring-batch jobs at the same time and I get the following error message, how can I resolve this? (any transaction settings)

Job Terminated in error:
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; SQL state [72000]; error code [8177]; 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.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:604)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:789)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:847

Thanks!

[Dave Syer]

I'm not sure about the specific exception (looks a little fishy), but it should be OK if you just try again. You can use RetryOperationsInterceptor to wrap your JobLauncher or Job.

[GregA]

I've run into the same issue when running two jobs at the same time. Did Dave's suggestion of wrapping the Job or JobLauncher work? I would like details of how you configured the RetryOperationsInterceptor if it did work.

[GregA]

Well, I've found a solution without a coding or configuration change. Working with my DBA we set the INITRANS on all the Spring Batch tables to 3 (as Oracle recommends; it defaults to 1). The taskExecutor in my SimpleJobLauncher (org.springframework.scheduling.quartz.SimpleThrea dPoolTaskExecutor) has a thread count of 2 so I'll only have a max of 2 threads attempting to access any of the Spring Batch Tables at a given time.

My DBA found the following problem description on Oracle's Metalink - Note 125451.1:
You are creating transactions in JDBC to use the transaction isolation
level 'serializable'. In your code, you issue the statement
setTransactionIsolation(TRANSACTION_SERIALIZABLE). When running two
transactions, both affecting the same table, you intermittently receive
an ora-8177 "Cannot serialize access for this transaction" message.

That's how we zero'ed in on the INITRANS.

[hailspring]

Sounds like a GREAT solution GregA! Thanks for sharing with us!

The way I resolved it was by modifying transaction isolation level at spring level, but your solution proves to be more prudent. I will check with my DBA too.

Thanks!!

[jonesg]

hi all, I'd just like to post an update on this thread, and how I resolved similar issues.

I was having the same difficulties as the OP. The scenario was this: multiple spring batch processes were starting simultaneously as separate jobs via a cron schedule. This was resulting in frequent "ORA-08177: can't serialize access for this transaction" errors.

In a test environment I was able to replicate the problem, and tried incrementing the INITRANS value (to 30) on each of the batch tables. This had some success e.g. running a small number of jobs concurrently seemed to be ok, but running more than, say, 10 jobs still resulted in errors.

After much google-ing, I finally came accross this other thread on this forum: http://forum.springframework.org/showthread.php?t=59779

and the suggestion of adding the

<property name="isolationLevelForCreate" value="ISOLATION_DEFAULT"/>

property to my JobRepositoryFactoryBean definition:

<bean id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean" >
		<property name="databaseType" value="oracle" />
		<property name="dataSource" ref="triPartyAppDataSource" />
		<property name="transactionManager" ref="hibernateTransactionManager" />
		<property name="isolationLevelForCreate" value="ISOLATION_DEFAULT"/>
	</bean>

i.e. make the job repository connection "read committed" rather than "serializable".

I can now run 50 concurrent jobs (actually 50 instances of the same job) in my test environment with INITRANS set to 1 without having any ORA-08177 errors. (although I did get some errors due to swamping the Oracle TNS service).

The hibernate/jdbc connection of my job which was persisting my actual batch data, was defaulting to "read committed" so there was no need to set the hibernate property

hibernate.connection.isolation=2

Thanks
G

[ibrandt]

FYI:

We just hit this error in production using the SyncTaskExecutor. We do trigger from Quartz, but from our logs and our Quartz schedule I'm positive there was not a concurrent job running. What did happen is from a single Quartz trigger we launched one job immediately after the other. We've been doing this nightly for about a year now and just hit this error for the first time the other night.

I found this digging through the Oracle docs:

http://download.oracle.com/docs/cd/B...htm#sthref1981

Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a too recent transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block.

Oracle generates an error when a serializable transaction tries to update or delete data modified by a transaction that commits after the serializable transaction began:

ORA-08177: Cannot serialize access for this transaction

I notice what I believe is new documentation as of 2.x on the issue in 4.2.1. Transaction Configuration For the JobRepository.

Just brainstorming here... What if SimpleJobRepository caught TransientDataAccessExceptions (or higher? DataAccessException? RuntimeException?)--the parent of CannotSerializeTransactionException we got in this case--and handled them with a configurable RetryOperations/RetryPolicy? Perhaps an ExceptionClassifierRetryPolicy could be used to specify different retry policies for different subclasses of TransientDataAccessException?

Per the docs, "The default isolation level for that method is SERIALIZABLE, which is quite aggressive..."; the above could be used to make the default aggressive and durable, as well as open the door for us to handle other sorts of issues.

It also might be good to set a higher INITRANS in the Oracle sample schema creation scripts to support the default. I'm not sure if there are any similar issues with other databases?

I'll file JIRAs for each of these. [Edit: JIRAs entered for Retry and INITRANS.]

[zalym]

Hello,

We have started getting this issue in prod intermittently. The error is against the BATCH_JOB_PARAMETERS table though. I had a few questions: The two solutions suggested above do not imply the same cause, even though it solves the problem. The INITRANS solution seems to imply that its something to do with some kind of resource crunch against the DB, where as the isolationLevelForCreate solution seems to imply that its a data issue, whereby we are trying to run the same job again.

[Dave Syer]

The issue would usually only arise in relation to creating a JobExecution instance, so BATCH_JOB_INSTANCE, BATCH_JOB_PARAMETERS and BATCH_JOB_EXECUTION could all be involved. It can happen even if you are not launching the same job because by default you ask for all access to those tables to be serialized during the creation of the JobExecution. The resource crunch can simply be two threads or processes trying to launch a job (any job) very close together. You don't need particularly hight traffic for that (as Ian noticed), but it's easy to put a little delay in to work around the problem, or use one of the other suggestions (INITRANS or, more risky, ISOLATION_DEFAULT). Another solution would be to wrap the JobLauncher call in a retry declaratively.

[zalym]

Thanks Dave. That's much clearer now.

I am using Spring Batch 2.0, and I configure the job repository using the schema. In the listed values for isolation-level-for-create, I do not see ISOLATION_DEFAULT, instead I see READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ and SERIALIZABLE. Is there something that is on par?