Multiple jobs launched in parallel produce database errors

[Sax388]

I'm having a spring boot application where several quartz triggers call spring batch jobs.
When they are called in parallel (multiple quartz triggers firing at once I'm getting the error:

at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
    VALUES (?, ?, ?, ?)\n]; ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during write.
  Hint: The transaction might succeed if retried.

I then used

  @Bean
  public JobLauncher asyncJobLauncher() throws Exception {
    TaskExecutorJobLauncher jobLauncher = new TaskExecutorJobLauncher();
    jobLauncher.setJobRepository(jobRepository);
    jobLauncher.setTaskExecutor(new SimpleAsyncTaskExecutor());
    jobLauncher.afterPropertiesSet();
    return jobLauncher;
  }

to launch the job and then it works better (I think) but I actually need to wait for the completion and I can't figure out how to do that here. I tried to use a JobListener with afterJob but I need objects that would be too cumbersome to unravel into jobParameters so that's why I want to wait.

[fmbenhassine]

The default isolation level of transactions on the job repository is ISOLATION_SERIALIZABLE, which is quite aggressive. Try to lower it to ISOLATION_READ_COMMITTED, which should fix your issue.

[Sax388]

Thank you for your answer! This would indeed prevent those conflicts I suppose, but in my case I don't feel the concurrency is that high that performance should be valued higher than the gained accuracy (by using a higher isolation level). Can't I instead adjust the retry mechanism in case of (presumably rare) conflicts?

[fmbenhassine]

Sure you can. However, you should do the retry manually, there is no built-in retry support in the job launcher provided OOTB by Spring Batch. I remember I answered a similar question on StackOverflow with a complete example, here it is: https://stackoverflow.com/a/55137314/5019386.

I will let you close this discussion if you think I answered your question, otherwise let me know if you need more support. I would love to help!