SQL Server deadlock analysis and prevention Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 81 of sequel server
tutorial in this video we'll discuss how
to read and analyze deadlock information
captured in the error log so we can
better understand what's causing these
dead logs and take appropriate actions
to prevent or minimize the occurrence of
these deadlocks this is continuation to
part 80 so please watch part 80 before
proceeding in a previous video session
we discussed that we can use this system
stored procedure to read the error log
so this error log should contain the
information about the deadlock because
in the previous video session we
discussed how to enable deadlock logging
so the deadlock log starts right here so
let's copy all these rows and paste them
in a notepad so we can understand what's
causing this deadlock the first thing
that we have to understand here is that
the deadlock information in the log has
three sections deadlock victim section
process list and resource list the
deadlock victim section contains the ID
of the process that was selected as the
deadlock victim and killed by sequel
server so if you look at the log
information right here notice that the
first section that we have here is the
deadlock victim section and if you look
at the value it contains it is the ID of
the process that is made the deadlock
victim so what I'm going to do is take
that ID and look up that in the process
list section that we have here so the
next section in the log information is
the process list section which contains
the list of the processes that
participated in the deadlock so if you
look at this process list section notice
the first process starts right here and
it ends right there and the second
process starts right here now when we
take this process ID and compare that
with these processes that we have here
it matches with the first process ID
that means this is the process that's
made the deadlock victim so whatever
transaction this process is executing
that transaction is ruled back so the
transaction
I mean stood procedure SP transaction -
that is made as the deadlock victim and
if you look at this process node element
it has got a lot of useful information
that we can use to troubleshoot deadlock
so for example we have this login name
here which basically tells us the login
name that this process was executed you
saying and if you look at the login name
of the second process notice that it is
using the same login name that means
both the processes are executed by the
same user and we can also see the
isolation level the process was using
notice it is using reader committed if
you recollect from the previous video
session we have not explicitly said any
transaction isolation level when we were
executing these procedures right so
that's why sequel server was using the
default read committed isolation level
in addition to login name and isolation
level we also have the fully qualified
stored procedure name you know which was
executing when the deadlock occurred so
if you look at this proc name here
notice that we have the fully qualified
name of the stored procedure sample DB
door DB or dot SP transaction - and we
also have input buffer so basically that
contains the actual code that was
executing so in order to execute you
know that stored procedure this is the
code that we used execute SP transaction
- so that's the code that was executing
when the deadlock occurred now the next
section in the list that we have here is
the resource list section that starts
right here and this is very important so
what does this resource list section
contain it contains the list of the
resources that is the database objects
owned by the processes involved in the
deadlock so if you look at the resource
list this is the first resource that we
have an important thing to note here is
the object name so we have this resource
table a and that resource has got an
owner and a waiting process so
the owner process and the waiting
process owner process is the one which
has acquired lock on it so owner process
is this process with that ID so if I
take and look up that ID in the process
list that is our second process which
successfully executed okay so if you
look at this process it's actually
executing SP transaction one stored
procedure and if you look at SP
transaction one stored procedure notice
it is first updating table a that means
it has already acquired an exclusive
lock on that table we know that right so
the owner for this resource that is
table a is this process and notice the
mode node here so it says X that means
it has acquired an exclusive lock on
that resource and then the next thing
that we have is the waiter list which
contains the ID of the process that is
waiting to acquire a lock on that
resource in this case this is the
waiting process ID and if you look at
this process ID in the process list that
we have it is the first process which is
made the deadlock victim right and if
you look at this process it was actually
executing this transaction SP
transaction two and if you look at SP
transaction two it has acquired an
exclusive lock on table B but it is
waiting to acquire an exclusive lock on
table a because the other transaction
has actually acquired an exclusive lock
already on that so from this information
it is clear you know which processes
owning the resource what type of lock it
has acquired and which process is
waiting for that resource and what type
of lock it is requesting for so here it
is requesting for an exclusive lock and
that cannot happen until you know this
transaction completes right so that's
the first resource and we also have
another resource table B and if you look
at table be the
owner for that resource is this process
the process that's made the deadlock
with them and obviously this process is
executing which transaction second
transaction so second transaction is
updating table B first so that means it
already has an exclusive lock on that
resource so the mode C is an exclusive
lock and the waiting process ID is this
one that is the second process which
completed successfully so this is
waiting for an exclusive lock on table B
resource okay so by looking at this
resource list it's clear for us these
are the resources that are involved in
the deadlock and these are the
transactions that are being executed
when the deadlock occurred so when we
look at those transactions side by side
then you know we can understand the
problem so this is trying to update
table a first and then table B and this
one is trying to update table feet B
first and then table a so when these
transactions are being executed
simultaneously they are locking the
resources in such a way that no one can
move forward so how can we solve this
problem pretty straightforward we need
to ensure that the database objects that
is in our case table a and table B are
always accessed in the same order right
so let's go ahead and change the order
so I'm going to flip this
so now in both the cases the tables are
being accessed in the same order so
let's go ahead and alter this procedure
and now let's execute both the
transactions simultaneously and see if a
deadlock can occur so obviously it's
going to take at least seconds ten
seconds because we have deliberately
made the transaction sleep for 5 seconds
see notice now both the transactions
completed successfully thank you for
listening and have a great day