Deadlock Priority High Chosen as deadlock victimShared and IX locks causing deadlock (Sql server)Multi-victim deadlock in SQL ServerLong running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?Database Full Backup fails being deadlock victimHow to set the deadlock priority for a maintenance task created trough the graphical interfaceDeadlock — Way to make this better?If a Parallelism Exchange Event deadlock is victim-less, is it a problem?Troubleshooting Deadlock, Victim holding on to shared lockDeadlock ExplanationSQL Extended Events Session for deadlock detection

What does VB stand for?

Is it true that control+alt+delete only became a thing because IBM would not build Bill Gates a computer with a task manager button?

Did WWII Japanese soldiers engage in cannibalism of their enemies?

How to halve redstone signal strength?

What are the examples (applications) of the MIPs in which the objective function has nonzero coefficients for only continuous variables?

How do I get the =LEFT function in excel, to also take the number zero as the first number?

Why can I log in to my Facebook account with a misspelled email/password?

How can glass marbles naturally occur in a desert?

Capacitors with a "/" on schematic

I was contacted by a private bank overseas to get my inheritance

Does a 4 bladed prop have almost twice the thrust of a 2 bladed prop?

Is DC heating faster than AC heating?

Why should public servants be apolitical?

If someone else uploads my GPL'd code to Github without my permission, is that a copyright violation?

Was Richard I's imprisonment by Leopold of Austria justified?

Can you use the Help action to give a 2019 UA Artillerist artificer's turret advantage?

How do these cubesats' whip antennas work?

What is the purpose of this mount on the upper suspension arm?

Why should I "believe in" weak solutions to PDEs?

Does this smartphone photo show Mars just below the Sun?

Our group keeps dying during the Lost Mine of Phandelver campaign. What are we doing wrong?

Does the Voyager team use a wrapper (Fortran(77?) to Python) to transmit current commands?

Is there a drawback to Flail Snail's Shell defense?

Secure my password from unsafe servers



Deadlock Priority High Chosen as deadlock victim


Shared and IX locks causing deadlock (Sql server)Multi-victim deadlock in SQL ServerLong running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this?Database Full Backup fails being deadlock victimHow to set the deadlock priority for a maintenance task created trough the graphical interfaceDeadlock — Way to make this better?If a Parallelism Exchange Event deadlock is victim-less, is it a problem?Troubleshooting Deadlock, Victim holding on to shared lockDeadlock ExplanationSQL Extended Events Session for deadlock detection






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








3















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









share|improve this question


























  • What build you are running sql server 2016 on ? Can you post the deadlock xml ? SCH-M is incompatible with SCH-S locks. Was this some alter table or partition tsql that was running ?

    – Kin Shah
    8 hours ago











  • this will help Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?

    – Kin Shah
    8 hours ago






  • 1





    Without knowing too much for context, its worth nothing that DEADLOCK_PRIORITY can be set as high as 10, whereas HIGH is equivalent to 5 on the scale of -10 to 10

    – LowlyDBA
    7 hours ago

















3















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









share|improve this question


























  • What build you are running sql server 2016 on ? Can you post the deadlock xml ? SCH-M is incompatible with SCH-S locks. Was this some alter table or partition tsql that was running ?

    – Kin Shah
    8 hours ago











  • this will help Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?

    – Kin Shah
    8 hours ago






  • 1





    Without knowing too much for context, its worth nothing that DEADLOCK_PRIORITY can be set as high as 10, whereas HIGH is equivalent to 5 on the scale of -10 to 10

    – LowlyDBA
    7 hours ago













3












3








3


0






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









share|improve this question
















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






sql-server sql-server-2016 deadlock






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 6 hours ago









Max Vernon

55.2k13 gold badges119 silver badges246 bronze badges




55.2k13 gold badges119 silver badges246 bronze badges










asked 8 hours ago









DBKDBK

1015 bronze badges




1015 bronze badges















  • What build you are running sql server 2016 on ? Can you post the deadlock xml ? SCH-M is incompatible with SCH-S locks. Was this some alter table or partition tsql that was running ?

    – Kin Shah
    8 hours ago











  • this will help Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?

    – Kin Shah
    8 hours ago






  • 1





    Without knowing too much for context, its worth nothing that DEADLOCK_PRIORITY can be set as high as 10, whereas HIGH is equivalent to 5 on the scale of -10 to 10

    – LowlyDBA
    7 hours ago

















  • What build you are running sql server 2016 on ? Can you post the deadlock xml ? SCH-M is incompatible with SCH-S locks. Was this some alter table or partition tsql that was running ?

    – Kin Shah
    8 hours ago











  • this will help Why would SQL Server's SET DEADLOCK_PRIORITY HIGH not be honored?

    – Kin Shah
    8 hours ago






  • 1





    Without knowing too much for context, its worth nothing that DEADLOCK_PRIORITY can be set as high as 10, whereas HIGH is equivalent to 5 on the scale of -10 to 10

    – LowlyDBA
    7 hours ago
















What build you are running sql server 2016 on ? Can you post the deadlock xml ? SCH-M is incompatible with SCH-S locks. Was this some alter table or partition tsql that was running ?

– Kin Shah
8 hours ago





What build you are running sql server 2016 on ? Can you post the deadlock xml ? SCH-M is incompatible with SCH-S locks. Was this some alter table or partition tsql that was running ?

– Kin Shah
8 hours ago













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

– Kin Shah
8 hours ago





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

– Kin Shah
8 hours ago




1




1





Without knowing too much for context, its worth nothing that DEADLOCK_PRIORITY can be set as high as 10, whereas HIGH is equivalent to 5 on the scale of -10 to 10

– LowlyDBA
7 hours ago





Without knowing too much for context, its worth nothing that DEADLOCK_PRIORITY can be set as high as 10, whereas HIGH is equivalent to 5 on the scale of -10 to 10

– LowlyDBA
7 hours ago










1 Answer
1






active

oldest

votes


















4















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?






share|improve this answer



























    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f244801%2fdeadlock-priority-high-chosen-as-deadlock-victim%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    4















    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?






    share|improve this answer





























      4















      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?






      share|improve this answer



























        4












        4








        4








        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?






        share|improve this answer














        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?







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 7 hours ago









        Josh DarnellJosh Darnell

        11.1k3 gold badges27 silver badges57 bronze badges




        11.1k3 gold badges27 silver badges57 bronze badges






























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f244801%2fdeadlock-priority-high-chosen-as-deadlock-victim%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Invision Community Contents History See also References External links Navigation menuProprietaryinvisioncommunity.comIPS Community ForumsIPS Community Forumsthis blog entry"License Changes, IP.Board 3.4, and the Future""Interview -- Matt Mecham of Ibforums""CEO Invision Power Board, Matt Mecham Is a Liar, Thief!"IPB License Explanation 1.3, 1.3.1, 2.0, and 2.1ArchivedSecurity Fixes, Updates And Enhancements For IPB 1.3.1Archived"New Demo Accounts - Invision Power Services"the original"New Default Skin"the original"Invision Power Board 3.0.0 and Applications Released"the original"Archived copy"the original"Perpetual licenses being done away with""Release Notes - Invision Power Services""Introducing: IPS Community Suite 4!"Invision Community Release Notes

            Canceling a color specificationRandomly assigning color to Graphics3D objects?Default color for Filling in Mathematica 9Coloring specific elements of sets with a prime modified order in an array plotHow to pick a color differing significantly from the colors already in a given color list?Detection of the text colorColor numbers based on their valueCan color schemes for use with ColorData include opacity specification?My dynamic color schemes

            Ласкавець круглолистий Зміст Опис | Поширення | Галерея | Примітки | Посилання | Навігаційне меню58171138361-22960890446Bupleurum rotundifoliumEuro+Med PlantbasePlants of the World Online — Kew ScienceGermplasm Resources Information Network (GRIN)Ласкавецькн. VI : Літери Ком — Левиправивши або дописавши її