SQL Server › Deadlocks

What error does an application receive when its transaction is chosen as a deadlock victim, and how should the application respond?

Intermediate

Answer

The victim transaction receives error number 1205, and the application should catch this specific error and automatically retry the entire transaction after a short delay, since the transaction was rolled back through no fault of its own and will often succeed on a second attempt.

BEGIN TRY
  BEGIN TRANSACTION;
  UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 1205
    -- retry logic goes here
  ROLLBACK TRANSACTION;
END CATCH;

Real-world example A payment application automatically retries a transfer that failed with error 1205, successfully completing the transfer on its second attempt after the conflicting transaction had already finished.

Common follow-ups: How many times should an application reasonably retry before giving up?;Should there be a delay between retry attempts, and if so, how long?

Error Handling with TRY CATCH;Transactions & ACID