SQL Server deadlock victim selection Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 79 of signal server
tutorial in this video we'll discuss how
sequel server detects dead logs what
happens when a deadlock is detected
what is deadlock priority and finally
what's the criteria that sequel server
uses to choose a deadlock with them when
there is a deadlock this is continuation
to about 78 so please watch part 78
before proceeding so how does sequel
server detects dead logs in sequel
server there is a background thread
called lock monitor thread this thread
runs every 5 seconds by default to
detect if there are any dead locks
if the lock monitored thread finds dead
logs the deadlock detection interval
will drop from the default 5 seconds to
as low as 100 milliseconds depending on
the frequency of deadlocks if the lock
monitor thread stops finding dead locks
then the database engine increases the
intervals between searches back to the
default 5 seconds so what happens when a
deadlock is detected when a deadlock is
detected the database engine ends the
deadlock by choosing one of the threads
as the deadlock with them the deadlock
wickham's transaction is then rolled
back and returns at Willow 5 error to
the application rolling back the
transaction of the deadlock victim
releases all the logs held by that
transaction this allows the other
transactions to become unblocked and
move forward now let's understand what
is deadlock priority by default sequel
server chooses a transaction as the
deadlock victim that is least expensive
to rollback
however a user can specify the priority
of sessions in a deadlock situation
using the set deadlock priority
statement the session with the lowest
deadlock priority is then chosen as the
deadlock victim here is an example of
how to set deadlock priority if you
don't specify deadlock priority
explicitly then the default is normal
now we can set this deadlock priority to
low normal or high we can also set it to
an integer value in the range of
negative 10 to positive 10 low maps to
negative 5 normal to 0 and high to
positive 5 now let's understand the
criteria that
sequel server uses to choose a deadlock
victim
if the deadlock priority is different
then the session with the lowest
priority is selected as the deadlock
victim if both the sessions have the
same priority then the transaction that
is least expensive to rollback is
selected as the victim if both the
sessions have the same deadlock priority
and the same cause to rollback then a
victim is chosen randomly let's
understand this with a couple of
examples so here I have already set up
two tables table a and table B and at
the moment table a has got five rows in
it and table B has got one row in it and
here is the sequel script to create
those tables and populate it with test
data
I'll have the script available on my
blog in case you need it now
I also have two more instances of sequel
civil management studio running and
we've got two transactions here and both
the transaction are trying to update
both the tables table a and table B
transaction 1 once to update table a
first and then table B whereas
transaction 2 wants to update table B
first and then table a now at the moment
we have not explicitly said the deadlock
priority for any of the transactions so
by default it is normal for both the
transactions so now let's execute part
of our transaction 1 and look at this
you know in table a we have got 5 rows
so basically we are setting name to name
plus transaction 1 okay and we are
updating all the 5 rows in table a and
we have only executed part of
transaction 1 now let's execute part of
our transaction 2 so here transaction 2
is first updating table B and they will
be has got only one row so one row
affected now when we try to execute the
second update statement within
transaction 1 it will be blocked because
transaction 2 has already acquired a
lock on that table
now if we try to execute the second
update statement of transaction - there
will be a deadlock situation ok now when
there is a deadlock situation here which
transaction will be chosen as the
deadlock victim
in this case transaction two will be
chosen as the deadlock victim because it
is the one that is least expensive to
roll back because this transaction has
updated only one row so we can very
easily sequence server can very easily
rollback that one row as opposed to
rolling back five rows that's why it's
going to choose transaction 2 as the
deadlock victim so let's look at that in
action so now the background thread is
running and it has detected the deadlock
and it has identified transaction 2 is
the one that is least expensive to
rollback and that's why it has selected
that as a deadlock victim ruled the
transaction back and notice we get that
1205 error and transaction 1 let's go
ahead and commit our transaction 1 and
if we look at the data in both the
tables table a and table B it should say
you know named transaction 1 in every
row of both the tables so let's look at
that now so let's select the data and
look at that name of the person and then
it appends transaction 1 2 all the rows
alright
now let's set deadlock priority
explicitly and see what's going to
happen so first of all let's actually go
back to this vendor and put the data
back in the state it was before we
started this transaction so I'm going to
truncate the data that's present in both
the tables and then insert it back so
insert into table a and then into table
B and then let's just select the data to
make sure we have just the names of the
people all right now let's go back to
our transaction windows and I'm going to
set deadlock priority for transaction 2
so sad
deadlock underscore priority and let's
set that to high and we are not setting
deadlock priority for transaction 1 so
it's going to stay at the default and
that is normal so in this case
transaction to deadlock priority is high
so when there is a deadlock
now in this case transaction one should
be chosen as deadlock victim so let's go
ahead and execute part of our
transaction 1 and part of our
transaction - and then the second update
statement in transaction 1 and the
second update statement in transaction 2
so they should be a deadlock and notice
that transaction 1 now is chosen as the
deadlock with them because it has you
know a lawyer deadlock priority than
transaction 2 okay so let's go ahead and
commit this transaction and let's select
our data now to make sure so now it
should have updated all the names are to
name and transaction 2 let's check if
that's the case and look at that here's
the first example that we just discussed
and the second example where we have
said the deadlock priority explicitly
thank you for listening and have a great
day