Handling deadlocks in ado net Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 84 of sequences of a
tutorial in this video we'll discuss how
to handle deadlock errors in an ad or
dotnet application
this is continuation to Path 83 so
please watch part 83 before proceeding
at the moment we've got two stored
procedures here we discussed both of
these two procedures in detail in our
previous video session what we know is
if we execute these two procedures
simultaneously we are guaranteed to get
a deadlock error one of the transactions
will be made the deadlock victim while
the other transaction completes
successfully the transaction that's made
the deadlock victim will display the
12:05 era now what we want to do is we
want to call these stored procedures
from an asp.net web application and when
a deadlock happens we want to handle
that from the asp.net application so
let's see how to do that here I have a
new asp.net webforms application so far
I have included the connection string
with the web config file so this
connection string is pointing to the
sample DB and within the sample DB
database we've got the stable stable a
at and table B and both the stored
procedures espy transaction 1 and SP
transaction - and this is p.net web
project I also have added two waveforms
waveform 1 and their form 2 and on the
waveform at the moment we don't have
anything what I'm going to do is drag
and drop a button control onto the
webform and I'm going to change the text
on this button control I'm going to call
this update table a and then table Bay
and let's flip this waveform to the
design mode and when we double click the
button it should generate a click event
handler we're going to write some ad or
dotnet code so I'm going to bring in a
do dotnet name spaces so we need system
dot configuration system dot data and
system dot data dot sequel client and we
have the connection string within the
web config file so we have to read that
and in our code behind file I'm going to
create a variable
the click event handler and use the
configuration manager class and read the
connection string so the name of the
connection string within a web config
file is DB CS so let's use that name
right here and then on that I'm going to
call connection string so that should
read the connection string from web
config file I'm going to use the using
block here so using I'm going to create
a signal connection object corn equals
new sequel connection object and to this
let's pass the connection string that we
have read from the web.com tree file now
let's go ahead and create a sequel
command or object let's call it CMD
equals new sequel command and using the
sequel command object we want to execute
our first stored procedure espy
transaction 1 so that's the name of the
stored procedure that we want to execute
and we want this command object to use
this connection object that we have
created it since the command object is
executing a stored procedure we have to
tell that to the command object so
command or command type equals command
type dot store procedure and then let's
open the connection and we want to
execute our command since this is an
update statement I'm going to use the
execute non query method of the command
object ok since we have included this
connection object in this using block we
don't have to explicitly close that okay
so that's our webform 1 so this is
updating table a first and then table B
because when we call this SP transaction
1 stored procedure it's updating table a
first and then table B now let's do the
same thing on all their form 2 door aspx
so let's get to waveform 2 dot aspx and
all I'm going to do is copy the HTML
that we have here on the form 2 dot aspx
and here I'm going to change the text on
the button I'm going to say update table
B and then table hey and let's get to
the code behind file and we are going to
copy the code that we have in web form 1
dot aspx and
we're from Tudor aspx dot CS okay all
right now from web form to what we want
to do is we want to call a different
store procedure that is sp transaction
two now we have got a lot of compilation
errors here that's basically because we
need to include those 380 or Orton
namespaces as well so I'm going to copy
those three namespaces and paste them
right here all right
so let's save all these changes and
let's navigate to webform one dot aspx
and let's navigate now to perform two
dot aspx all right
so now I'm going to click update table a
and then table B and simultaneously I'm
going to click the other update button
now one of the transactions should
complete successfully while the other
should fail with an error look at that
you know this transaction completed
successfully and the other one has
displayed this yellow screen of death
because this stood procedure that is
whatever this waveform one dot aspx was
executing that's made the deadlock
victim and we get that deadlock error
since we have not handled it you know by
default it's going to display this
yellow screen of that now let's see how
to handle this deadlock error inside our
asp.net application so let's do that
first in over from one or aspx so we are
in the code-behind file now what I'm
going to do is wrap all this code inside
a try-catch block
so let's include a try block here lose
the try block right there and include at
respond in catch block and I'm going to
catch the sequel exception and let's
call the object e^x so now when we are
executing this code if it's all
successful then we want to display a
meaningful message to the user let's say
we want to say transaction successful
and to display the message what I'm
going to do
is include a break element here and then
a label so let's drag and drop a label
control onto the waveform let's remove
this default text and within the
code-behind file so if this code
completes successfully then I'm going to
say label 1 dot text equals transaction
successful and let's also change the
color of the label so label 1.4 color
equal system dot drawing dot color dot
green ok now if at all if there is an
exception if there is a sequel exception
we want to catch that and you know they
could be variety of sequel exceptions
now we are specifically interested in if
it's a deadlock error so I'm going to
use the exception objects number
property to check the number of the
sequel exception now remember when we
get a deadlock error what is the error
number it is 1205 so I'm going to check
the number property and if it is equal
to 1200 5 what that means that means we
have a deadlock error in that case what
do we want to do we want to display a
different message in the label so let's
say we want to say deadlock transaction
failed please retry okay and let's say
we want to change the color to red maybe
and if it's not a deadlock error if it's
any other sequel exception error then
you know we just want to display
whatever is the exception message in the
label so in that case what I'm going to
do is label one dot text equals I'm
going to use the exception objects
message property and display the error
you know the error message and you know
this for color property is duplicated so
I'm going to actually take that out from
if and else block and put it right there
and remove this as well all right now
let's do the same thing on webform to
dot aspx as well so first of all we need
a label control so I'm going to copy
this break element and label and paste
it on our web form to dot aspx as well
similarly let's copy the code that we
have here so I'm going to copy the
entire try and catch block inside the
button one click event handler of a
webform to dot aspx so let's paste the
code that we have copied right here and
we have to change the name of the stored
procedure to the SP transaction - all
right
so let's save all our changes rebuild
the application and reload both the
pages so I am going to reload that from
one and perform two now let's execute
you know first stored procedure one and
then simultaneously stored procedure -
so shortly one of the transactions
should be successful and the other one
should fail so this is the one that
successful this is the one that has
failed and look at that it displays that
meaningful message dead log transaction
failed please
retry now once we click the button again
look at that the transaction will be
resubmitted and it should now complete
successfully without any error so
transaction successful thank you for
listening and have a great day