Spring批处理死锁-无法增加标识；嵌套的异常是com。微软sqlserver。jdbc。SQLServerException

Question

我们正在从Oracle DB迁移到Azure SQL Server，用于我们的Spring批处理应用程序。

我在尝试同时执行两个不同的作业时遇到以下错误，这两个作业更新不同的表，但使用相同的公共批处理表

原因：org。springframework。刀。DataAccessResourceFailureException:无法增加标识；嵌套的异常是com。微软sqlserver。jdbc。SQLServerException:事务（进程ID 167）在另一个进程的锁资源上被死锁，并被选为死锁受害者。重新运行事务。在org。springframework。jdbc。支持递增的。SqlServerMaxValueIncrementer。getNextKey（SqlServerMaxValueIncrementer.java:124）~[bat-applybatch-jobs-2.2.12-SNAPSHOT.jar:？]在org。springframework。jdbc。支持递增的。AbstractDataFieldMaxValueIncrementer。nextLongValue（AbstractDataFieldMaxValueIncrementer.java:125）

我的作业存储库配置

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

数据库死锁

<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>

尝试过：

<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" />

我已经创建了如下突出显示的表格

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)
);

有什么问题？我该怎么解决这个问题？

更新：工作定义

<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>

更新#2：我能试试这样的吗？

<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>

Answer 1

恐怕这是Spring第4批中已知的错误。x目前只计划在Spring第5批中解决，预计在几个月内不会解决：https://github.com/spring-projects/spring-batch/issues/3927

您可以通过在提交修复程序时进行局部调整来模拟修复程序：https://github.com/spring-projects/spring-batch/commit/fe911c8456bb49a69b1c84c78c0a0e0fdf224803，即。

• 调整模式以包含序列
• 更改用于构建JobRepository的DataFieldMaxValue增量工厂。

我认为后者不适用于纯XML配置。关于这个问题的讨论包含了一些如何在Java中实现的提示：https://github.com/spring-projects/spring-batch/issues/1448

具体的自定义取决于您已经拥有的自定义，但它至少应该大致如下所示。您可以使用BatchConfigrer应用增量器工厂：

@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();
    }
  };
}

哪里

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
  }
}

和SqlServerSequenceMaxValueExcmenter应该是来自提交的增量。