Within the same table, performing updates, inserts, and deletes in a single transaction can result in key locks and deadlocks on lock | communication buffer resources.

[wodediqizhangqq]

Driver version

Provide the JDBC driver version (e.g. 10.2.0).
implementation 'com.microsoft.sqlserver:mssql-jdbc:12.8.1.jre11'
implementation 'com.microsoft.sqlserver:mssql-jdbc:8.4.1.jre8'
I only used these two, and both resulted in deadlocks.

jdbcurl：
jdbc:sqlserver://172.16.120.84:8433;database=rc50_turkish_rootdb;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*;loginTimeout=30

SQL Server version

Provide the output of executing SELECT @@VERSION on your target SQL Server.
Linux server：
Microsoft SQL Server 2019 (RTM-CU27) (KB5037331) - 15.0.4375.4 (X64)
Jun 10 2024 18:00:07
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))
Windows server：
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Client Operating System

Provide the Operating System the client application is running on.

windows cmd or idea start up， && Centos

JAVA/JVM version

Provide the JAVA/JVM version (e.g. java version "1.8.0").
java -version
openjdk version "17.0.9" 2023-10-17
OpenJDK Runtime Environment Temurin-17.0.9+9 (build 17.0.9+9)
OpenJDK 64-Bit Server VM Temurin-17.0.9+9 (build 17.0.9+9, mixed mode, sharing)

Table schema

Provide the table schema to repro the issue.

