sql - 选择更新时的 SQL 死锁
问题描述
我正在尝试解决 SQL 死锁问题。以下是 system_health 报告
<deadlock>
<victim-list>
<victimProcess id="process87d03ccf8" />
</victim-list>
<process-list>
<process id="process87d03ccf8" taskpriority="0" logused="0" waitresource="KEY: 7:72057901332627456 (f323ae9efc53)" waittime="1087" ownerId="20788909869" transactionname="SELECT" lasttranstarted="2020-12-03T23:13:56.500" XDES="0x338706d10" lockMode="S" schedulerid="6" kpid="38240" status="suspended" spid="103" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2020-12-03T23:13:56.490" lastbatchcompleted="2020-12-03T23:13:56.490" lastattention="1900-01-01T00:00:00.490" clientapp=".Net SqlClient Data Provider" hostname="ID45846" hostpid="58020" loginname="ubuser" isolationlevel="read committed (2)" xactid="20788909869" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="x.dbo.OrderData_GetByOrderID" line="6" stmtstart="124" sqlhandle="0x03000700cddb7412e6afdc00a0a9000001000000000000000000000000000000000000000000000000000000"></frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 309648333] </inputbuf>
</process>
<process id="process32f127868" taskpriority="0" logused="112" waitresource="KEY: 7:72057901332692992 (004616e83cc3)" waittime="1087" ownerId="20788909868" transactionname="UPDATE" lasttranstarted="2020-12-03T23:13:56.500" XDES="0x81bad63a8" lockMode="X" schedulerid="15" kpid="66292" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-12-03T23:13:56.490" lastbatchcompleted="2020-12-03T23:13:56.490" lastattention="1900-01-01T00:00:00.490" clientapp=".Net SqlClient Data Provider" hostname="ID45846" hostpid="58020" loginname="ubuser" isolationlevel="read committed (2)" xactid="20788909868" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="x.dbo.OrderData_Set" line="36" stmtstart="1662" sqlhandle="0x030007002608c15b50af010156ac000001000000000000000000000000000000000000000000000000000000"></frame>
</executionStack>
<inputbuf>
Proc [Database Id = 7 Object Id = 1539377190] </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057901332627456" dbid="7" objectname="unidbmaster.dbo.OrderData" indexname="PK_OrderData" id="lock494546200" mode="X" associatedObjectId="72057901332627456">
<owner-list>
<owner id="process32f127868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process87d03ccf8" mode="S" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057901332692992" dbid="7" objectname="unidbmaster.dbo.OrderData" indexname="IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue" id="lock73ecc1b80" mode="S" associatedObjectId="72057901332692992">
<owner-list>
<owner id="process87d03ccf8" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process32f127868" mode="X" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
基于此,以下是正确的:
- Update 进程锁定了 PK_OrderData 并且 Select 进程想要它?
- Select 进程锁定了 IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue 并且 Update 进程想要它?
- Select 进程正在选择 Update 进程想要更新的同一行
我假设解决此问题的一种方法是删除 IX_OrderData_Currency_ParcelData_GrandTotal_CustomsValue 索引,但它在其他地方使用。
所以我的问题是,有什么选择可以解决这个问题?我知道我可以在 Select 语句中添加一个指令,说“脏数据没问题”,但是感觉不对……
解决方案
选择和更新之间的死锁发生是因为选择使用首先找到记录最快的索引,然后使用基表(聚集索引)来检索其余信息,因为您正在选择所有列。
另一方面,更新是首先更新基表(聚集索引),然后导致引用正在更新的列的所有其他索引在此之后更新。
正如您所看到的,索引的访问顺序是颠倒的,因为这两个语句都在完全相同的时间发生,所以它们死锁了。
select * from table where fk = @Id
一种可能的解决方案是外键列上的另一个索引。假设此列不是更新的一部分,它不会受到更新的影响。
另一种可能的解决方案是将您选择的列限制为您需要的列(select *
几乎总是一个坏主意),并为您选择的所有列以及外键创建一个覆盖索引。这样,选择只会命中单个索引。
推荐阅读
- node.js - 杀死不断重启的Windows进程
- python - 如何删除 Windows 远程服务器上的文件?
- powershell - 使用 powershell 安装 Sharepoint 2016
- excel - 将命名范围数据从一个工作簿移动到另一个工作簿
- java - 如何按数字和单词组拆分文本
- excel - 是否有任何自动方式将 Azure SQL 数据库表数据导出到 Excel?
- python - 尝试安装烧瓶时显示“ [Errno 13] Permission denied: '/usr/lib/python2.7/dist-packages/Jinja2-2.8.egg-info/entry_points.txt'”?
- python - 将简单函数用作具有 OOP 特性的类
- jquery - 如何在jQuery中的复选框数组上设置监听器?
- java - 设置在 Spinner 上选择的基于 TableLayout 的可见性项目