Search code examples
sql-serversql-server-2012deadlock

Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?


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".

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?

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.

Annoyingly, the victim is an important business process whilst the survivors are both SSMS Intellisense queries.

Edit

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.

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.

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>

Solution

  • 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.

    See michaeljswart.com/2013/04/the-sch-m-lock-is-evil.

    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

    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

    Hope this helps

    Update If you want to research this further I have found the MS Patent description of how the Deadlock Monitor chooses victims here