Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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 =
         Waitfor delay '00:00:05' 
         Update TableB Set Name = 'Mary Transaction 1' where Id =
         -- 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.

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.