patternsqlMinor
Deadlock Priority High Chosen as deadlock victim
Viewed 0 times
prioritydeadlockhighchosenvictim
Problem
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:
Here is the deadlock xml:
ALTER PARTITION FUNCTION...
EXEC CreatePartition
Proc [Database Id = 9 Object Id = 1422628111]
CREATE PROC [dbo].[GetXXX]
AS
BEGIN
SET NOCOUNT ON;
SELECT ...
Proc [Database Id = 9 Object Id = 1458104235]
Solution
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
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?Context
StackExchange Database Administrators Q#244801, answer score: 8
Revisions (0)
No revisions yet.