Spring Batch Deadlock - Could not increment identity; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException

Question

We are in the process of moving to Azure SQL Server from Oracle DB for our Spring Batch application.

I am getting the following error while trying to execute two different jobs at the same time that updates different tables however uses the same common BATCH_ tables

Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not increment identity; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 167) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. at org.springframework.jdbc.support.incrementer.SqlServerMaxValueIncrementer.getNextKey(SqlServerMaxValueIncrementer.java:124) ~[bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:?] at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:125)

My Job Repository configuration

<job-repository id="jobRepository" isolation-level-for-create="READ_COMMITED" />

Database deadlock

<deadlock>
  <victim-list>
    <victimProcess id="process2a41675a4e8" />
  </victim-list>
  <process-list>
    <process id="process2a41675a4e8" taskpriority="0" logused="280" waitresource="RID: 6:9:24682488:29" waittime="4984" ownerId="696000712" transactionname="implicit_transaction" lasttranstarted="2021-12-29T12:18:30.153" XDES="0x29a22bc4428" lockMode="U" schedulerid="4" kpid="52760" status="suspended" spid="173" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-29T12:18:30.157" lastbatchcompleted="2021-12-29T12:18:30.153" lastattention="1900-01-01T00:00:00.153" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ServerName" hostpid="0" loginname="LoginName" isolationlevel="read committed (2)" xactid="696000712" currentdb="6" currentdbname="Database" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="unknown" queryhash="0xadc42a7474869694" queryplanhash="0x238c4f9df8a5d6cc" line="1" stmtstart="26" stmtend="146" sqlhandle="0x020000007654041849f4ffe980c136b592ccbe8260983e220000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0xadc42a7474869694" queryplanhash="0x238c4f9df8a5d6cc" line="1" stmtend="126" sqlhandle="0x0200000045a2af306ade799ae9ffa65edc0f722c526e26330000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
delete from LoginName.BATCH_STEP_EXECUTION_SEQ where ID &lt; 10899   </inputbuf>
    </process>
    <process id="process2a42d680ca8" taskpriority="0" logused="420" waitresource="RID: 6:9:24682490:8" waittime="4984" ownerId="696000707" transactionname="implicit_transaction" lasttranstarted="2021-12-29T12:18:30.153" XDES="0x2a41ae18428" lockMode="U" schedulerid="7" kpid="53280" status="suspended" spid="129" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-12-29T12:18:30.153" lastbatchcompleted="2021-12-29T12:18:30.153" lastattention="1900-01-01T00:00:00.153" clientapp="Microsoft JDBC Driver for SQL Server" hostname="ServerName" hostpid="0" loginname="LoginName" isolationlevel="read committed (2)" xactid="696000707" currentdb="6" currentdbname="Database" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
      <executionStack>
        <frame procname="unknown" queryhash="0xadc42a7474869694" queryplanhash="0x238c4f9df8a5d6cc" line="1" stmtstart="26" stmtend="146" sqlhandle="0x020000007654041849f4ffe980c136b592ccbe8260983e220000000000000000000000000000000000000000">
unknown    </frame>
        <frame procname="unknown" queryhash="0xadc42a7474869694" queryplanhash="0x238c4f9df8a5d6cc" line="1" stmtend="126" sqlhandle="0x02000000a0f1f51de77e1eefa19367c42fc9d1938c2075020000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
delete from LoginName.BATCH_STEP_EXECUTION_SEQ where ID &lt; 10898   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <ridlock fileid="9" pageid="24682488" dbid="6" objectname="162589bb-bc36-4834-8bdc-e58a2deca742.LoginName.BATCH_STEP_EXECUTION_SEQ" id="lock2a043bbcc00" mode="X" associatedObjectId="72057594071547904">
      <owner-list>
        <owner id="process2a42d680ca8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2a41675a4e8" mode="U" requestType="wait" />
      </waiter-list>
    </ridlock>
    <ridlock fileid="9" pageid="24682490" dbid="6" objectname="162589bb-bc36-4834-8bdc-e58a2deca742.LoginName.BATCH_STEP_EXECUTION_SEQ" id="lock29f5f1b7f00" mode="X" associatedObjectId="72057594071547904">
      <owner-list>
        <owner id="process2a41675a4e8" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2a42d680ca8" mode="U" requestType="wait" />
      </waiter-list>
    </ridlock>
  </resource-list>
