This is a code snippet I have used where I currently work to handle deadlocks:
-- The following snippet is for building deadlock handling.
declare @ErrorNumber int,
@ErrorLine int,
@ErrorMessage nvarchar(4000),
@ErrorProcedure nvarchar(128),
@ErrorSeverity int,
@ErrorState int;
while 1 = 1
begin
begin transaction;
begin try;
-- <-- code goes here -->
commit;
break;
end try
begin catch
rollback;
select
@ErrorNumber = ERROR_NUMBER(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorNumber = 1205 -- Trap deadlock
begin
WAITFOR DELAY '00:00:00.05' -- Wait for 50 ms
continue;
end
else begin
-- All other errors
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
end
end catch
end