sql - 设置 deadlock_priority 不会使低优先级 session 成为受害者

Question

我们的一项作业与用户例程发生死锁，因此我们在调用过程之前将以下代码放入作业步骤中:

DECLARE @deadlock_var NCHAR(3);  
SET @deadlock_var = N'LOW';  
SET DEADLOCK_PRIORITY @deadlock_var;
*--Call procedure
exec Client_myDeliveries_I_S*

但是，该过程仍在正在处理的堆栈中。死锁优先级不会通过子过程链继承吗？我可以确认死锁受害者 session 在任何时候都不会设置其 deadlock_priority。

完整的死锁 XML 如下，出于隐私原因删除了一些内容:

<deadlock>
 <victim-list>
  <victimProcess id="process28fc21868" />
 </victim-list>
 <process-list>
  <process id="process28fc21868" taskpriority="0" logused="2504" waitresource="KEY: 5:72057594562412544 (40fd182c0dd9)" waittime="5008" ownerId="299034576" transactionname="user_transaction" lasttranstarted="2018-02-01T12:22:55.580" XDES="0x140b2cc70" lockMode="X" schedulerid="1" kpid="3600" status="suspended" spid="87" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-02-01T12:22:55.580" lastbatchcompleted="2018-02-01T12:22:55.580" lastattention="2018-02-01T12:22:52.480" clientapp="EUROSTOP e-i Service" hostname="SRVAZBRWSQL01" hostpid="1328" loginname="sa" isolationlevel="read committed (2)" xactid="299034576" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="ALL.dbo.trgi_u_Constants" line="705" stmtstart="37030" stmtend="37270" sqlhandle="0x03000500651fce5aad8fd3002aa5000000000000000000000000000000000000000000000000000000000000">
update myconstants
                set value = (select last_c_no from inserted)
                where batch = 'last_c_no'    </frame>
    <frame procname="IF_TEST.myschema.SendTestData_Customers_SToALL_Customers" line="87" stmtstart="11420" stmtend="11650" sqlhandle="0x030007007f19ab663ac3e5005ca7000001000000000000000000000000000000000000000000000000000000">
update myschema.ALL_Constants
            set     last_c_no = @nLastCNumber + @NumberOfInsertedCs    </frame>
    <frame procname="IF_TEST.myschema.SendTestData_Customers" line="22" stmtstart="962" stmtend="1196" sqlhandle="0x030007009c954b7757b4d00054a7000001000000000000000000000000000000000000000000000000000000">
exec myschema.SendTestData_Customers_SToALL_Customers @MessageCode, @RejectAllOnValidationError    </frame>
    <frame procname="IF_TEST.myschema.SendSubmittedFData" line="13" stmtstart="876" stmtend="1114" sqlhandle="0x030007009b4fb66e2db4d00054a7000001000000000000000000000000000000000000000000000000000000">
exec [myschema].[SendTestData_Customers] @MessageCode, @RejectAllOnValidationError

    -- Customer Orders    </frame>
    <frame procname="adhoc" line="1" stmtstart="104" sqlhandle="0x010007008547740e50dc4dd90700000000000000000000000000000000000000000000000000000000000000">
Exec myschema.SendSubmittedFData @0, @1, @2, @3    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@0 nvarchar(4000),@1 int,@2 nvarchar(4000),@3 int);Exec myschema.SendSubmittedFData @0, @1, @2, @3   </inputbuf>
  </process>
  <process id="process2b6e94cf8" taskpriority="-6" logused="43652" waitresource="KEY: 5:72057594562412544 (d08358b1108f)" waittime="5009" ownerId="299033786" transactionname="user_transaction" lasttranstarted="2018-02-01T12:22:53.810" XDES="0x28262f130" lockMode="X" schedulerid="2" kpid="13408" status="suspended" spid="122" sbid="0" ecid="0" priority="-5" trancount="3" lastbatchstarted="2018-02-01T12:15:00.580" lastbatchcompleted="2018-02-01T12:15:00.580" lastattention="1900-01-01T00:00:00.580" clientapp="SQLAgent - TSQL JobStep (Job 0x24121E41ABD80643985B522FE6C248A7 : Step 1)" hostname="SRVAZBRWSQL01" hostpid="2252" loginname="SRVAZBRWSQL01\ALLSYSTEM" isolationlevel="read committed (2)" xactid="299033786" currentdb="5" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
   <executionStack>
    <frame procname="ALL.dbo.trgi_u_Constants" line="726" stmtstart="38142" stmtend="38386" sqlhandle="0x03000500651fce5aad8fd3002aa5000000000000000000000000000000000000000000000000000000000000">