</deadlock>

tried:

<job-repository id="jobRepository" isolation-level-for-create="READ_UNCOMMITED" />

<job-repository id="jobRepository"
                isolation-level-for-create="ISOLATION_REPEATABLE_READ" />

<job-repository id="jobRepository"
                isolation-level-for-create="SERIALIZABLE" />

I have created the tables as highlighted below

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (
ID BIGINT IDENTITY(<last Oracle sequence value>, 1)
);

CREATE TABLE BATCH_JOB_EXECUTION_SEQ (
ID BIGINT IDENTITY(<last Oracle sequence value>, 1)
);

CREATE TABLE BATCH_JOB_SEQ (
ID BIGINT IDENTITY(<last Oracle sequence value>, 1)
);

what is the issue? How do I fix this?

Update: Job Definition

<bean id="simpleStep" class="org.springframework.batch.core.step.factory.SimpleStepFactoryBean"
    abstract="true">
    <property name="transactionManager" ref="transactionManager" />
    <property name="jobRepository" ref="jobRepository" />
    <property name="startLimit" value="100" />
    <property name="commitInterval" value="1" />
</bean>

Update#2: Can I try something like this?

<bean id="informixIncrementer" class="com.bah.batch.informixsupport.InformixMaxValueIncrementerFactory"><property name="dataSource" ref="dataSource" />

<bean  id="jobRepository" class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean" isolation-level-for-create="READ_COMMITTED" table-prefix="BATCH_">
   <property name="incrementerFactory" ref="informixIncrementer"/>
</bean>

Deaminate

@mahmoud-ben-hassine - do you have suggestions? – Deaminate

I can't tell you what the issue in your case is but typically this kind of deadlock occurs because 2 parallel processes try to access resources in a different order and thus are waiting for a resource the other holds. As an example imagine a process tries to process rows A, B, C, D in that order while the other for some reason tries to do in in the order D,B,A,C. Now the first process already locked A, B and C and is trying to lock D. However the other process locked D already and is waiting for the first process to finish and release the lock on B - so now you have a deadlock. – Dehorn

In your case the problem seems to be that you have 2 processes that try to delete multiple elements from LoginName.BATCH_STEP_EXECUTION_SEQ and if this happens in a different order this can lead to a deadlock. So either try to get some order into the deletion process (e.g. by using a subquery to get an ordered list of ids to delete) or just do it sequentially (e.g. lock the table if possible). – Dehorn

Shouldn't those tables have an index on ID, otherwise you will be performing a full table scan? – Bountiful

Note you can see in the deadlock graph that one delete is holding an X lock on page 24682488 and is waiting on an U lock on page 24682490 and the other process has a U lock on page 24682488 and wants an X lock on page 24682490 – Bountiful

Answer 1

I'm afraid this is a known bug in Spring Batch 4.x that is currently only planned to be resolved with Spring Batch 5, which is not to be expected for a couple of months: https://github.com/spring-projects/spring-batch/issues/3927

You can emulate the fix by making local adjustments as in the commit of the fix: https://github.com/spring-projects/spring-batch/commit/fe911c8456bb49a69b1c84c78c0a0e0fdf224803, i.e.

• Adjust the schema to contain sequences
• Change the DataFieldMaxValueIncrementerFactory that is used to build the JobRepository.

I don't think the latter is feasible with pure XML configuration. The discussion on this issue contains some hints how it can be done in Java: https://github.com/spring-projects/spring-batch/issues/1448

