February 15, 2012 at 2:39 pm
I had a deadlock today and it got me to wondering how SS2008R2 determines the default DEADLOCK_PRIORITY for queries. I caught the deadlock with Profiler and the UPDATE statement had a deadlock priority of 0 but the ALTER INDEX . . . REORGANIZE had a deadlock priority of -5 so it was killed. That's what I wanted to happen, but since I had not explicitly set those values, how did they come into being?
Thanks!
-John
February 15, 2012 at 3:12 pm
The one that will be the least work to roll back will be the victim, basically the one that's logged the least changes in the current transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 15, 2012 at 3:23 pm
Gail, that's true for transactions with the same deadlock priority, but it seems to me that SS2008R2 is picking winners by assigning a non-zero default deadlock priority to some queries. I'd like to know how it made one -5 and the other 0. I have several traces where both queries had a priority of 0, but not always and in queries where I did not explicitly set the priority.
February 15, 2012 at 3:46 pm
It could well be setting the deadlock priority implicitly based on the amount that need to be rolled back if the user hasn't explicitly set one already. Alter Index Reorganise would be very quick to roll back, so unless the update was trivial (a few rows) it would be the one picked based on rollback work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply