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.