OptimisticLockingFailureException in spring batch

Question

I am facing org.springframework.dao.OptimisticLockingFailureException: Attempt to update step execution id=xxx with wrong version (0), where current version is 0 in my App. It was working and recently it started throwing this exception whenever any spring batch job is started (we use Spring batch for bulk import from excel and file export from DB, There are 6 total jobs)

The exception is thrown at the very first step and it is only occurring since yesterday as it was working fine before. The same code works in different environments.

Digging deeper to spring batch source code, I found that it is trying to update the row with step_execution_id and version in where clause but no row is updated. Then it throws this exception to notify if there is any concurrent modification. But the version which it is trying to update is 0 and current version is 0 as well. As many people have asked this question but there is a difference in updating version and current version, and in my case it's the same.

I am using spring batch 3.0.8 and Hibernate 5.4.1.

I've also tried clearing spring batch tables. Tried restarting, redeploying etc.

P.S - Pardon my English.

Answer 1

OptimisticLockingFailureException occurs when 2 jobs are running against different dataservers at the same time. Looks like the following BATCH tables are created as replicated tables.

1. BATCH_JOB_EXECUTION
2. BATCH_JOB_EXECUTION_CONTEXT
3. BATCH_JOB_EXECUTION_PARAMS
4. BATCH_JOB_EXECUTION_SEQ
5. BATCH_JOB_INSTANCE
6. BATCH_JOB_SEQ
7. BATCH_STEP_EXECUTION
8. BATCH_STEP_EXECUTION_CONTEXT
9. BATCH_STEP_EXECUTION_SEQ

The job sequence id and execution ids are created by incrementing the current values in BATCH_JOB_EXECUTION_SEQ and BATCH_JOB_SEQ table respectively. This will cause problems when you have jobs running against different dataservers at the same time, as two job can technically have the same job execution id.

In our application, we faced the same issue. JOB1 & JOB2 are started at the same time. JOB1 is started against DATASERVER1 and JOB2 is started against DATASERVER2 at the same time. In our case, both JOB1 & JOB2 used same execution id 20000002345 as evident from inconsistent data in DATASERVER1 and DATASERVER2. This resulted in JOB1 failing as the version in the step table has changed midway in DATASERVER2. We got the below exception.

Exception: org.springframework.dao.OptimisticLockingFailureException: Attempt to update step exception id=20000002345 with wrong version (1), where current version is 2

JOB2 is also failed due to duplicate step execution id.

Exception: Caused by: org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; Duplicate entry '1' for key 1; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '1' for key 1

Solution: We requested DBA to turn off replication of BATCH tables (above 9 tables) on all dataservers (DATASERVER1, DATASERVER2, DATASERVER3, etc). This solves the problems. After we turned off the replication, this issue never occurs.