SQL Server tutorial for beginners - SQL Server deadlock error handling

In this video we will discuss how to catch deadlock error using try/catch in SQL Server. Text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-error-handling.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-error-handling_30.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists 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. Link for all dot net and sql server video tutorial playlists https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1 Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2015/08/sql-server-deadlock-error-handling.html