spring-projects/spring-batch

ORA-08177 can't serialize access for this transaction

Closed this issue · 5 comments

prasanthks81 commented

Getting below ORA-08177 issue when multiple Spring batch jobs are triggered at the same time.

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO BATCH_JOB_EXECUTION(JOB_EXECUTION_ID, JOB_INSTANCE_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, VERSION, CREATE_TIME, LAST_UPDATED)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
]; SQL state [72000]; error code [8177]; ORA-08177: can't serialize access for this transaction

Property already added to set the Transaction isolation level as READ_COMMITTED

  • spring.batch.jdbc.isolation-level-for-create=READ_COMMITTED
  • spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_COMMITTED

Versions used

  • Spring Boot version: 3.3.2
  • Spring Batch version: 5.1.0
  • Oracle Database version: 19.14
  • JDBC Driver: ojdbc11
  • HikariCP version: 5.0.1
prasanthks81 commented

Friendly reminder - Could you please provide an update on this?

fmbenhassine commented

Indeed, the issue should be resolved if you lower the transaction's isolation level.

However, the following is incorrect:

spring.batch.jdbc.isolation-level-for-create=READ_COMMITTED
spring.datasource.hikari.transaction-isolation=TRANSACTION_READ_COMMITTED

It should be:

spring.batch.jdbc.isolation-level-for-create=ISOLATION_READ_COMMITTED
spring.datasource.hikari.transaction-isolation=ISOLATION_READ_COMMITTED

There is a sample in the docs here: https://docs.spring.io/spring-batch/reference/job/configuring-repository.html#txConfigForJobRepository

Have you tried that?

fmbenhassine commented

Closing this for now. Will re-check if more feedback is provided.

prasanthks81 commented

Sorry for the late response on this.
The suggested properties (given below) also did not help.

spring.batch.jdbc.isolation-level-for-create=ISOLATION_READ_COMMITTED
spring.datasource.hikari.transaction-isolation=ISOLATION_READ_COMMITTED

fmbenhassine commented

That should not be the case, the isolation level should be taken into account if set with a spring boot property. Are you sure these properties are taken into account? Are you using @EnableBatchProcessing or extending DefaultBatchConfiguration? Because if you do, those properties are not interpreted by boot.

Please provide a minimal example that reproduces the issue: https://github.com/spring-projects/spring-batch/blob/main/ISSUE_REPORTING.md. I would love to help, but It is very hard and inefficient to guess the issue like this on my side.