March 23, 2012 at 2:00 am
How SQL server choosing the Deadlock Victim???~~??
March 23, 2012 at 2:06 am
Which session is chosen as the deadlock victim depends on each session's deadlock priority:
If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim. For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll back.
If the sessions have different deadlock priorities, the session with the lowest deadlock priority is chosen as the deadlock victim.
March 23, 2012 at 3:11 am
THnaks
what about if they also has the same "log Used"?
March 23, 2012 at 3:13 am
each action has a cost against it, its the SPID with the lowest cost which is the deadlock victim
if they are exactly the same in each and every way then (im sure someone will correct me) its a random pick of either SPID by the engine.
March 23, 2012 at 4:15 am
mc2601 (3/23/2012)
THnakswhat about if they also has the same "log Used"?
Then, assuming they deadlock priorities are the same and they are both user processes, one will be picked at random.
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
March 23, 2012 at 4:17 am
anthony.green (3/23/2012)
each action has a cost against it, its the SPID with the lowest cost which is the deadlock victim
It's not the cost of the action, it's the cost of the rollback. In a deadlock between a select that ran 5 hours and a single row update, the select will be the one rolled back.
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply