Deadlock when batch apps are launched enmasse on SQLServer

[cppwfs]

Batch's use of sequence tables (BATCH_JOB_SEQ & BATCH_JOB_EXECUTION_SEQ) are suspeptable to a deadlock on SQL Server.

This occurs when several batch jobs are launched at the same time. The deadlock occurs on the sequence tables above.
Sample Exception:

java.lang.IllegalStateException: Failed to execute ApplicationRunner
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:813) [spring-boot-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:800) [spring-boot-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:346) [spring-boot-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1340) [spring-boot-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1329) [spring-boot-2.4.5.jar!/:2.4.5]
	at org.springframework.cloud.dataflow.composedtaskrunner.ComposedTaskRunner.main(ComposedTaskRunner.java:31) [classes!/:2.8.0-SNAPSHOT]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_282]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_282]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_282]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_282]
	at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49) [spring-cloud-dataflow-composed-task-runner-2.8.0-SNAPSHOT.jar:2.8.0-SNAPSHOT]
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:108) [spring-cloud-dataflow-composed-task-runner-2.8.0-SNAPSHOT.jar:2.8.0-SNAPSHOT]
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:58) [spring-cloud-dataflow-composed-task-runner-2.8.0-SNAPSHOT.jar:2.8.0-SNAPSHOT]
	at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:88) [spring-cloud-dataflow-composed-task-runner-2.8.0-SNAPSHOT.jar:2.8.0-SNAPSHOT]
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not increment identity; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 66) 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.AbstractIdentityColumnMaxValueIncrementer.getNextKey(AbstractIdentityColumnMaxValueIncrementer.java:114) ~[spring-jdbc-5.3.6.jar!/:5.3.6]
	at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:128) ~[spring-jdbc-5.3.6.jar!/:5.3.6]
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:113) ~[spring-batch-core-4.3.2.jar!/:4.3.2]
	at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:145) ~[spring-batch-core-4.3.2.jar!/:4.3.2]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_282]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_282]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_282]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_282]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.6.jar!/:5.3.6]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.6.jar!/:5.3.6]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:181) ~[spring-batch-core-4.3.2.jar!/:4.3.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at com.sun.proxy.$Proxy59.createJobExecution(Unknown Source) ~[na:na]
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:137) ~[spring-batch-core-4.3.2.jar!/:4.3.2]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_282]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_282]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_282]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_282]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:128) ~[spring-batch-core-4.3.2.jar!/:4.3.2]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.6.jar!/:5.3.6]
	at com.sun.proxy.$Proxy62.run(Unknown Source) ~[na:na]
	at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.execute(JobLauncherApplicationRunner.java:199) ~[spring-boot-autoconfigure-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.executeLocalJobs(JobLauncherApplicationRunner.java:173) ~[spring-boot-autoconfigure-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.launchJobFromProperties(JobLauncherApplicationRunner.java:160) ~[spring-boot-autoconfigure-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:155) ~[spring-boot-autoconfigure-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:150) ~[spring-boot-autoconfigure-2.4.5.jar!/:2.4.5]
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:810) [spring-boot-2.4.5.jar!/:2.4.5]
	... 13 common frames omitted
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:767) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:710) ~[mssql-jdbc-8.4.1.jre8.jar!/:na]
	at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120) ~[HikariCP-3.4.5.jar!/:na]
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[HikariCP-3.4.5.jar!/:na]
	at org.springframework.jdbc.support.incrementer.AbstractIdentityColumnMaxValueIncrementer.getNextKey(AbstractIdentityColumnMaxValueIncrementer.java:111) ~[spring-jdbc-5.3.6.jar!/:5.3.6]
	... 49 common frames omitted

SQLServer now supports Sequence. Coverting to using sequences resolves this deadlock issue.

[fmbenhassine]

We are planning to replace the usage of tables with real sequences for SQLServer in v5, see #1448 (comment).

Closing this as duplicate of #1448.

[jvalkeal]

@benas I think there has to be a fix in 4.x as well issue is reported in current version.