多个Spring批处理作业并发执行导致死锁

Question

我使用的是spring Batch4.0.0、spring Boot2.2.0、java JDK12.0.2、db sql server 2016。

null

ALTER TABLE [OWN].[BATCH_JOB_EXECUTION_SEQ]
ADD CONSTRAINT [PK_BATCH_JOB_EXECUTION_SEQ] PRIMARY KEY CLUSTERED ([ID] ASC);

ALTER TABLE [OWN].[BATCH_JOB_SEQ]
ADD CONSTRAINT [PK_BATCH_JOB_SEQ] PRIMARY KEY CLUSTERED ([ID] ASC);

ALTER TABLE [OWN].[BATCH_STEP_EXECUTION_SEQ]
ADD CONSTRAINT [PK_BATCH_STEP_EXECUTION_SEQ] PRIMARY KEY CLUSTERED ([ID] ASC);

    @Override
    protected JobRepository createJobRepository() throws Exception {
        JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
        factory.setDatabaseType(DATABASE_TYPE);
        factory.setIsolationLevelForCreate(ISOLATION_REPEATABLE_READ);
        factory.setDataSource(dataSource);
        factory.setTransactionManager(getTransactionManager());
        factory.setTablePrefix(TABLE_PREFIX);
        factory.afterPropertiesSet();
        return factory.getObject();
    }

    @Override
    protected JobExplorer createJobExplorer() throws Exception {
        JobExplorerFactoryBean jobExplorerFactoryBean = new JobExplorerFactoryBean();
        jobExplorerFactoryBean.setDataSource(dataSource);
        jobExplorerFactoryBean.afterPropertiesSet();
        jobExplorerFactoryBean.setTablePrefix(TABLE_PREFIX);
        return jobExplorerFactoryBean.getObject();
    }

Answer 1

我已经找到了解决方案，并为任何有同样问题的人发布；我从来没有找到一个完整和全面的解决方案，所以我想解释如何解决它，为什么它会发生，让我们开始。此死锁是由BATCH_STEP_EXECUTION_SEQ、BATCH_JOB_EXECUTION_SEQ和BATCH_JOB_SEQ表中的update和delete引起的，但为什么会发生这种情况呢？也许是因为事情没有得到正确的管理，但真正的问题是sql server从sql server 2012开始使用序列，所以spring batch不得不使用这些表来管理序列，对于那些使用比2012更旧版本的sql server的人来说，我没有解决方案，本指南仅适用于2012年的版本。

1.在数据库中创建序列。

注意从一个尚未处理的ID开始序列，否则，如果从1开始，则必须清理SPRING表和可能使用作业ID的表。

DROP TABLE [OWN].[BATCH_JOB_SEQ]
GO


DROP TABLE [OWN].[BATCH_JOB_EXECUTION_SEQ]
GO


DROP TABLE [OWN].[BATCH_STEP_EXECUTION_SEQ]
GO


CREATE SEQUENCE [OWN].[BATCH_JOB_SEQ] 
 AS [bigint]
 START WITH 1000
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE 
GO


CREATE SEQUENCE [OWN].[BATCH_JOB_EXECUTION_SEQ] 
 AS [bigint]
 START WITH 1000
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE 
GO

CREATE SEQUENCE [OWN].[BATCH_STEP_EXECUTION_SEQ] 
 AS [bigint]
 START WITH 1000
 INCREMENT BY 1
 MINVALUE -9223372036854775808
 MAXVALUE 9223372036854775807
 CACHE 
GO

@Component
public class SQLServerConfig extends DefaultBatchConfigurer{

  @Autowired
  private DataSource dataSource;

  @Value("${spring.batch.tablePrefix}")
  private String tablePrefix;

  @Override
  protected JobRepository createJobRepository() throws Exception {
      JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
      factory.setDatabaseType(DatabaseType.SQLSERVER.name());
      factory.setDataSource(dataSource);
      factory.setTransactionManager(getTransactionManager());
      factory.setTablePrefix(tablePrefix);
      factory.setIncrementerFactory(new CustomDataFieldMaxValueIncrementerFactory(dataSource));
      factory.setIsolationLevelForCreate("ISOLATION_REPEATABLE_READ");
      factory.afterPropertiesSet();
      return factory.getObject();
  }

  @Override
  protected JobExplorer createJobExplorer() throws Exception {
      JobExplorerFactoryBean jobExplorerFactoryBean = new JobExplorerFactoryBean();
      jobExplorerFactoryBean.setDataSource(dataSource);
      jobExplorerFactoryBean.afterPropertiesSet();
      jobExplorerFactoryBean.setTablePrefix(tablePrefix);
      return jobExplorerFactoryBean.getObject();
  }

}

 public class CustomDataFieldMaxValueIncrementerFactory extends DefaultDataFieldMaxValueIncrementerFactory {


  private DataSource dataSource;


  public CustomDataFieldMaxValueIncrementerFactory(DataSource dataSource) {
      super(dataSource);
      this.dataSource = dataSource;
  }


  @Override
  public DataFieldMaxValueIncrementer getIncrementer(String incrementerType, String incrementerName) {
      DatabaseType databaseType = DatabaseType.valueOf(incrementerType.toUpperCase());
      DataFieldMaxValueIncrementer dataFieldMaxValueIncrementer = null;
      if (databaseType == DatabaseType.SQLSERVER) {
          dataFieldMaxValueIncrementer =  new CustomSqlServerMaxValueIncrementer(dataSource, incrementerName);
      } else {
          dataFieldMaxValueIncrementer = super.getIncrementer(incrementerType, incrementerName);
      }
      return dataFieldMaxValueIncrementer;
  }
}

仅此而已，现在运行良好！！！

我希望它对某人有用。