Deadlock priority
5February 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.
SQL Studies 
[…] 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 […]
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
Well, if you are seeing a lot of deadlocks you probably have an issue that needs to be resolved. Changing the deadlock priority is just going to mask that issue.
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.