sql-server - 插入和删除同时出现SQL死锁
问题描述
目前,我的 .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 < @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>
对我来说,这两个锁似乎都在升级。我怎样才能避免这种行为?
解决方案
我认为跳过某些行(不时地)您的删除过程并不重要。我的想法是在使用“READPAST”提示删除时跳过锁定的行:
Delete From
[DATA_CONT] WITH (READPAST)
WHERE
CID = @p_CID AND
(@p_DeleteOlderThan IS NULL OR ResultDate < @p_DeleteOlderThan
推荐阅读
- linux-kernel - 如何从具有循环依赖关系的模块 (*.ko) 中导出函数
- javascript - 尝试显示从外部脚本返回的数组中的值
- excel - 工作表名称与目标工作表名称不匹配时的 VBA 运行时错误 9
- java - 如何在 Java 中处理来自 Jackson 解析器的 `jsonParseException`
- angular - 动态 FormGroup 上的 ngFor 未更新
- r - 在R中按日期过滤列
- javascript - 自动滚动反应 redux 实现
- javascript - 如何捕获没有键和值的json数据
- javascript - 我有这个错误“cURL 错误 60:SSL 证书问题:无法获取本地颁发者证书”
- python - Python SQL 通过多个查询循环变量