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