SQL Server deadlock error handling Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 83 of sequel silver
tutorial in this video we'll discuss how
to catch deadlock errors using try-catch
in sequel server at the moment we've got
two stored procedures SP transaction 1
and SP transaction 2 both of these two
procedures are trying to update two
tables table a and table B this stored
procedure SP transaction 1 it's updating
table a first and then table B whereas
this would procedure SP transaction 2 is
doing the update in the reverse order
it's updating table B first and then
table a between both the update
statements we have a delay of 5 seconds
in both the stored procedures so if we
execute both of these stored procedures
simultaneously then we are guaranteed to
get a deadlock let's look at that in
action first let's execute our first
stored procedure and then simultaneously
this second stored procedure in a few
seconds we should have a deadlock one of
the transactions will be made the
deadlock victim and it will you know
display that 12:05 error whereas the
other transaction should complete
successfully so in this instance this is
the transaction that's made the deadlock
victim and we get that 12:05 error
that's the default behavior just like
any other sequel server error deadlock
errors can also be handled using try cat
and let's see how to do that so I'm
going to wrap these update statements
inside begin try and then try block so
let's include begin try and then and try
let's have the corresponding begin catch
and end catch let's format this code a
bit now if both the update statements
succeed then that means we don't have a
problem so we want to commit the
transaction and let's display a
meaningful message to the user let's
simply say transaction
successful now on the other hand if
there is a problem executing these
update statements and now the control
will immediately go to the catch block
and in the catch block I'm going to
check the error number so let's use the
sequence of a error number function so
if error number equal to 12 5 then that
means we have the deadlock error so what
do we want to do if it's a deadlock
error we want to display a different
message to the user so I'm going to say
select deadlock transaction failed and
let's say please retry so the user could
retry his transaction and then we also
want to rollback
so if federal if there is any problem we
want to rollback that transaction we
don't want you know this code to be
executed in half so let's include that
rollback right here now let's do the
same change for our transaction too as
well in the interest of time I have
already typed the code so I'm going to
copy this from the notepad and paste it
right here and the change is very
similar to what we have for stored
procedure 1 so we have the first update
statement and the second update
statement if both of the state update
statements succeed come in the
transaction and display this message to
the user if at all if there is any
problem executing this code then the
control will go to the catch block in
the catch block we are checking the
error number if it's 12:05 then we know
that it's a deadlock error in that case
this is the message that we want to
display to the user and then roll the
transaction back so let's alter both of
these stood procedures and then let's
execute them simultaneously first stored
procedure 1 and simultaneously stored
procedure 2 so again we should have one
of the transactions as the deadlock
victim and now it should display the
error message that we have specified so
in this case this transaction is
successful and look at that it says
transaction successful whatever message
we have specified here whereas this
transaction is method the deadlock
victim so the control went into the
catch block it checked the error number
12:05 so it printed this message
deadlock transaction failed please retry
and then it ruled the transaction back
in our next video we will discuss how to
handle these deadlock errors in any
dotnet application that uses ad or
dotnet thank you for listening and have
a great day