THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did you know? -- New Deadlock Priorities

Back in SQL Server 2000, and all previous versions, the set option SET DEADLOCK_PRIORITY had 2 values, LOW and NORMAL, and LOW did not mean that you had lower priority for being selected the victim. Setting this option to LOW was like setting the martyr flag, it meant "If I am involved in a deadlock, I will not be able to live with the shame, so KILL ME". There was no way to set your priority to HIGH. But that was then, this is now. I had actually already written and delivered my updated SQL Server Internals course, when I discovered a change in SQL Server 2005. Fortunately, I had not written the locking chapter of my book yet.

In SQL Server 2005, SET DEADLOCK_PRIORITY allows a process to determine its priority for being chosen as the victim using one of 21 different priority levels, from –10 to 10.  You can still use the value LOW, which  is equivalent to –5, or NORMAL which is the same as 0, or HIGH, which is 5. The default, of course, is NORMAL.

Which session is chosen as the deadlock victim depends on each session's deadlock priority. If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim. If both sessions have set the same deadlock priority, SQL Server selects as the victim the session that is less expensive to roll back.

As I just discovered this new feature (and have now updated my course with this information) I have not had an opportunity to test this new capability in a production environment.

Since everyone has permission to use this option, it will be interesting to see what happens if everyone decides to give themselves the highest priority.

----

Part of what I was looking forward to about blogging was getting comments, and so far that is just not happening. It looks like lots of people are reading my posts, but only one person has commented on each one. If I don't get feedback, I just may lose my motivation to keep posting.  :-)

 

-- Kalen

Published Saturday, September 09, 2006 9:06 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hugo Kornelis said:

Hi Kalen,

The answer to your final question (what will happen if everyone gives themselves the highest priority) is that the end result will be the same as when nobody uses this option. On a deadlock, SQL Server will first compare deadlock priorities; if they are equal, the deadlock victim is the one that's the least expensive to rollback.

So nobody setting a deadlock priority or everybody setting the maximum deadlock priority yields the same end results.
September 9, 2006 1:03 PM
 

Hugo Kornelis said:

Oops, forgot to add....

If you wish to receive more comments, allowing anonymous comments might help. (Though in all honesty, I have anonymous comments enabled on my site, and I'm not exactly swamped with comments either)
September 9, 2006 1:04 PM
 

Matthew Martin said:

That's cool, I'll probably use that.  I would probably put UPDATES/INSERTS on high priority and SELECTS on low priority, but one would imagine that connections just doing SELECTS are the first candidates for deadlock kills anyhow.  Unless deadlock victims are normally chosen at random--

I'm an avid reader of SQL blogs, and even famous sorts like Kim Tripp and Bill Vaugn have bloglines.com subscribers in the single and double digits. (RSS subscriptions being a really rough gauage of readership)

As soon as the book ships, the comments will be numerous to be a hassle.
September 9, 2006 7:26 PM
 

Adam Machanic said:

Kalen,

Be patient -- you just started last week and not many people know you're here yet!  However, you were included in today's Database Daily update from SSC, so that will help a bit.  And as Hugo pointed out, you have anonymous comments disabled at the moment.  If you enable that option, you may see more comments from people who don't want to sign up.  Personally, I have them disabled as I don't want any kind of blog SPAM.
September 10, 2006 2:09 PM
 

Adam Machanic said:

Looking again after signing out, I guess you do have anonymous comments enabled... get ready for the flood ;-)
September 10, 2006 2:10 PM
 

Kalen Delaney said:

Hi Adam

Yes, I know I just started ... but there were all these 'views' and no one was saying anything. Besides, I had a smiley there. :-)  I just wanted readers to know that comments are more than welcome.

And I've certainly got plenty to do while waiting for all the comments to start rolling in.

--Kalen
September 10, 2006 6:20 PM
 

ACALVETT said:

I just heard about your blog and it brought a big smile to my face. Looking forward to reading more great articles.

September 14, 2006 5:28 AM
 

Richard said:

Hi

Slightly nervous about posting in such exalted company!  Will be keeping a close eye on your blog now I have seen it.

September 18, 2006 3:56 AM
 

TKnoob said:

Just saw your blog.  Love your books.  Please continue to blog away.  And thanks.
September 21, 2006 12:32 PM
 

Why 21? said:

What is the point in having 21 possible values when three is enough?
September 25, 2006 4:38 AM
 

Kevin3NF said:

Hi Kalen,

I just stumbled across this site today, and this was the second post I read.  I've been studying deadlocks recently, so this was quite timely.  

Thanks,

Kevin3NF
November 2, 2006 6:08 PM
 

Kalen Delaney said:

In SQL Server Management Studio, the screen where you choose which SET options to enable for all connections

July 17, 2007 6:16 PM
 

Rob said:

This was very helpful Kalen.  Very interesting information.  I am going to take advantage of this in SQL Server 2005 now.

July 23, 2007 7:13 AM
 

kalen said:

I have spent 2 hours looking for an answer and you are the only one who gave it to me. Thank you.

June 20, 2008 3:43 PM
 

Jeremy Odom said:

Is it best to set DEADLOCK_PRIORITY from within your stored procedure or just before the stored proc is called?

June 4, 2012 12:00 PM
 

Kalen Delaney said:

Jeremy

I recommend setting it only in the scope in which you need it. If you only need it for the proc, set it in the proc, and then when the proc is over, it will be automatically reset to the value it was before.

~Kalen

June 4, 2012 12:54 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement