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

this is part 78 of sequel server
tutorial in this video we'll discuss
deadlocks in sequel server so when can a
deadlock occur in a database a deadlock
occurs when two or more processes have a
resource locked and each process
requests a lock on the resource that
another process has already locked
neither of the transactions here can
move forward as each one is waiting for
the other to release the law the diagram
here explains the scenario we've got two
resources here table a and table B and
two processes process a and process B
both these processes want to update both
these resources that is table a and
table B so process a starts first and it
starts updating table a since no other
process has already acquired an
exclusive lock on table a process a is
allowed to acquire an exclusive lock on
that table and then process B starts and
it wants to update table B first since
no other process has already acquired a
lock on it process B is allowed to
acquire an exclusive lock on it at this
point
process a is done updating table a and
now it wants to update table B both the
update statements are part of the same
transaction now process a is blocked
from acquiring a lock on table
be-because process B has already locked
it finally process B is done updating
table B and now it wants to update table
a but then process B is not allowed to
acquire a lock on table a because
process a has already locked that
resource so in this case process B is
waiting on process a to release the lock
on table a similarly process a is
waiting on process B to release the lock
on resource table B so here we have a
deadlock situation when deadlocks like
this occur sequel server will choose one
of the processes as the deadlock victim
and roll back that process so the other
process can move forward let's look at
this in action i've created these two
tables table a and table B and both the
tables at the moment have caught exactly
one row within each of them and here's
the sequel script to create those tables
and populate them with data
I'll have the script available on my
blog in case you need it I also have two
transactions here notice transaction one
wants to update both the tables table a
in table B transaction one is updating
table a first and then table B whereas
if you look at transaction two it's
updating table B first and then table a
so let's execute part of our transaction
so transaction one started updating
table a so transaction one has acquired
an exclusive lock on table a now let's
go ahead and execute part of our
transaction - so transaction two started
updating table B okay so at the moment
transaction one has an exclusive log on
table a and transaction B has an
exclusive lock on table B now let's
execute the second update statement in
transaction one now this will be blocked
because process B that is our
transaction to has already blocked you
know locked table B so look at that
transaction two is blocked from
acquiring exclusive lock on that table
now when we do I mean when we execute
the second update statement that's part
of our transaction - since transaction
one has already locked that resource you
know this statement execution will be
blocked look at that both the you know
transactions are blocked now and after a
few seconds notice that transaction -
you know is chosen as the deadlock
victim and it terminates with an error
so if you look at the error message
let's actually copy this to a notepad
transaction you know with that process
ID 56 was deadlocked on lock resources
with another process and has been chosen
as the deadlock victim rerun the
transaction so if you want to complete
the world
that transaction - is doing you will
have to rerun the transaction and if you
look at the data that we have in the
table it should have been the updates
that transaction 1 has issued now the
select statements here are blocked
because we have not committed our
transaction so let's go ahead and commit
our transaction 1 and now if we look at
the data look at that
whatever updates transaction 1 performed
you know that's the data that we see in
both the tables and here are the
examples that we just discussed in our
next video we'll discuss the criteria
that sequel server uses to choose a
deadlock victim thank you for listening
and have a great day