We have multiple Spring Batch jobs each running in their own java instance using the CommandLineJobRunner. All of the jobs are started simultaneously, only read/write flat files and update the same Spring Batch metadata hosted in SQL Server. The only database involved is the Spring Batch metadata database.
When the multiple jobs are started simultaneously we get SQL deadlock exceptions. A more detailed stack trace can be found below. From the database perspective we can see that the deadlock victims were doing one of the following: Insert into BATCH_JOB_SEQ default values or Delete from BATCH_JOB_SEQ where ID < some_number.
We are utilizing a default MapJobRegistry, and either the default job repository or specifying the JobRepositoryFactoryBean. For the data source used to interact with the Spring Batch database we have tried both the DriverManagerDataSource or a DBCP2 pooling BasicDataSource both using the standard Microsoft SQL Server SQLServerDriver. I can upload more specific config files but in my testing as long as I use the SQL Server and standard Spring configuration the issues occur.
In my investigation I think the issue is due to how the default incrementer class, org.springframework.jdbc.support.incrementer.SqlServerMaxValueIncrementer, increments the job and step instance ids in conjunction with how the SQL Server database tables are constructed. The code in SqlServerMaxValueIncrementer is synchronized so if we were running all the jobs in the same Java instance this would not be an issue.
If we implement the Spring Batch metadata in a DB2 database we do not have a problem. The SQL Server implementation uses actual tables and the DB2 implementation uses sequence objects.
Has anyone run into this issue? Am I just missing something? It seems like whenever we have a problem like this it is as simple as go set setting xxx in yyy. If not, does anyone know why Spring Batch doesn’t implement sequence objects in the SQL Server implementation?
Stack trace:
[org.springframework.batch.core.launch.support.CommandLineJobRunner] - <Job Terminated in error: Could not increment identity; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.>
org.springframework.dao.DataAccessResourceFailureException: Could not increment identity;
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException:
Transaction (Process ID 74) 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)
at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:1
28)
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:108)
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:135)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
Configuration:
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:batch="http://www.springframework.org/schema/batch"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xsi:schemaLocation="
http://www.springframework.org/schema/batch
http://www.springframework.org/schema/batch/spring-batch.xsd
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
lazy-init="true">
<property name="dataSource" ref="batchPoolingDataSource" />
</bean>
<bean id="jobRegistry"
class="org.springframework.batch.core.configuration.support.MapJobRegistry" />
<bean id="jobRegistryBeanPostProcessor"
class="org.springframework.batch.core.configuration.support.JobRegistryBeanPostProcessor">
<property name="jobRegistry" ref="jobRegistry" />
</bean>
<bean id="jobRepository"
class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
<property name="databaseType" value="SQLSERVER" />
<property name="dataSource" ref="batchPoolingDataSource" />
<property name="transactionManager" ref="transactionManager" />
</bean>
<bean id="jobLauncher"
class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
<property name="jobRepository" ref="jobRepository" />
</bean>
<bean id="jobExplorer"
class="org.springframework.batch.core.explore.support.JobExplorerFactoryBean">
<property name="dataSource" ref="batchPoolingDataSource" />
</bean>
<bean id="jobOperator"
class="org.springframework.batch.core.launch.support.SimpleJobOperator">
<property name="jobExplorer" ref="jobExplorer" />
<property name="jobLauncher" ref="jobLauncher" />
<property name="jobRegistry" ref="jobRegistry" />
<property name="jobRepository" ref="jobRepository" />
</bean>
<bean class="org.springframework.batch.core.scope.StepScope">
<property name="proxyTargetClass" value="true" />
</bean>
<bean id="batchPoolingDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
<property name="url" value="jdbc:sqlserver://server info" />
<property name="username" value="${batch.jdbc.user}" />
<property name="password" value="${batch.jdbc.password}" />
<property name="initialSize" value="5" />
<property name="maxTotal" value="15" />
<property name="maxWaitMillis" value="5000" />
</bean>
<bean id="batchDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
<property name="driverClassName" value="org.springframework.jdbc.datasource.DriverManagerDataSource" />
<property name="url" value="jdbc:sqlserver://server info" />
<property name="username" value="${batch.jdbc.user}" />
<property name="password" value="${batch.jdbc.password}" />
</bean>
