为什么不遵守SQL Server的SET DEADLOCK_PRIORITY HIGH？ [英] Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?
问题描述
我已经捕获了一个SQL Server 2012死锁图（使用盖尔·肖（查询），它显示了taskpriority = 10的进程被当成两个tasktaskorority = 0的进程的死锁受害者。
I've captured a SQL Server 2012 deadlock graph (using Gail Shaw's query) that shows a process with taskpriority="10" being picked as the deadlock victim over 2 processes with taskpriority="0".
我的理解是首先检查死锁优先级，然后选择优先级较低的进程作为受害者。只有当所有流程都具有相同的优先级时，其他因素才有意义。任何人都无法阐明为什么可能无法兑现DEADLOCK_PRIORITY吗？
My understanding is that the deadlock priority is checked first and lower priority processes will be chosen as the victim. Only when all processes are equal priority will other factors be relevant. Can anyone shed any light on why DEADLOCK_PRIORITY might not be honored?
有趣的是， SET DEADLOCK_PRIORITY MSDN页面说HIGH映射到5，我的代码肯定使用HIGH，所以我不知道10的来源。
Interestingly, the SET DEADLOCK_PRIORITY MSDN page says that HIGH maps to 5, and my code definitely uses HIGH, so I'm not sure where the 10 comes from.
令人讨厌的是，受害者是一个重要的业务流程，而幸存者都是SSMS Intellisense查询。
Annoyingly, the victim is an important business process whilst the survivors are both SSMS Intellisense queries.
编辑
首先，这个问题是关于为什么DEADLOCK_PRIORITY不会被兑现，不是什么死锁，如何防止死锁或如何解决死锁或造成什么原因下例中的一个。
Firstly, this question is about why DEADLOCK_PRIORITY would not be honoured, not what deadlocks are or how to prevent them or work around them or what caused the one in the example below. Those are all interesting conversations, but not here.
其次，基于@SteveFord找到的链接，可能还有其他一些相关事实。在此SQL Server上启用了锁分区，并且SQL Server版本早于2012 CU6（当KB2776344中的补丁发布时。）
Secondly, a couple of additional facts that might be relevant based on links found by @SteveFord; Lock partitioning is enabled on this SQL Server and the SQL Server version is earlier than 2012 CU6 (when the patch in KB2776344 was released.
第三，对于那些对此感兴趣的人来说，清理过的死锁图，显示了优先级较高的进程被选为受害者。我删除了SQL并更改了一些名称，其他所有内容均保持不变。
Thirdly, for those interested here is a sanitised deadlock graph, showing the higher priority process being chosen as the victim. I've removed SQL and changed a few names, everything else is intact.
<deadlock>
<victim-list>
<victimProcess id="process5f390c8" />
</victim-list>
<process-list>
<process id="process5f390c8" taskpriority="10" logused="3200" waitresource="KEY: 6:281474978938880 (655334c51469)" waittime="1806" ownerId="296690694" transactionname="ALTER PARTITION FUNCTION" lasttranstarted="2018-01-29T11:59:36.140" XDES="0x886312d28" lockMode="X" schedulerid="9" kpid="32684" status="suspended" spid="86" sbid="0" ecid="0" priority="5" trancount="1" lastbatchstarted="2018-01-29T11:58:38.310" lastbatchcompleted="2018-01-29T11:58:38.310" lastattention="1900-01-01T00:00:00.310" clientapp="CLIENTAPP" hostname="HOSTNAME" hostpid="10912" loginname="DOMAIN\USERNAME" isolationlevel="read committed (2)" xactid="296690694" currentdb="6" lockTimeout="4294967295" clientoption1="673187936" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="2" stmtstart="138" sqlhandle="0x01000600a1f28605207939860500000000000000000000000000000000000000000000000000000000000000">
...removed...</frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
...removed...</frame>
<frame procname="SUBSPNAME" line="75" stmtstart="5434" stmtend="5502" sqlhandle="0x0300060011b27f3d08e76c012ba8000001000000000000000000000000000000000000000000000000000000">
...removed...</frame>
<frame procname="SPNAME" line="65" stmtstart="4234" stmtend="4516" sqlhandle="0x030006004990de353efaf70071a8000001000000000000000000000000000000000000000000000000000000">
...removed...</frame>
<frame procname="adhoc" line="1" sqlhandle="0x01000600679e2e28907739860500000000000000000000000000000000000000000000000000000000000000">
...removed...</frame>
</executionStack>
<inputbuf>
...removed...</inputbuf>
</process>
<process id="process791872558" taskpriority="0" logused="0" waitresource="OBJECT: 6:139251651:11 " waittime="8299" ownerId="300839454" transactionname="MDView" lasttranstarted="2018-01-29T12:19:33.727" XDES="0x4cddd58a0" lockMode="Sch-S" schedulerid="9" kpid="20372" status="suspended" spid="75" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-01-29T12:19:33.720" lastbatchcompleted="2018-01-29T12:19:33.713" lastattention="2018-01-29T12:19:18.360" clientapp="Microsoft SQL Server Management Studio" hostname="ANOTHERHOSTNAME" hostpid="62236" loginname="DOMAIN\ANOTHERUSERNAME" isolationlevel="read committed (2)" xactid="300839326" currentdb="6" lockTimeout="10000" clientoption1="671090784" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="56" sqlhandle="0x02000000c7bca00d097183e2d5dd8e6785f452180936fd930000000000000000000000000000000000000000">
...removed...</frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
...removed...</frame>
</executionStack>
<inputbuf>
...removed...</inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="281474978938880" dbid="6" objectname="DBNAME.sys.sysschobjs" indexname="clst" id="lock1ef508c700" mode="U" associatedObjectId="281474978938880">
<owner-list>
<owner id="process791872558" mode="S" />
</owner-list>
<waiter-list>
<waiter id="process5f390c8" mode="X" requestType="convert" />
</waiter-list>
</keylock>
<objectlock lockPartition="11" objid="139251651" subresource="FULL" dbid="6" objectname="TABLENAME" id="lock398e43e00" mode="Sch-M" associatedObjectId="139251651">
<owner-list>
<owner id="process5f390c8" mode="Sch-M" />
</owner-list>
<waiter-list>
<waiter id="process791872558" mode="Sch-S" requestType="wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
推荐答案
看起来正在被杀死的命令是一个更改分区功能，有趣的是，这需要一个SCH-M锁，该锁与为所有功能所采用的SCH-S锁不兼容。我想这可能是一个原因。
It looks like the command which is being killed is an ALTER PARTITION FUNCTION, it is interesting to note that this requires a SCH-M lock which is incompatible with SCH-S locks which are taken for everything. I guess this may be a cause.
请参见 michaeljswart.com/2013/04/the-sch-m-lock-is-evil 。
另请参阅以下有关ALTER PARTITION函数中SCH-M死锁的描述以及在SQL 2014& amp;中导致统计信息更新的查询。 2016年，但也许在2012年也是如此：获取SCH-M锁定时发生死锁
Also see this description of a SCH-M deadlock from an ALTER PARTITION Function and a query that causes a statistics update in SQL 2014 & 2016, but maybe true in 2012 too: Deadlock Occurs when you acquire a SCH-M lock
查看图表，一个进程在sysschobjs上具有共享（更新）锁，并且正在等待表上的SCH-S锁。您的进程在表上具有SCH-M锁，正在等待sysschobjs上的X锁。 sysschobjs是位于sysobjects后面的系统基础表。请参阅此处的讨论技术网：经常导致死锁的SQL查询
Looking at your graph, one process has a shared (update) lock on sysschobjs and is waiting for a SCH-S lock on your table. Your process has a SCH-M lock on your table and is waiting for a X lock on sysschobjs. sysschobjs is a system base table which sits behind sysobjects. See the discussion here Technet: SQL Query that causes deadlock often
希望这会有所帮助
更新
如果您想进一步研究，我发现了死锁监视器如何选择受害者的MS专利说明此处
这篇关于为什么不遵守SQL Server的SET DEADLOCK_PRIORITY HIGH？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
