0

My spring batch application got the error ORA-08177: can't serialize access for this transaction when I tried to execute a job. The weird thing is that I only get this error the first 3 times. And from the 4th time onwards everything worked fine.

Error when running job 1st time:

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
2023-06-02T08:15:49.607355500Z

2nd time:

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
2023-06-02T08:15:53.372471700Z

and at the 3rd trigger:

UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO BATCH_JOB_EXECUTION_CONTEXT (SHORT_CONTEXT, SERIALIZED_CONTEXT, JOB_EXECUTION_ID) VALUES(?, ?, ?)]; SQL state [72000]; error code [8177]; ORA-08177: can't serialize access for this transaction

It seems to be the same error but each time in different tables. And then everything is back to normal. Any idea? Thanks

Phuc Nguyen
  • 371
  • 1
  • 3
  • 14
  • What is the [transaction isolation level](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-2A0FDFF0-5F72-4476-BFD2-060A20EA1685) for this transaction? You may check possible solutions in [Serializable Isolation Level](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html#GUID-8DA9A191-4CA3-4B1A-995F-4B17471C2738) section. – astentx Jun 02 '23 at 08:31
  • Oracle does not typically support serialized transaction isolation: you're most likely seeing this error because records you are trying to modify are being modified by some other transaction after yours starts, and you have to wait for them to rollback. You application would need to handle the error and restart the transaction, or (ideally) not use this isolation level in the first place as it is generally not necessary with Oracle. – pmdba Jun 02 '23 at 10:09
  • Check this: https://stackoverflow.com/questions/48211271/cannotserializetransactionexception-in-spring-batchs-simplejobrepository – Mahmoud Ben Hassine Jun 05 '23 at 07:00

0 Answers0