SQL Server Interview Question #258
How should an application handle deadlock error 1205?
Advanced Transactions & Concurrency Senior Advanced
Quick Interview Answer
SQL Server chooses one transaction as the deadlock victim, rolls it back, and returns error 1205.
Applications should generally treat this as a transient concurrency failure and retry the entire logical transaction when the operation is safe and idempotency/business rules permit it. Retry policies normally use a limited number of attempts with a delay or backoff.
Retrying is important, but repeated deadlocks also require root-cause analysis. Retry logic should not hide a persistent transaction-design problem.
Detailed Explanation
SQL Server chooses one transaction as the deadlock victim, rolls it back, and returns error 1205.
Applications should generally treat this as a transient concurrency failure and retry the entire logical transaction when the operation is safe and idempotency/business rules permit it. Retry policies normally use a limited number of attempts with a delay or backoff.
Retrying is important, but repeated deadlocks also require root-cause analysis. Retry logic should not hide a persistent transaction-design problem.