主页 / dba / 问题 / 299762
Accepted
Thomas K
Asked: 2021-09-18 03:05:20 +0800 CST

SQL Server - 两个并发 DELETE 期间的死锁

  • 772

我有一个 Java 应用程序公开 REST API 以删除表中的记录（SQL Server），有时 API 必须处理并发删除请求（每个请求使用不同的数据库会话）

这是数据库模式的摘录：

MailingInstruction
------------------
id (PK)
[...]
addressId (FK to Address table)

Address
------------------
id (PK)
street
street_nr
[...]

MailingInstruction 记录的删除会触发关联地址的级联删除。每个 MailingInstruction 都有一个专用地址（不可重复使用）

但有时，其中一个并发删除会因为死锁而失败：

事务（进程 ID 51）与另一个进程在锁资源上死锁，并被选为死锁牺牲品。重新运行事务。

所以我启用了一些日志：

DBCC TRACEON(1222, 3605,-1)

似乎问题是地址记录上的删除，这很奇怪，因为当我通过主键删除时，我认为锁只会在特定记录上。

这是死锁跟踪：

2021-09-17 09:53:53.39 spid15s 死锁列表 2021-09-17 09:53:53.39 spid15s 死锁受害者=process80ba33848 2021-09-17 09:53:53.40 spid15s 进程列表 2021-09-17 09: 53:53.40 spid15s 进程 id=process80ba33848 taskpriority=0 logused=348 waitresource=KEY: 5:72057594043564032 (e82f06e0affb) waittime=1424 ownerId=30578 transactionname=implicit_transaction lasttranstarted=> 2021-09-0947T09:x=1151X151。 S schedulerid=4 kpid=684 status=suspended spid=51 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=> 2021-09-17T09:53:51.970 lastbatchcompleted=> 2021-09-17T09:53:51.967 lastattention =1900-01-01T00:00:00。967 clientapp=Microsoft JDBC Driver for SQL Server 主机名=MY-DESKTOP hostpid=0 loginname=sa 隔离级别=读取已提交 (2) xactid=30578 currentdb=5 currentdbname=MY_DB lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 2021-09-17 09:53:53.44 spid15s executionStack 2021-09-17 09:53:53.45 spid15s frame procname=adhoc line=1 stmtstart=24 stmtend=100 sqlhandle=0x02000000627bbe0c9854b43c4ee3d7183c254e990331f4e80000000000000000000000000000000000000000 2021-09-17 09:53:53.46 spid15s unknown 2021-09- 17 09:53:53.46 spid15s frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 2021-09-17 09:53:53.47 spid15s unknown 2021-09-17 09:53:53.47 spid15s inputbuf 2021-09-17 09:53: 53.47 spid15s (@P0 bigint) 从地址中删除 addressId=@P0 2021-09-17 09:53:53.48 spid15s 进程 id=process80ba1f088 taskpriority=0 logused=352 waitresource=KEY: 5:72057594043564032 (036d9ed30da4) waittime=1424 ownerId =30579 transactionname=implicit_transaction lasttranstarted=> 2021-09-17T09:53:51.967 XDES=0x1137680420 lockMode=S schedulerid=2 kpid=604 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=> 2021-09-17T09:53:51.970 lastbatchcompleted=> 2021-09-17T09:53:51.967 lastattention=1900-01-01T00:00:00.967 clientapp=Microsoft JDBC Driver for SQL Server hostname=MY-DESKTOP hostpid=0 loginname= sa 隔离级别=已提交读取 (2) xactid=30579 currentdb=5 currentdbname=MY_DB lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058 2021-09-17 09:53:53。51 spid15s executionStack 2021-09-17 09:53:53.52 spid15s frame procname=adhoc line=1 stmtstart=24 stmtend=100 sqlhandle=0x02000000627bbe0c9854b43c4ee3d7183c254e990331f4e80000000000000000000000000000000000000000 2021-09-17 09:53:53.53 spid15s unknown 2021-09-17 09:53 :53.53 spid15s frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 2021-09-17 09:53:53.54 spid15s unknown 2021-09-17 09:53:53.54 spid15s inputbuf 2021-09-17 09:53:53.54 spid15s (@ P0 bigint) 从 addressId=@P0 2021-09-17 09:53:53.55 spid15s resource-list 2021-09-17 09:53:53.55 spid15s keylock hobtid=72057594043564032 dbid=5 objectname=MY_DB.dbo 的地址中删除。MailingInstruction indexname=PK__MailingI__F3FD20CC83FED272 id=lock1146070b80 mode=X associatedObjectId=72057594043564032 2021-09-17 09:53:53.56 spid15s owner-list 2021-09-17 09:53:53.56 spid15s owner id=process80ba1f088 mode=X 2021-09-17 09:53:53.57 spid15s 服务员列表 2021-09-17 09:53:53.57 spid15s 服务员 id=process80ba33848 mode=S requestType=wait 2021-09-17 09:53:53.57 spid15s keylock hobtid=72057594043564032 MY_DB.dbo.MailingInstruction indexname=PK__MailingI__F3FD20CC83FED272 id=lock11393baa00 mode=X associatedObjectId=72057594043564032 2021-09-17 09:53:53.58 spid15s owner-list 2021-09-17 09:53:53.59 spid15s owner id=process80ba33848 mode=X 2021 -09-17 09:53:53.59 spid15s 服务员列表 2021-09-17 09:53:53。59 spid15s 服务员 id=process80ba1f088 mode=S requestType=wait

