SQL Server deadlock error handling

Suggested Videos
Part 80 - Logging deadlocks in sql server
Part 81 - SQL Server deadlock analysis and prevention
Part 82 - Capturing deadlocks in SQL Profiler



In this video we will discuss how to catch deadlock error using try/catch in SQL Server.



Modify the stored procedure as shown below to catch the deadlock error. The code is commented and is self-explanatory.

Alter procedure spTransaction1

as 

Begin 

    Begin Tran

    Begin Try 

         Update TableA Set Name = 'Mark Transaction 1' where Id = 1 

         Waitfor delay '00:00:05' 

         Update TableB Set Name = 'Mary Transaction 1' where Id = 1 

         -- If both the update statements succeeded.

         -- No Deadlock occurred. So commit the transaction.

         Commit Transaction

         Select 'Transaction Successful'   

    End Try

    Begin Catch

         -- Check if the error is deadlock error

         If(ERROR_NUMBER() = 1205)

         Begin

             Select 'Deadlock. Transaction failed. Please retry'

         End

         -- Rollback the transaction

         Rollback

    End Catch   

End

Alter procedure spTransaction2 

as 

Begin 

    Begin Tran

    Begin Try

         Update TableB Set Name = 'Mary Transaction 2' where Id = 1

         Waitfor delay '00:00:05'

         Update TableA Set Name = 'Mark Transaction 2' where Id = 1

         Commit Transaction

         Select 'Transaction Successful'   

    End Try

    Begin Catch

         If(ERROR_NUMBER() = 1205)

         Begin

             Select 'Deadlock. Transaction failed. Please retry'

        End

         Rollback

    End Catch   

End

After modifying the stored procedures, execute both the procedures from 2 different windows simultaneously. Notice that the deadlock error is handled by the catch block.

In our next video, we will discuss how applications using ADO.NET can handle deadlock errors.