CREATE TABLE it_tools_esu (
abcdefge_code varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
abcde_code varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
sab varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
esu varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
[type] int DEFAULT 1 NOT NULL,
create_time datetime NULL,
CONSTRAINT PK__it_tools__E346E079A6C93B46 PRIMARY KEY (abcdefge_code,abcde_code,sab,esu)
);
CREATE NONCLUSTERED INDEX idx_tools_esu ON dbo.it_tools_esu ( abcdefge_code ASC , abcde_code ASC , esu ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_tools_sab_type ON dbo.it_tools_esu ( abcdefge_code ASC , abcde_code ASC , sab ASC , type ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY ] ;

Problem description

Provide full details of the problem.
CREATE TABLE it_tools_esu (
abcdefge_code varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
abcde_code varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
sab varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
esu varchar(128) COLLATE Turkish_100_CS_AS NOT NULL,
[type] int DEFAULT 1 NOT NULL,
create_time datetime NULL,
CONSTRAINT PK__it_tools__E346E079A6C93B46 PRIMARY KEY (abcdefge_code,abcde_code,sab,esu)
);
CREATE NONCLUSTERED INDEX idx_tools_esu ON dbo.it_tools_esu ( abcdefge_code ASC , abcde_code ASC , esu ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_tools_sab_type ON dbo.it_tools_esu ( abcdefge_code ASC , abcde_code ASC , sab ASC , type ASC )
WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY ] ;

Task Description
abcdefge_code is 'myCode', which includes four abcde_code: 60000, 60001, 60002, and 60003.
Each abcde_code initializes 10,000 sab, and each sab has 6 esu.
In the business logic, for each transaction:
Update 3 unchanged esu
Add 3 new esu
Delete the old 3 esu
Each store runs a separate thread to handle these operations.

Example Code :

String abcdefge_code = 'myCode'
protected static Long SIZE_NUM = 1000000L;
for (Long startNum = 0L; startNum < 4L; startNum++) {
Long abcdeCodeLong = startNum + 60000;

		Long num = startNum * SIZE_NUM;
        AtomicLong eslIdatomicLongBase = new AtomicLong(num);
        new Thread(() -> {
            while(true){
                String abcdeCode = String.valueOf(abcdeCodeLong);
                Long aa = eslIdatomicLongBase.get();
                AtomicLong eslIdatomicStart = new AtomicLong(aa);
                Long now = System.currentTimeMillis();
                Long id = eslIdatomicStart.addAndGet(random.nextLong(4000L));
                String sab = String.valueOf(id);
                Date nowDate = new Date() ;

				String barcode1 = "97" + sab;
				String barcode2 = "98" + sab;
				String barcode3 = "99" + sab;
				String barcode4 = sab + now;
				String barcode5 = sab + (now + 1);
				String barcode6 = sab + (now + 2);
				
				
				TransactionJdbcTemplate queryJdbcTemplate = new TransactionJdbcTemplate(dataSource);
				//	query old data，compare new data。3 update items、3 delete items、 3 insert items
				//create_time = now
				//type = 1
				
				TransactionJdbcTemplate updateJdbcTemplate = new TransactionJdbcTemplate(dataSource);
				
				transactionJdbcTemplate.beginTranstaion();
				// update 3 do not change esos, time 
				for (ItToolsEso esu : updateEans) {
                            updateJdbcTemplate.update(sql, args);    
							//sauch as update sql
							//UPDATE [it_tools_esu] WITH (ROWLOCK)  SET [type] = ?, [create_time] = ? WHERE [abcdefge_code] = ? AND [abcde_code] = ? AND [sab] = ? AND [esu] = ?, args: [1, Thu Nov 07 17:06:34 CST 2024, myCode, 60005, 5001792, 985001792]
                    }
				//1 sql for 3 delete data
				 ConditionAndArgs cond = newCondition(abcdeCode).andEqualsIfNotNull("sab", sab)
                                .andIn("esu", removeEans);
                        SqlAndArgs sqlAndArgs = sqlect.dialectDelete(ItToolsEso.class, cond);
				updateJdbcTemplate.update(sqlAndArgs.getSql() , sqlAndArgs.getArgs());
			
				
				// 3 insert sql for 
				for (ItToolsEso model : addEans) {
                            SqlAndArgs sqlAndArgs = sqlect.dialectInsertSelective(model);
                            updateJdbcTemplate.update(sqlAndArgs);
                }
			}}
		
		}

If the order of update, insert, and delete is incorrect, index-related key locks may still occur.
After I used update, delete, insert, I no longer encountered deadlocks caused by key locks. What I encountered was a deadlock due to "deadlocked on lock | communication buffer resources".

Expected behavior

A clear and concise description of what you expected to happen.

Actual behavior

Output of what you actually see.

Error message/stack trace

Complete error message and stack trace.

Any other details that can be helpful

Add any other context about the problem here.
log dateils:
org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [UPDATE [it_tools_esu] WITH (ROWLOCK) SET [type] = ?, [create_time] = ? WHERE [customer_code] = ? AND [abcde_code] = ? AND [sab] = ? AND [esu] = ?]; Transaction (Process ID 218) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 218) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:268) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1577) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:669) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:693) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1036) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1090) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1081) ~[spring-jdbc-5.3.30.jar:5.3.30]
...
at java.base/java.lang.Thread.run(Thread.java:840) [?:?]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 218) was deadlocked on lock | communication buffer 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:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatementBatch(SQLServerPreparedStatement.java:2814) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtBatchExecCmd.doExecute(SQLServerPreparedStatement.java:2678) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2079) ~[mssql-jdbc-8.4.1.jre8.jar:?]
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128) ~[HikariCP-3.4.5.jar:?]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:?]
at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$4(JdbcTemplate.java:1050) ~[spring-jdbc-5.3.30.jar:5.3.30]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:650) ~[spring-jdbc-5.3.30.jar:5.3.30]
... 22 more

JDBC trace logs

Provide the JDBC driver trace logs. Instructions can be found here: https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation

Other desc

other table also have。I add useBulkCopyForBatchInsert=true for jdbc.url to resolve.
You can refer to my pseudocode to reproduce the issue stably. I have also changed many server-related configurations, but none of them worked. I need to know whether it might be a driver issue? Are there any other solutions? For example, server-related configurations.

[machavan]

The exception thrown for deadlock

Transaction (Process ID 218) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.

is returned by the server and not the driver.

Please refer:

https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver16

When an instance of the SQL Server Database Engine chooses a transaction as a deadlock victim, it terminates the current batch, rolls back the transaction, and returns error message 1205 to the application.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

It also has a bunch of guidelines to minimize deadlocks.