7

Is there a way to prevent a process with deadlock priority high from becoming a deadlock victim?

I have SQL Server 2016 SP2 (13.0.5237.0). Here is a deadlock graph I noticed recently in my system. The process with high deadlock priority was chosen as a victim (probably because of high log usage compared to the other process). But that shouldn't have happened. Is this a defect introduced in SQL? Is there a way to prevent a process with high deadlock priority from becoming a victim? Here is the deadlock xml:

<deadlock>
 <victim-list>
  <victimProcess id="process1d3ea515848" />
 </victim-list>
 <process-list>
  <process id="process1d3ea515848" taskpriority="10" logused="5800" waitresource="OBJECT: 9:290100074:0 " waittime="4711" ownerId="359850034" transactionname="user_transaction" lasttranstarted="2019-07-31T08:12:25.267" XDES="0x1d2f62d1840" lockMode="Sch-M" schedulerid="3" kpid="10320" status="suspended" spid="52" sbid="0" ecid="0" priority="5" trancount="4" lastbatchstarted="2019-07-31T08:13:30.990" lastbatchcompleted="2019-07-31T08:13:29.427" lastattention="1900-01-01T00:00:00.427" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="14944" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="359850034" currentdb="9" currentdbname="XXX" lockTimeout="4294967295" clientoption1="673415200" clientoption2="128056">
   <executionStack>
    <frame procname="CreatePartition" line="30" stmtstart="2052" stmtend="2218" sqlhandle="0x03000900d670d75323ec7f0094aa000001000000000000000000000000000000000000000000000000000000">
ALTER PARTITION FUNCTION...    </frame>
    <frame procname="CreatePartitions" line="26" stmtstart="1618" stmtend="1788" sqlhandle="0x030009000f95cb5424ec7f0094aa000001000000000000000000000000000000000000000000000000000000">
EXEC CreatePartition     </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1422628111]   </inputbuf>
  </process>
  <process id="process1d4593028c8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 9 DATA_SPACE(data_space_id = 65601), lockPartitionId = 0" waittime="21105" ownerId="359870992" transactionname="sqlsource_transform" lasttranstarted="2019-07-31T08:14:29.460" XDES="0x1d4911c8de0" lockMode="Sch-S" schedulerid="4" kpid="5052" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-07-31T08:14:29.460" lastbatchcompleted="2019-07-31T08:14:29.447" lastattention="1900-01-01T00:00:00.447" clientapp=".Net SqlClient Data Provider" hostname="XXX" hostpid="14944" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="359870992" currentdb="9" currentdbname="XXX" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="GetXXX" line="17" stmtstart="-1" sqlhandle="0x03000900abe7e85629eb7f0094aa000001000000000000000000000000000000000000000000000000000000">

CREATE PROC [dbo].[GetXXX]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ...    </frame>
   </executionStack>
   <inputbuf>
Proc [Database Id = 9 Object Id = 1458104235]   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="0" objid="290100074" subresource="FULL" dbid="9" objectname="TableXXX" id="lock1d4eeca4f80" mode="Sch-S" associatedObjectId="290100074">
   <owner-list>
    <owner id="process1d4593028c8" mode="Sch-S" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d3ea515848" mode="Sch-M" requestType="wait" />
   </waiter-list>
  </objectlock>
  <metadatalock subresource="DATA_SPACE" classid="data_space_id = 65601" dbid="9" lockPartition="0" id="lock1d48e40dc00" mode="Sch-M">
   <owner-list>
    <owner id="process1d3ea515848" mode="Sch-M" />
   </owner-list>
   <waiter-list>
    <waiter id="process1d4593028c8" mode="Sch-S" requestType="wait" />
   </waiter-list>
  </metadatalock>
 </resource-list>
</deadlock>
3
8

The process with high deadlock priority was chosen as a victim...

I've run into this before with maintenance operations - specifically index reorgs, as described on my blog here: Deadlock Victim Choice in SQL Server - An Exception?

In that case, I was able to reproduce the problem, having a process with the highest deadlock priority (10) chosen as the victim when deadlocked with a process with the lowest deadlock priority (-10).

Is there a way to prevent a process with high deadlock priority from becoming a victim?

The only solution I'm aware of is to avoid doing these operations (partition switching, altering partition functions, index maintenance) during times of heavy user activity. Preferably during some kind of maintenance window.

It seems like there's something internal to the way deadlock victims are chosen that "knows" one of the operations is a maintenance task, and is hard-coded in some way to choose that as the victim over a non-maintenance user query. That's all just speculation though based on my observations.


...probably because of high log usage compared to the other process

For what it's worth, this is actually the opposite of how deadlock victims are normally chosen. The process with the higher log usage is not chosen as the victim, because it would require more work to roll it back.


There's an interesting example similar to yours on Stack Overflow as well, which implies that acquisition of a SCH-M lock is part of the problem (which would make sense, given the types of operations we're discussing): Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.