sql-server - SQLException:处理 2 个不同的表时出现死锁错误
问题描述
我有死锁错误:
事务(进程 ID 91)与另一个进程在锁资源上死锁,并已被选为死锁牺牲品。重新运行事务。
图片死锁(https://i.stack.imgur.com/rDyxP.png)
Sp 91 是存储 Katzkin.dbo.ItemsAirBags_DX_IdItem:
delete ItemsAirBags
from ItemsAirBags
where IdItem = @IdItem
Sp291 是存储 Katzkin.dbo.Items_TX_Description:
select *
from items
where description = @description or left(description,5) = @description
deadlock.xml
我从 redgate 下载的完整内容:
<deadlock>
<victim-list>
<victimProcess id="process366a0a5848" />
</victim-list>
<process-list>
<process id="process366a0a5848" taskpriority="0" logused="6424" waitresource="KEY: 6:72057609466478592 (56dfc8bc1bd3)" waittime="134" ownerId="2620704196" transactionname="user_transaction" lasttranstarted="2021-01-25T10:48:53.610" XDES="0x4462dd4490" lockMode="U" schedulerid="11" kpid="14292" status="suspended" spid="91" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-01-25T10:48:53.690" lastbatchcompleted="2021-01-25T10:48:53.687" lastattention="2021-01-25T08:32:04.567" clientapp=".Net SqlClient Data Provider" hostname="SQLSERVER" hostpid="17172" loginname="KatCa" isolationlevel="read committed (2)" xactid="2620704196" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="Katzkin.dbo.ItemsAirBags_DX_IdItem" line="2" stmtstart="136" stmtend="258" sqlhandle="0x030006006b1f922228e7730050ab000001000000000000000000000000000000000000000000000000000000">
delete ItemsAirBags from ItemsAirBags
where IdItem = @IdIte </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 580001643] </inputbuf>
</process>
<process id="process3dc4191468" taskpriority="0" logused="34720" waitresource="KEY: 6:72057609931325440 (b015ce3d2676)" waittime="62" ownerId="2620704065" transactionname="user_transaction" lasttranstarted="2021-01-25T10:48:53.500" XDES="0x3d45478490" lockMode="S" schedulerid="6" kpid="1500" status="suspended" spid="291" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2021-01-25T10:48:53.760" lastbatchcompleted="2021-01-25T10:48:53.760" lastattention="2021-01-25T08:30:50.683" clientapp=".Net SqlClient Data Provider" hostname="SQLSERVER" hostpid="30552" loginname="KatCa" isolationlevel="read committed (2)" xactid="2620704065" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="Katzkin.dbo.Items_TX_Description" line="4" stmtstart="172" stmtend="348" sqlhandle="0x030006008513045381f88c002da3000001000000000000000000000000000000000000000000000000000000">
Select * from items
where description=@description or left(description,5)=@descriptio </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 6 Object Id = 1392776069] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057609466478592" dbid="6" objectname="Katzkin.dbo.ItemsAirBags" indexname="PK_ItemsAirBags" id="lock3ca39db480" mode="X" associatedObjectId="72057609466478592">
<owner-list>
<owner id="process3dc4191468" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process366a0a5848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057609931325440" dbid="6" objectname="Katzkin.dbo.Items" indexname="PK_Items" id="lock3f52da2100" mode="X" associatedObjectId="72057609931325440">
<owner-list>
<owner id="process366a0a5848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process3dc4191468" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
我们在两张不同的桌子上,但造成了死锁,我不明白原因以及如何解决它。关于如何解决这些问题的任何想法?
解决方案
如死锁图所示,您有 2 个存储过程,它们都试图更新/访问相同的行,但由于某种原因,一个是ItemsAirBags
首先更新/访问表,然后是Items
表,另一个存储过程以相反的顺序更新/访问。这就是造成僵局的原因。如果您使两个存储过程都以相同的顺序更新/访问两个表,您将消除死锁。
当您处理父子情况时,有时需要先对计划更新的子行进行更新锁定,然后再更新父项,然后是子项。
您希望确保始终以相同的顺序更新/访问您的表,因为这样第一个存储过程将阻塞第二个存储过程(这是您想要的)而不是死锁。
注意:这些存储过程中发生的事情一定比代码显示的要多,因为就目前而言,不会出现死锁。不知何故,进程process3dc4191468
有一个排他锁,ItemsAirBags
它不是所示 SQL 的一部分。
推荐阅读
- java - 使用 Spring Boot 的 Log4j2 JDBC Appender“错误无法写入数据库”
- javascript - 为什么我的 Google Maps API 回调在前面的内联脚本之前执行?
- python - PandaSQLException:(sqlite3.OperationalError)没有这样的列错误与pandasql
- python-3.x - Python:如何创建一个日期时间数组,并在分箱前为数组中的每个索引提取对应的年、月、日、小时
- jquery - laravel jquery 计数表行追加自动递增 id
- swift - 从 FileMaker 复制/粘贴后出现奇数符号
- angular - 我如何从角度将本地存储值发送到服务器
- sandbox - 使用 gnu autoconf,有没有办法构建到沙箱中?
- git - GitLab 辅助地理代码推送失败,内部 API 无法访问
- jersey - Jersey 客户端 - 发布请求给出错误“Content-Type 标头必须是 application/vnd.api+json”