update myconstants
                set value = (select last_gs_rec_no from inserted)
                where batch = 'last_gs_rec_no'    </frame>
    <frame procname="ALL.dbo.trgi_i_i_I_deliveries" line="1005" stmtstart="69046" stmtend="69220" sqlhandle="0x030005006cd104389a850d00a1a6000000000000000000000000000000000000000000000000000000000000">
update dbo.Constants set last_gs_rec_no = last_gs_rec_no + @nmyDeliveriesCreatedCount    </frame>
    <frame procname="C_HELP.dbo.Client_myDeliveries_I_Std" line="86" stmtstart="7306" stmtend="8324" sqlhandle="0x030006000d09a1438efcba0074a8000001000000000000000000000000000000000000000000000000000000">
insert  into [ALL].dbo.i_I_deliveries
                --[columns]
                select  --[columns]
                from    #Client_deliveries_PO_stg stg    </frame>
    <frame procname="C_HELP.dbo.Client_myDeliveries_I_S" line="446" stmtstart="32832" stmtend="33032" sqlhandle="0x0300060028323b37f7a70101eca7000001000000000000000000000000000000000000000000000000000000">
exec  dbo.Client_myDeliveries_I_Std @week_selector, @username, @factory_bin_location, @Parameter    </frame>
    <frame procname="adhoc" line="6" stmtstart="216" sqlhandle="0x02000000e49e3111676b7e3aec714d06946692f70e3a8a880000000000000000000000000000000000000000">
exec Client_myDeliveries_I_S    </frame>
   </executionStack>
   <inputbuf>
DECLARE @deadlock_var NCHAR(3);  
SET @deadlock_var = N'LOW';  

SET DEADLOCK_PRIORITY @deadlock_var;

exec Client_myDeliveries_I_S   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <keylock hobtid="72057594562412544" dbid="5" objectname="ALL.dbo.myconstants" indexname="pk_myconstants" id="lockd5ad7b00" mode="RangeS-U" associatedObjectId="72057594562412544">
   <owner-list>
    <owner id="process2b6e94cf8" mode="RangeS-S" />
   </owner-list>
   <waiter-list>
    <waiter id="process28fc21868" mode="X" requestType="convert" />
   </waiter-list>
  </keylock>
  <keylock hobtid="72057594562412544" dbid="5" objectname="ALL.dbo.myconstants" indexname="pk_myconstants" id="lock9466df00" mode="RangeS-U" associatedObjectId="72057594562412544">
   <owner-list>
    <owner id="process28fc21868" mode="RangeS-S" />
   </owner-list>
   <waiter-list>
    <waiter id="process2b6e94cf8" mode="X" requestType="convert" />
   </waiter-list>
  </keylock>
 </resource-list>
</deadlock>

附加版本:Microsoft SQL Server 2012 (SP3-CU2) (KB3137746) - 11.0.6523.0 (X64) 2016 年 3 月 2 日 21:29:16 版权所有 (c) 微软公司 Windows NT 6.3(内部版本 9600:)(虚拟机管理程序)上的标准版(64 位)

Answer 1

从大家的反馈来看，在微软没有明确说明的情况下(据我们所知)，SQL Server似乎可以忽略死锁优先级；用于确定受害者的算法未被理解或记录。如果有人可以找到有关此主题的可靠信息，请分享。