Deadlock priority

5

February 8, 2017 by Kenneth Fisher

Everyone deals with deadlocks from time to time. But sometimes we need to control who’s the deadlock victim and who isn’t. For example, I’m doing a big delete on a table in a 24×7 environment, I can’t afford downtime to do it so I’m doing my delete in small chunks to reduce transaction size and blocking time. My delete needs to happen but I’m in no hurry and I really can’t afford to deadlock some other transaction. So how do I make sure?

Or on the other hand, I’m running an update that absolutely has to happen right now. It’s going to take a bit and I can’t afford the time for it to be started over. A deadlock would be a disaster. What do I do?

That’s where deadlock priority comes into play.

SET DEADLOCK_PRIORITY HIGH; -- This is the same as a priority of 5.
SET DEADLOCK_PRIORITY LOW; -- This is the same as a priority of -5.
SET DEADLOCK_PRIORITY 6;

This setting doesn’t affect anything other than deadlocks. Basically, the higher deadlock priority wins and the lower becomes the deadlock victim. Now everything starts out with a deadlock priority of NORMAL or 0. So, if necessary, with a little planning, you can easily control who the victim will be.

I do want to say, though, that this setting should be used sparingly. If you decide to set every connection to HIGH then you may as well not have bothered. In order for DEADLOCK_PRIORITY to have any effect, there have to be different priorities.

5 thoughts on “Deadlock priority

  1. […] by the system trying to do an auto statistics update. One of the answers suggested changing the deadlock priority before running a series of ALTERs to set the database OFFLINE, back ONLINE, and then finally back […]

  2. John Mitchell says:

    Nice little reminder, Kenneth – thanks. You have to bear in mind that, where deadlock priorities are equal or not set, SQL Server will kill the transaction(s) that will be cheapest to roll back. Therefore, if you are seeing a lot of deadlocks, and you set deadlock priority, it could have an impact on performance.

    John

  3. Bredon Hill says:

    SSMS has option to set a default Deadlock Priority under Options/Query Execution/SQL Server/Advanced. I set this to ‘Low’ as well as the Transaction Isolation Level to ‘Read Uncommitted’. Can override defaults if needed but ad-hoc data investigation works well enough in our environment with this smaller footprint.

    • I can see the low deadlock priority (although I’m not 100% certain I agree with it) but I wouldn’t do the read uncommitted as a default. This is equivalent to NOLOCK and if you are doing anything important from your queries and forget to override it you might run into problems.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,682 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: