Retry logic for deadlock exceptions Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 85 of secret server
tutorial in this video well disk is
implementing retry logic for deadlock
exceptions this is continuation to part
84 so please watch part 84 before
proceeding now when a transaction fails
due to deadlock we can write some logic
so the system can automatically resubmit
that failed at transaction the deadlocks
usually lasts for a very short duration
so upon resubmitting the failed
transaction it may complete successfully
this is much better from a user
experience standpoint so to achieve this
we're going to make use of these four
technologies c-sharp is p.net sequel
server and jQuery AJAX now let's flip to
issue studio this is the same example
that we worked with in the previous
video session we will be modifying this
example to implement the retry logic the
problem with this example at the moment
is that whenever a transaction is made a
deadlock victim will get an error like
this now the end user has to manually
resubmit that failed at transaction so
don't you think it would be much better
from a user experience standpoint if the
system can automatically resubmit that
failed transaction so let's see how to
achieve this what I have done so far is
I have added this class file to the
project and the name of the file is
result toxeus and within this result
class we've got these three properties
this first property attempts laughte is
going to let us know about the number of
retry attempts left this message
property is going to contain the message
that we want to display to the end user
and this success property is going to
tell us whether the transaction has
completed successfully or not
now the first modification that I'm
going to do is for the code that we have
in the code behind file of this webform
one dot aspx now at the moment notice
all of the logic is present within
button one underscore click even handler
function now I'm going to move this code
into a public function and this public
function is going to return an object of
type result and let's actually make this
function static and let's call this
function call stored procedure because
that's what this function is essentially
going to do it
going to call our secrets of asteroid
procedure SP transaction 1 now we want
this function to be called from
JavaScript so I'm going to decorate this
with system dot web dot services dot web
method attribute and this function is
also going to have an input parameter of
type integer and I'm going to call this
atoms left so we will be passing the
number of retry attempts left as a
parameter to this function now I'm going
to move all this logic inside that
public function so I'm going to cut that
and paste it right here now the first
thing that I'm going to do here is
create an instance of our result class
and I'm going to call that underscore
result because that's the object that we
will be returning from this function and
another thing I am going to do is check
the number of atoms left so if items
left is greater than 0 only then we want
to resubmit that transaction so I'm
going to put all of this logic that we
have here inside that if block so let's
move all of that code inside that if
block all right so now we've got some
compilation errors here that's basically
because this function is a static
function but inside the function we have
instances of label so that's the reason
why we have those compliation errors and
what we are going to do is return the
result object from this function so we
will have to populate the properties of
that result object so if the control
reaches this point then we know that the
transaction has successfully completed
so if the transaction has successfully
completed the first thing that I'm going
to do is populate the message property
of the result object to transaction
successful and the next thing I'm going
to do is sad attempts left property to
zero because look at the condition here
if Adams left is greater than zero then
we are calling this equals of a stored
procedure
right so if the transaction has already
completed successfully then there's no
reason why we want to call that stored
procedure again so that's the reason why
we're setting atoms left to zero that
means when it again tries to call look
at this condition if it's greater than
zero only then this code will be called
otherwise it wouldn't okay so we are
setting atoms left property to zero and
finally since the transaction has
completed successfully I'm going to set
this success property to true all right
so that's what we want to do with the
result object when the transaction
completes successfully in case if there
is an exception a sequel exception and
if the number is 12/5 then we know that
it's a deadlock error in that case we
want to set the message property of the
result object to something like maybe
deadlock occurred and let's say retrying
because we want to retry if there is a
deadlock and we also want to display the
number of attempts left to the end-user
so attempts left whatever are the number
of attempts left now the immediate
question that comes to our mind is how
are we going to get the number of
attempts left now just before this
result dot message what I'm going to do
is underscore result dot items left
equals now the number of atoms left are
coming inside this function as a
parameter right so this parameter
contains that value now if there is a
deadlock exception we want to retry
submitting the transaction again so we
will want to reduce the number of
attempts left by one and that's what we
are going to display here the number of
attempts left equals result dot attempts
left dot two string okay so that's what
we want to do in case if there is a
deadlock exception we'll reduce the
number of retry atoms by one and this is
the message that we want to display now
if there is any other sequel exception
other than a deadlock then
to come into this health blog now here
what I'm going to do is simply throw
that exception back now depending on
what exception has occurred and
depending on your application need you
may you know implement the required
logic there but for the purpose of this
time I am simply going to throw that
exception back if it's any other sequel
exception apart from a deadlock and
finally now if federal if it has come to
this catch block then we know that the
transaction has failed so in that case
I'm simply going to set result dot
success equals false all right so that's
the modification that we need to do
inside this could behind file another
thing we have the result object the
final thing that is left is to return
that result object so let's go ahead and
return the underscore result object okay
now let's go to the aspx page now we
want to call this function from jQuery
so the first thing that we need is a
reference to the jQuery file so I'm
going to drag and drop the jQuery file
on over there form and we are going to
also have a script section script type
equals text slash JavaScript and inside
this let's have our document dot ready
functions a dollar document dot ready
so when the document is ready if you
want to process some JavaScript code all
right so first of all we don't require
the server-side button here because you
know we are going to call the
server-side function using jQuery so
it's enough if we have an HTML button so
I'm going to go to the tool box and drag
and drop an input button so let's drag
this one and that we can change the text
on this button to update table a and
then table B we can get rid of this
button since we are getting rid of that
button we can get rid of the
corresponding even handler function in
the code-behind file as well so we can
delete this safely and let's give this
button an ID let's actually call that
ptn all right
so the first thing that I'm going to do
is hook up click even handle it to this
button so let's use the jQuery ID
selector find the button and associate
click event handler so when the button
is clicked we want to execute some code
which is going to be part of this
anonymous function so what is the first
thing that we want to do whenever
somebody clicks this button we want to
first disable it so that you know the
end user cannot click it two times so
I'm going to simply disable it by
calling dollar this or this refers to
the button and on that I'm going to call
the prop function and the property that
we want to set is disabled and we want
to set that to true so that's the first
thing that we want to do we want to
disable the button now the next thing
that we want to do is within the label
as soon as we click the button within
the label we want to display message
saying updating that is we are updating
the table or you can display any
meaningful message you want now we are
going to refer to this label element in
a couple of times so I'm going to cash
that in a variable so
in the scope in the global scope I'm
going to create a variable let's call
this lvl message equals let's use the
jQuery ID selector find the label using
its ID so we are cashing it in this
variable so that you know every time we
want to refer to that we don't have to
look up in the Dom we can reuse this
variable that way it's much better for
the performance so the next thing that I
want to do is I want to set the text of
the label to something like updating so
that's a visual indication to the user
that the update is in progress and then
I'm going to declare another variable
here and I'm going to call this items
left and I'm not going to initialize it
here I'm going to initialize it within
the button click event so Adams left
equals 5 so when we click the button
irrespective of whether the transaction
is successful or not
we want to give five atoms to retry so
that's the reason why I mean
initializing that to five and then I'm
going to have a separate function here
and let's actually call that update data
so I'm going to call this function so
when we click the button alright now
what if what kind of code are we going
to have within this update data function
so from here we are going to issue an
ajax call to the server-side function
that we have in the code-behind file so
we're going to call this using jQuery
AJAX so in update data function I'm
going to use the Ajax jQuery AJAX
function we need to specify the options
the first option is going to be the URL
the URL to which we want to issue the
request the URL is going to be where
from one dot aspx within this webform in
the code-behind file we have a function
and the name of the function is called
stored procedure so that's the function
that we want to call right so that's the
URL to which we want to issue a request
and what is the type of request that we
want to issue we
specify that using method we want to
issue a post request and then I'm going
to specify the type of content that we
are going to send to the server so I
want to send a JSON string so the
content type is going to be application
forward slash Jason since we specified
that we will be sending Jason string we
need to serialize the data to that
format so the data that we want to send
to the server is a JSON string so we
have a JavaScript object and if you look
at the parameter name in the server-side
function it's Adams left so that's the
parameter to which we want to send the
data so the name of the parameter is
that and to this we need to append the
atoms left and we are going to be going
to get that from from this variable so
Adams left and finally let's close the
closing brace so that's the data we want
to send to the server and what is the
type of data that we are expecting back
from the server we are going to specify
that using data type option and it's
going to be of type JSON when the
request completes successfully if you
want to associate a callback function so
this is the function that will be called
when the request completes successfully
and whatever data the server-side
function is going to send this parameter
right here data is going to receive that
now if the request completes
successfully this is the callback
function that we call that gets called
in case if there is any error processing
the request then we want to associate a
callback function for that as well so
this is the function that gets called if
there is any error and the error object
will be passed to this callback function
now what do we want to do with that we
want to display that in the label so if
at all if there is any other error then
let's go ahead and set the color of the
label to read and LBL message dot text
so the text that we want to display here
is
the error objects response text all
right now
if the request completes successfully
then what do we want to do with the data
that we are going to get now what is the
data that we are going to get we are
going to get back this result object in
a JSON format right so now the first
thing that I'm going to do here is
within our label LBL message dot text so
we want to display whatever message we
have in the result object that we are
going to get one important thing to keep
in mind here is asp.net is going to
append a property called D to the object
that gets passed to this callback
function and now if you want the value
from the message property you simply say
message on that okay so we want to
display the message that we get back
from the server in the label control and
then I am going to also set initialize
this attempts left variable now we have
initialized this items left variable to
a value of five but then each time we
call the server-side function we want to
reduce it right so to do that we are
actually going to get that value from
this data object so data dot d dot and
the name of the property just to avoid
any typos I'm going to copy and paste it
so we have retrieved the number of items
left as well now what do we want to do
I'm going to use if-elsif-else statement
here if data dot d dot success okay what
does that mean
if the request has completed
successfully now when do we set success
to true when the transaction has
completed successfully so if the
transaction has completed successfully
then what do you want to do we want to
do two things now look at this then when
we first click the button we are
immediately disabling it so that you
know double clicks can be prevented but
when the request completes successfully
then we want to enable that button back
so he can issue a second update if he
wishes to do so
so I'm going to find the button using
its ID the idea of the button is P TN
and they are going to set disabled to
false so that's the first thing that I
want to do and then since the
transaction has completed successfully I
am going to set the color of the label
to green so color is going to be green
okay and now let's use else--if now if
the transaction completes successfully
that's what we want to do if the
transaction didn't complete successfully
and if items left is greater than zero
then what do we want to do we want to
retry submitting the transaction so in
that case you know the appropriate
message will be already displayed in the
label and we want to change the color of
the label to red so that it's
immediately evident to the user that
it's an error message and the most
important thing to do is call this
update data function once again so
that's what is going to resubmit the
transaction let's see how so when we
call update data it's going to call
itself again and it's going to call this
call stored procedure function and what
is called stored procedure function
doing it's doing its calling this
suicide transaction right so if items
left is greater than zero we want to
resubmit that transaction by calling you
know this update data which in turn is
going to call the server-side function
which we'll call the sequence of a
stored procedure all right else so the
request did not complete successfully
there are no more retry attempts left
then what do you want to do one thing is
clear we want to change the color of the
label to red but we also want to change
the message because there are no more
retry attempts left so at this point
what I want to display is something like
deadlock occurred
and maybe something like zero items left
please try later
or call customer service whatever is
relevant to your application but
essentially that gives the idea on what
you want to do so there are no more
you know retrace left and we cannot
prevent that deadlock
you know deadlocks are frequently
occurring so probably it's time to call
the customer service you know the
technical team might have already been
aware because they would definitely be
some alerts about these dead logs and
you know they would investigate and find
out what is actually causing so many
dead logs and then we should be able to
reach design of a cord so the chances of
these deadlocks happening can be reduced
all right so this is what is you know
other changes required on our platform
one we need to do similar changes on
their form two dot aspx as well so in
the interest of time what I'm going to
do is I'm actually going to copy all of
this code and paste it in our web form
to dot aspx and I'm going to change you
know whatever we need to change so here
on web form two we first want to update
table B and then table a that's the
first change that we want to do and the
second changes we want to call the
code-behind function that's present in
that form tools code behind okay so
those are the changes that we need to do
as far as the aspx page is concerned
within the code behind file I'm going to
copy and paste whatever code that we
have in this function so let's take that
and paste that within our web form to
code behind instead of this button one
and let's go click event handler so I'm
going to delete that and paste the code
that we have just copied and we need to
you know change the name of the
transaction here to sp transaction two
so web form two is going to call SP
transaction two
and from their form to dot aspx we are
calling the function that's present in
that form to code behind and the text on
the button here it says update table B
and then table B table a because that's
what our second stored procedure does
that is SP transaction - all right so
let's build our solution and let's
reload our webform one dot aspx and
we're form two dollar aspx now let's go
ahead and issue the first update and the
second update look at this as soon as we
click the button at this place
updating and the button is disabled in a
short time one of the transactions
should be made a deadlock victim look at
that and it's retrying and you know this
transaction should complete successfully
look at this I didn't resubmit it it
automatically resubmitted you know when
the transaction failed now let's see
what's going to happen if there are
repeated dead logs you know if we
exhaust all the five retry attempts
let's see what's going to happen and to
simulate those deadlocks what I'm going
to do is intentionally set deadlock
priority of this transaction to high so
I'm going to use set deadlock underscore
priority to high okay so let's go ahead
and alter this procedure now I'm going
to update now as a result of this change
what is going to happen you know
transaction 1 is going to be made always
the deadlock victim isn't there because
by default the transaction 1 deadlock
priority is going to be normal for this
transaction it's high if there is a
deadlock transaction 1 will always be
rolled back and then behind the scenes
from sequel server we can make calls to
this transaction which is going to block
transaction 1 and there will be a
deadlock again and the number of retry
items will be reduced and let's see
what's going to happen when we exhaust
all the five retry attempts so let's
start the first transaction and the
second transaction and while that is in
progress
I'm going to issue another request now
ultimately transaction one look at that
deadlock occurred retrying Adams left
four and eventually this transaction
should complete let's reissue that and
look at that items left three this is
made again the deadlock victim now I'm
going to issue another update now look
at that Adams left his two hits retrying
again there will be another deadlock
so Adams left this one now look at
what's going to happen deadlock occurred
zero attempts left please try later now
it's not going to retry again because it
has exhausted all the retry atoms
because there are repeated deadlocks you
know and there's no point in infinitely
loving you know the resubmit at some
point you'll have to break it and we'll
have to redesign our code so that we
don't have those repeated dead logs now
let's see what's going to happen if
there is any other exception apart from
a deadlock exception and to simulate
that what I'm going to do is I'm going
to change you know maybe the code on
over from one let's say let's actually
change the name of the stored procedure
to something like one five we don't have
SP transaction one five a stored
procedure with that name so it should
through another sequel exception and
let's see what we get so I'm going to
reload our webform one or aspx
and when I click the button look at that
and I could not find stored procedure SP
transaction one five it shows that error
message because what are we doing here
when there is any other exception about
from 1205 that is a deadlock we are
throwing that exception and when the
exception is thrown within the jQuery
code what are we doing you know this is
the function that gets called and we are
displaying the response text and you
know we are not calling the update data
function again so it's going to end
there and then we'll have to fix that
thank you for listening and have a great
day