同时进行插入和删除时出现SQL死锁问题

Question

同时进行插入和删除时出现SQL死锁问题

3

我目前的.NET应用程序中的MSSQL数据库有时会出现死锁。我使用存储过程将数据添加到名为“DATA_CONT”的表中。此外，还有一个定期任务从此表中删除旧数据。有时在同时执行插入和删除过程时会出现死锁。

表DATA_CONT拥有一个包含外键CID（uniqueidentifier）和ResultDate（datetime2（3））的聚集索引。

这是MS SQL的死锁xml图：

<deadlock>
 <victim-list>
  <victimProcess id="process6137528c8" />
 </victim-list>
 <process-list>
  <process id="process6137528c8" taskpriority="5" logused="0" waitresource="KEY: 7:72057594046185472 (0d3d2e12b103)" waittime="1637" ownerId="9357686" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.167" XDES="0x61e4703b0" lockMode="RangeS-U" schedulerid="7" kpid="14644" status="suspended" spid="53" sbid="0" ecid="0" priority="-5" trancount="2" lastbatchstarted="2018-11-12T11:20:55.167" lastbatchcompleted="2018-11-12T11:20:55.167" lastattention="1900-01-01T00:00:00.167" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DE\afr3fe" isolationlevel="serializable (4)" xactid="9357686" currentdb="7" lockTimeout="1800" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="MyDatabase.dbo.Proc_DeleteDataCont" line="12" stmtstart="708" stmtend="954" sqlhandle="0x03000700f2a6e25b72b58a0092a9000001000000000000000000000000000000000000000000000000000000">
Delete From
        [DATA_CONT]
    WHERE
        CID = @p_CID AND
        (@p_DeleteOlderThan IS NULL OR ResultDate &lt; @p_DeleteOlderThan    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 7 Object Id = 1541580530]   </inputbuf>
  </process>
  <process id="process658113468" taskpriority="0" logused="592" waitresource="KEY: 7:72057594046185472 (ae5185f64403)" waittime="1711" ownerId="9357687" transactionname="user_transaction" lasttranstarted="2018-11-12T11:20:55.170" XDES="0x65cd77000" lockMode="RangeI-N" schedulerid="6" kpid="21008" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-11-12T11:20:55.170" lastbatchcompleted="2018-11-12T11:20:55.170" lastattention="1900-01-01T00:00:00.170" clientapp=".Net SqlClient Data Provider" hostname="FE-Z13YL" hostpid="24056" loginname="DE\afr3fe" isolationlevel="serializable (4)" xactid="9357687" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="MyDatabase.dbo.Proc_AddDataCont" line="8" stmtstart="266" stmtend="598" sqlhandle="0x030007005654c5593a84070188a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO [DATA_CONT] SELECT CID, Value, LocationData, ResultDate, GETUTCDATE() FROM @p_DataContLis    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 7 Object Id = 1506104406]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19a80" mode="X" associatedObjectId="72057594046185472">
   <owner-list>
    <owner id="process658113468" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process6137528c8" mode="RangeS-U" requestType="wait" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594046185472" dbid="7" objectname="MyDatabase.dbo.DATA_CONT" indexname="IX_DATA_CONT" id="lock620b19800" mode="RangeS-U" associatedObjectId="72057594046185472">
   <owner-list>
    <owner id="process6137528c8" mode="RangeS-U" />
   </owner-list>
   <waiter-list>
    <waiter id="process658113468" mode="RangeI-N" requestType="wait" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

对我来说，似乎两个锁都被升级了。我该如何避免这种情况？

- Andi F.

1

如果您不需要参数，请勿使用@p_DeleteOlderThan。服务器将在第一次执行查询时缓存执行计划。这可能导致执行计划低效。低效的执行计划最终会锁定更多的行，时间更长。这很容易导致死锁。 - Panagiotis Kanavos

2

假设“删除旧数据”过程不是关键的，请考虑将DEADLOCK_PRIORITY设置为LOW，分批小量删除，分配宽松的重试策略并完全忽略此过程中的死锁，仅检查删除是否足够快以保持表格一致。除了您可能采取的其他减少/消除死锁的措施之外，当忽略它们可以解决问题时，通常不可能或不划算完全摆脱它们。（显然，这并非所有死锁都是如此，因为并非所有内容都可以重试--但清理可以）。 - Jeroen Mostert

正如Jeroen所说，我将删除过程的“DEADLOCK_PRIORITY”设置为“LOW”。因此，如果再次发生死锁，那么较不重要的任务将被取消。 Panagiotis的提示很好，我必须尝试避免可选的筛选参数。为了测试，我删除了这个参数，但仍然出现死锁。 - Andi F.

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Obelixx · Accepted Answer

我假设删除过程中跳过某些行（时不时地）对你并不重要。我的想法是在使用“READPAST”提示时跳过锁定行来进行删除，代码如下：

Delete From
    [DATA_CONT] WITH (READPAST)
WHERE
    CID = @p_CID AND
    (@p_DeleteOlderThan IS NULL OR ResultDate &lt; @p_DeleteOlderThan