知道为什么删除两条不同的记录会触发这种死锁吗？

谢谢

sql-server deadlock

2 个回答

  1. 铅码头

    您可能遇到的一个问题是隐式事务，这是 JDBC 驱动程序的默认设置。您可以在您发布的死锁详细信息中验证这一点：transactionname=implicit_transaction.

    您可以通过con.setAutoCommit(true);在连接字符串中使用来更改它。

    有点意思

    一个有趣的地方是这个删除的锁定模式是共享的：lockMode=S. 这可能表明查询计划中涉及到查找。

    您可以通过运行此查询来获取删除计划，只要它仍在计划缓存中。

    SELECT
    qs.last_execution_time,
    qs.execution_count,
    qs.total_worker_time,
text = 
    SUBSTRING
    (
        st.text, 
        ( qs.statement_start_offset / 2 ) + 1,
        (
          ( 
            CASE qs.statement_end_offset 
                 WHEN -1 
                 THEN DATALENGTH(st.text) 
                 ELSE qs.statement_end_offset 
            END - qs.statement_start_offset 
          ) / 2 
        ) + 1
    ),
    query_plan =
        TRY_CAST(qp.query_plan AS xml)
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text
(
    qs.sql_handle
) AS st  
CROSS APPLY sys.dm_exec_text_query_plan 
(
    qs.plan_handle,
    qs.statement_start_offset,
    qs.statement_end_offset
) AS qp
WHERE qs.sql_handle = 0x02000000E1E2A73A5B449F98DAE6C300BE7E0DBC30E139D00000000000000000000000000000000000000000;

    它还可能表明批处理中还有其他查询以及未显示在死锁图中的删除，因为死锁图是热垃圾。

    概论

    如果您要继续使用隐式事务，则应该打开Read Committed Snapshot Isolation以避免在Read Committed隔离级别下发生的荒谬锁定的其他并发症，这也是热垃圾。

    您可以像这样打开它，只需输入您的数据库名称：

    ALTER DATABASE 
    StackOverflow 
SET READ_COMMITTED_SNAPSHOT ON 
    WITH ROLLBACK IMMEDIATE;
    • 3
  2. Best Answer

    似乎问题是地址记录上的删除，这很奇怪，因为当我通过主键删除时，我认为锁只会在特定记录上。

    当然，在地址上。但随后它会扫描 MailingInstruction 以实现级联删除。

    级联删除死锁的主要原因是您的外键没有正确的索引。AddressID 必须至少有一个索引，最好是 MailingInstruction 的主键的前导列。

    • 3

相关问题