question

lamda avatar image
lamda asked

Implementing an error handler that traps error message 1205 and allows an application to handle the deadlock.

I was reading on handling deadlock and I came up with this page in Microsoft "Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). By resubmitting the query automatically, the user does not need to know that a deadlock occurred." Can someone please explain how I can implement this?
deadlock
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
Here you go: -- Retry logic DECLARE @retries INT ; SET @retries = 4 ; WHILE ( @retries > 0 ) BEGIN -- Use TRY/CATCH to handle the deadlock BEGIN TRY -- place sql code here /*Business Logic goes here*/ SET @retries = 0 ; SELECT 'Successful' ; END TRY BEGIN CATCH -- Error is a deadlock IF ( ERROR_NUMBER() = 1205 ) BEGIN SET @retries = @retries - 1 ; SELECT 'Deadlock occurred retrying '+CAST(@retries AS VARCHAR)+' more times.' ; END -- Error is not a deadlock ELSE BEGIN DECLARE @ErrorMessage NVARCHAR(4000) ; DECLARE @ErrorSeverity INT ; DECLARE @ErrorState INT ; SELECT @ErrorMessage = ERROR_MESSAGE() , @ErrorSeverity = ERROR_SEVERITY() , @ErrorState = ERROR_STATE() ; -- Re-Raise the Error that caused the problem RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ) ; SET @retries = 0 ; END -- Check transaction state and roll back if necessary IF XACT_STATE() 0 ROLLBACK TRANSACTION ; END CATCH ; END
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

lamda avatar image
lamda answered
Thank you @ DenisT
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.