Spring Batch多任务并发执行致SQL Server元数据表死锁求助

I've run into this exact deadlock scenario with Spring Batch on SQL Server before, so let's break down what's happening and try some targeted fixes beyond what you've already attempted.

First, let's recap your setup and current attempts:

• Tech Stack: Spring Batch 4.0.0, Spring Boot 2.2.0, Java JDK 12.0.2, SQL Server 2016
• Problem: Concurrent job executions trigger deadlocks on Spring Batch metadata tables, specifically hitting this error consistently:

  Could not increment identity; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

• Attempted Fixes:
  • Added clustered primary keys to the sequence tables (BATCH_JOB_EXECUTION_SEQ, BATCH_JOB_SEQ, BATCH_STEP_EXECUTION_SEQ)
  • Overrode JobRepository and JobExplorer to set isolation level to ISOLATION_REPEATABLE_READ

Targeted Fixes to Resolve the Deadlock #

1. Switch to SQL Server's Native Sequence Support (Most Impactful Fix) #

Spring Batch's default table-based sequence approach is prone to deadlocks on SQL Server under high concurrency—it relies on row locks that can cause contention when multiple jobs try to increment the sequence at the same time. SQL Server 2016 supports native sequence objects, which are optimized for concurrent access.

First, create native sequences in your SQL Server database:

CREATE SEQUENCE OWN.BATCH_JOB_SEQ AS BIGINT START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OWN.BATCH_JOB_EXECUTION_SEQ AS BIGINT START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE OWN.BATCH_STEP_EXECUTION_SEQ AS BIGINT START WITH 1 INCREMENT BY 1;

Then update your JobRepository configuration to use these native sequences:

@Override
protected JobRepository createJobRepository() throws Exception {
    JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
    factory.setDatabaseType("SQLSERVER"); // Ensure this is set correctly
    factory.setIsolationLevelForCreate(ISOLATION_READ_COMMITTED); // We'll adjust this next
    factory.setDataSource(dataSource);
    factory.setTransactionManager(getTransactionManager());
    factory.setTablePrefix(TABLE_PREFIX);
    
    // Enable native SQL Server sequence support
    factory.setIncrementerFactory(new DefaultDataFieldMaxValueIncrementerFactory(dataSource));
    factory.setIncrementerType(DataFieldMaxValueIncrementerFactory.IncrementerType.SQLSERVER);
    
    factory.afterPropertiesSet();
    return factory.getObject();
}

2. Adjust Isolation Level to ISOLATION_READ_COMMITTED #

While REPEATABLE_READ sounds like a safer choice, SQL Server's implementation holds shared locks longer for this isolation level, which increases deadlock risk. For Spring Batch metadata operations (like sequence increments), ISOLATION_READ_COMMITTED (the default for most databases) is sufficient because these operations are atomic and don't require repeatable read guarantees.

This change is already included in the code snippet above, but make sure you're replacing ISOLATION_REPEATABLE_READ with ISOLATION_READ_COMMITTED.

3. Disable Lock Escalation on Legacy Sequence Tables #

If you're still using the table-based sequences temporarily (or if the deadlock persists), disable lock escalation on those tables. SQL Server might escalate row locks to table locks under high load, which exacerbates deadlocks:

ALTER TABLE OWN.BATCH_JOB_EXECUTION_SEQ SET (LOCK_ESCALATION = DISABLE);
ALTER TABLE OWN.BATCH_JOB_SEQ SET (LOCK_ESCALATION = DISABLE);
ALTER TABLE OWN.BATCH_STEP_EXECUTION_SEQ SET (LOCK_ESCALATION = DISABLE);

4. Tune Job Launcher Concurrency #

Don't overwhelm the database with too many concurrent job executions. Configure your JobLauncher with a thread pool that has a reasonable size (match it to your database's capacity):

@Bean
public JobLauncher asyncJobLauncher(JobRepository jobRepository) throws Exception {
    SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
    jobLauncher.setJobRepository(jobRepository);
    
    // Use a thread pool instead of unbounded async execution
    ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
    taskExecutor.setCorePoolSize(4); // Adjust based on your database's capacity
    taskExecutor.setMaxPoolSize(8);
    taskExecutor.setQueueCapacity(10);
    taskExecutor.initialize();
    
    jobLauncher.setTaskExecutor(taskExecutor);
    jobLauncher.afterPropertiesSet();
    return jobLauncher;
}

5. Diagnose Exact Deadlock Contention with SQL Server Logs #

To get to the root of the issue, enable deadlock tracing in SQL Server. This will log detailed deadlock graphs to the error log, showing exactly which tables/rows are causing contention:

DBCC TRACEON(1222, -1);

You can then analyze the deadlock graph to confirm if the issue is still with sequence tables or if another part of your batch jobs is contributing to the problem.

Final Recommendations #

The switch to native SQL Server sequences is the most critical fix here—table-based sequences are a legacy approach that doesn't play well with SQL Server's concurrency model. Combine that with adjusting the isolation level and tuning your job launcher's thread pool, and you should see the deadlocks disappear.

内容的提问来源于stack exchange，提问作者ptjack