All 7 Deadlocks questions
More in Deadlocks
What is a deadlock in SQL Server, and why does it happen? What error does an application receive when its transaction is chosen as a deadlock victim, and how should the application respond? How do you capture detailed information about deadlocks that are happening on a server so you can investigate their root cause? What is the difference between a deadlock and normal blocking, and how do you tell them apart when troubleshooting a slow application? How would you read a deadlock graph to identify the exact queries and resources that caused a specific deadlock? What design changes can you make to application code and database access patterns to reduce the frequency of deadlocks?
Advertisement
Back to results for “Transactions & ACID”
SQL Server › Deadlocks

How does SQL Server decide which transaction becomes the deadlock victim?

Beginner

Answer

SQL Server generally chooses the transaction that is cheapest to roll back based on the amount of work already done, unless a session has been given a higher deadlock priority, in which case a lower priority session is chosen as the victim instead, regardless of cost.
SET DEADLOCK_PRIORITY LOW;
-- This session will be chosen as the victim before others
-- with normal or high priority
Real-world example A reporting job is given a low deadlock priority so that if it ever conflicts with a more important order processing transaction, the reporting job is the one automatically rolled back instead.

Common follow-ups: What deadlock priority values are available besides LOW and HIGH?;Does setting a low priority guarantee that session will never cause a deadlock?

Isolation & Locking;Transactions & ACID
All 7 Deadlocks questions