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;
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
add a comment |
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
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 thatDEADLOCK_PRIORITY
can be set as high as10
, whereasHIGH
is equivalent to5
on the scale of-10
to10
– LowlyDBA
7 hours ago
add a comment |
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
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
sql-server sql-server-2016 deadlock
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 thatDEADLOCK_PRIORITY
can be set as high as10
, whereasHIGH
is equivalent to5
on the scale of-10
to10
– LowlyDBA
7 hours ago
add a comment |
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 thatDEADLOCK_PRIORITY
can be set as high as10
, whereasHIGH
is equivalent to5
on the scale of-10
to10
– 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
add a comment |
1 Answer
1
active
oldest
votes
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?
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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?
add a comment |
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?
add a comment |
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?
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?
answered 7 hours ago
Josh DarnellJosh Darnell
11.1k3 gold badges27 silver badges57 bronze badges
11.1k3 gold badges27 silver badges57 bronze badges
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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 as10
, whereasHIGH
is equivalent to5
on the scale of-10
to10
– LowlyDBA
7 hours ago