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.blogspo...
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.
/ @aarvikitchen5572
Slides
http://csharp-video-tutorials.blogspo...
All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...
All SQL Server Slides
http://csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
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/kudvenka...
Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspo...
Sur cette page du site, vous pouvez voir la vidéo en ligne SQL Server deadlock error handling durée heure minute seconde en bonne qualité , qui a été Téléchargé par l'utilisateur kudvenkat 30 août 2015, Partagez le lien avec vos amis et connaissances, sur youtube cette vidéo a déjà été regardée 66,233 fois et il a aimé 329 téléspectateurs. Bon visionnage!
