SQL Operations should be retried rather than throwing CannotAcquireLockException.

[ramshers]

Bug description
When concurrent instances of a Job do SQL operations on same table at same time, CannotAcquireLockException should not be thrown but instead the SQL operation should be retried.

Environment
$ java -version
java version "17.0.8" 2023-07-18 LTS
Java(TM) SE Runtime Environment (build 17.0.8+9-LTS-211)
Java HotSpot(TM) 64-Bit Server VM (build 17.0.8+9-LTS-211, mixed mode, sharing)

Spring Boot Version - 3.1.3
Spring Batch Version - 5.0.3
DB - Postgres - 13.x with Isolation level READ_COMMITTED

Steps to reproduce
This is difficult to reproduce. As we can not for sure time two job instances to update the DB at same time at a precision level of milli or nano seconds.

Description
When runing the same spring batch job concurrently(i.e multiple instances of the same job are started at same time) with different identifying parms I faced -

Caused by: org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; 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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
]; ERROR: could not serialize access due to read/write dependencies among transactions
Detail: Reason code: Canceled on identification as a pivot, during conflict in checking.
Hint: The transaction might succeed if retried.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:115)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1580)

As the error above indicates this is due to Locking done by one job instance that blocks and then fails another job instance.

To resolve this, the Insert Operation (As far as I have explored.. this part of the code) has to be retried on facing CannotAcquireLockException rather than failing the whole job instance.

Similar approach should be taken when inserting in to other tables if there is possibility of facing CannotAcquireLockException.

Expected behavior
To resolve this db insert/update/delete operations should not fail on facing CannotAcquireLockException. But instead should retry a couple of times till it succeeds.

[Badii-kh]

This problème is related to the default isolation level of the JobRepository (SERIALIZABLE). In my case, passing the isolation level to READ_COMMITTED resolve the issue.

spring.batch.jdbc.isolation-level-for-create: READ_COMMITTED