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 commented 4 days ago

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 commented 4 days ago

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.

machavan commented 2 days ago

Hi @wodediqizhangqq

Shall we close this issue as this is not a driver issue and hopefully the above response will help you in resolving the issue?

Jeffery-Wasty commented 1 day ago

Closing as this is not a driver issue.

microsoft / mssql-jdbc