SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Deadlock priority

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.

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.

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: deadlocking, microsoft sql server, T-SQL


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...