The concrete customization depends on the customizations that you already have but it should work at least roughly as follows. You can apply the incrementer factory with a BatchConfigurer:

@Bean
public BatchConfigurer batchConfigurer(DataSource dataSource) {
  return new DefaultBatchConfigurer(dataSource) {
    @Override
    protected JobRepository createJobRepository() throws Exception {
      JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
      factory.setDataSource(getDataSource());
      factory.setTransactionManager(getTransactionManager());
      factory.setIncrementerFactory(new MyIncrementerFactory(getDataSource()));
      factory.afterPropertiesSet();
      return factory.getObject();
    }
  };
}

where

public class MyIncrementerFactory implements DataFieldMaxValueIncrementerFactory {

  private final DataSource dataSource;

  public MyIncrementerFactory(DataSource dataSource) {
    this.dataSource = dataSource;
  }

  @Override
  public DataFieldMaxValueIncrementer getIncrementer(String databaseType, String incrementerName) {
    return new SqlServerSequenceMaxValueIncrementer(dataSource, incrementerName);
  }

  @Override
  public boolean isSupportedIncrementerType(String databaseType)       {
    return true;
  }

  @Override
  public String[] getSupportedIncrementerTypes() {
    return null; // method should not get called anyway
  }
}

and SqlServerSequenceMaxValueIncrementer should be the incrementer from the commit.

Cartercarteret

do you mean that I need to create the following sequences BATCH_STEP_EXECUTION_SEQ, SEQUENCE BATCH_JOB_EXECUTION_SEQ, SEQUENCE BATCH_JOB_SEQ ? and add the class SqlServerSequenceMaxValueIncrementer.java ? But how do I update the class DefaultDataFieldMaxValueIncrementerFactory.java to use SqlServerSequenceMaxValueIncrementer? – Deaminate

The SqlServerSequenceMaxValueIncrementer needs to be returned by your own implementation of DataFieldMaxValueIncrementerFactory. You can then implement a BatchConfigurer by extending DefaultBatchConfigurer and overriding createJobRepository. You can use the same implementation but add the line factory.setIncrementorFactory(...) for you incrementer factory. – Cartercarteret

The comment above is quite dense. I expanded the answer with some concrete code. – Cartercarteret

I am using XML configuration for job definition - added the xml config in the question. what is needed at the XML level? – Deaminate

I found this snippet in the old reference documentation: docs.spring.io/spring-batch/docs/3.0.10.RELEASE/reference/… Instead of the BatchConfigurer, you can use that raw syntax instead of the job-repository shortcut in the XML. You need to set the property incrementerFactory to MyIncrementerFactory. – Cartercarteret

Can i try something like what I have updatee#2 in the question? - – Deaminate

Yes! Looks good to me. But I'm more acquainted with Java than XML config. – Cartercarteret

Answer 2

Basically, you need to overwrite incrementerFactory in JobRepository like so:

@Bean
public JobRepository jobRepository(DataSource dataSource, PlatformTransactionManager transactionManager) throws Exception {
    JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
    factory.setDataSource(dataSource);
    factory.setTransactionManager(transactionManager);
    factory.setIncrementerFactory(new DefaultDataFieldMaxValueIncrementerFactory(dataSource) {
        @Override
        public DataFieldMaxValueIncrementer getIncrementer(String incrementerType, String incrementerName) {
            return new SqlServerSequenceMaxValueIncrementer(dataSource, incrementerName);
        }
    });
    factory.afterPropertiesSet();
    return factory.getObject();
}

@Bean
public SimpleJobLauncher jobLauncher(JobRepository jobRepository) {
    SimpleJobLauncher simpleJobLauncher = new SimpleJobLauncher();
    simpleJobLauncher.setJobRepository(jobRepository);
    return simpleJobLauncher;
}

And here is the source code of SqlServerSequenceMaxValueIncrementer which you can include in your codebase.

Diuretic