Multiple JobExecuions for same JobInstance In Multi-site environment

[ashish571]

Description:
We are using Spring Batch in a multi-site environment wherein each site has 2 instances so in total 4 instances of spring boot app which contains the same concerned batch job. The batch job is deployed in a Spring Boot app of which same version is deployed to both sites. Our one site is in Saint Louis, Missouri, US and another in Kansas City, Missouri, US. All instance's primary database is Saint Louis data center and fall back is Kansas City data center.
The batch jobs are scheduled to run with Spring Scheduler. We are specifying a constant JobParameter Key name and a date time value till the minute level (yyyyMMddHHmm) as JobParameter Long value e.g. 202106081845 (8 Jun 2021 6:45 PM).

We expect for the same JobParameter value, multiple job executions should result in only 1 job execution run.

Issue:
The batch job is scheduled to run every 15 mins. Most of the times, only 1 instance wins and rest instances of same batch job does not start at same time. So far so good. However, sometimes, we have observed, for same JobInstance Id, two JobExecutions (with different JobExecution Ids) start at same time. The batch job has 3 steps. We observed, for one all are executed (winner) however at the same time, another job execution's one step executed and while running the second step, it found that its a duplicate of another job execution and it threw JobRestartException. The JobRestartException is fine but the issue is it occurred too late and the first step corrupted the database data.

Environment:
Spring Batch version: 4.2.1.RELEASE, Java version: 1.8, database: Oracle 11g

Steps to reproduce:
Deploy the spring boot app which has a spring batch job on 2 sites (at least) with 2 instances on each site. Schedule to run them at exactly same time with Spring scheduler. By the way, the issue occurs sometimes only and not all the times.

Expected behavior:
Only 1 job instance should win and succeed. For all the rest 3 instance's not a single step should get executed.

[ashish571]

We found that the issue even occurs for single site multi instance scenario.
Since we wanted only 1 JobExecution for one JobInstance Id, the simplest solution we came up with is adding Unique constraint on JobExecution table for JobInstanceId.

ALTER TABLE BATCH_JOB_EXECUTION
ADD CONSTRAINT BATCH_JOB_EXECUTION_UK01 UNIQUE (JOB_INSTANCE_ID);

This fixed the issue for our requirement. This issue can be closed.

[fmbenhassine]

Since we wanted only 1 JobExecution for one JobInstance Id, the simplest solution we came up with is adding Unique constraint on JobExecution table for JobInstanceId.

@ashish571 What you wanted seems incorrect to me. Have you considered the failure scenario? In that case multiple job executions can be related to the same job instance. So adding a unique constraint on JOB_INSTANCE_ID is not correct to me: the BATCH_JOB_EXECUTION table can contain multiple records (ie job executions) for the same job instance ID (which is by design as a job instance might have multiple executions, for the failure case for instance). Adding a unique constraint on JOB_INSTANCE_ID prevents that. Do you agree?