Tip:
Highlight text to annotate it
X
In this demo, I'm going to show you how deadlocks are created. First of all, I'm going to set
up a small scenario that is going to create a deadlock. We'll look at the locks involved
and the hierarchy of locks. Finally, we'll look at a deadlock graph to get some more
insight into how to fix the problem in our applications. The first thing I'm going to
do is I'm going to turn on Profiler to capture a deadlock graph from the one we're going
to create. And I don't recommend using Profiler in a production environment, but for the purposes
of this demo, this is the easiest way to do it. I'm just going to adjust the width of
the columns a little bit here, because we'll need the TextData column in a second. Okay,
the next step is to setup the scenario. I've got a script here that creates two tables
and inserts a single row into each one. Now I have two different scripts here that do
update operations on each of the tables I created, and what I'm going to do is I'm going
to record the session ID because in the lock query I'm going to use, it's going to show
the session ID, it's not going to show us Connection A and B, so let me just write these
down so it's easier to follow. Okay, so in Connection A, what I'm going to do now is
I'm going to open a transaction, and then I'm going to update the single row in FirstTable.
And when I go over and look at the locks, we can see that on 57 -- that was the right
one, right? -- yep -- we can see we have a database shared lock, a table intent-exclusive
lock, a page intent-exclusive lock, and finally, an exclusive lock on our row. If we go back
and look at Connection B, we'll do the same sort of thing except on SecondTable. And noting
that our transaction in both connections is still open, which is how we're able to look
at these locks here. So on connection 58 -- sorry, session 58 -- it's the same sort of thing:
database shared lock, table intent-exclusive lock, page intent-exclusive lock, and row
exclusive lock. Now we haven't actually created a conflict yet; all we have are two transactions
that are open. In order to create the deadlock, we have to have each connection asking for
resources from the other connection, and we haven't done that yet. So let's go back to
Connection A and what we're going to do now is run a SELECT * FROM SecondTable, which
Connection B has currently updated and it's holding a transaction open. So when we execute
this, SQL Server is going to have to wait behind the exclusive lock on the table that's
being held by Connection B. So this is not a deadlock, this is called blocking, and blocking
is perfectly normal. In this case, we're waiting indefinitely because I've intentionally held
the transaction open just for the purpose of the demo. You can see how the general best-practice
of trying to keep your transactions as short as possible is a good one. So if the transaction
in Connection B was very short and it committed now, this would unblock and it would proceed.
But if we go and look at the locks, you can see that before I execute the SELECT statement,
all the locks are granted. When I run the query again, you can see that now we have
to wait to read the row that is exclusively locked by the other session. And you can see
this by looking at the RID for both of the locks. So still we haven't created a deadlock.
What we have to do is go back and create the same sort of scenario on Connection B. Connection
A has locked FirstTable, and it's asked for resources from SecondTable. Connection B has
locked resources on SecondTable, and now we're about to ask for resources on FirstTable.
This is what creates the deadlock. So the Deadlock Monitor, which is a process in SQL
Server, will pick it up, but not immediately. So what I'm going to do is after I run this
statement, I'm going to quickly flip back to the lock query and run it, and hopefully
we can see all of the locks as they are before the Deadlock Monitor picks up that there is
a problem. So, I'm sorry it's going to flip really quickly here. So I'll run this, flip
over to the locks query, run that, and we'll analyze what happens. Okay, unfortunately,
I was not quick enough, so that's kind of unfortunate, but we'll be able to see the
locks in the deadlock graph a little bit later. So as you can see, what happened was: the
transaction on Connection B was killed by the Deadlock Monitor. In other words, it was
chosen as the deadlock victim. And what happened on Connection A is that our SELECT statement
was unblocked and we got our result back. Now, you might notice that this returned the
result of 1, and that's because when Connection B was deadlocked, it was killed, and what happened was the transaction rolled
back. So it's as if this never happened, and we get the value 1 back. So we actually still
have an open transaction here, but that's okay, I'll just leave it. And we should have
captured a deadlock in Profiler here. Sorry it's a little small, but you can see that
this one here -- our SELECT * FROM FirstTable -- was killed by the blue X, and you can see
the other process here. The square boxes show us the resources that were requested by both
and how they were requested. You can see the request mode means this process requested
this resource to read it, because it's a shared lock, but this process already owned the lock
in exclusive mode. And the same thing here: SecondTable was requested, but it was exclusively
locked by this one, which I believe is process B. So in this case, it's nice in that it gives
us the actual table name; sometimes it won't, and you'll have to use this object ID to go
back to the metadata tables to figure out what actually was locked. So now what I'm
going to do is -- well, first, let me just say that the deadlock graph here is really
nice to be able to visualize what happens because sometimes it can happen with more
than just two tables. It could happen within the same table, it could involve more than
two processes. So I find this really helpful to be able to visualize what's going on. Now,
the deadlock graph itself is actually driven by XML -- as you can see in the TextData column
here. So what I'm going to do is extract the TextData column and just save this as an XML
file, with a special extension, on my desktop. Now, if I open that file directly in Management
Studio, it's just going to show me the same deadlock graph. What I'm interested in, though,
is the XML behind it, because it gives us more information. What I'm going to do is
select the file, but I'm going to open it with the XML editor. And you can see there's
a lot of information here that the deadlock graph consolidates for us. You can see things
like the resources, how long it's waited, all the locking modes, et cetera. One of the
most interesting things is found in the process list, and if I scroll to the right here, you
can see it gives us a whole lot of information to be able to track back to where it occurred,
and the most important thing, hopefully, is it captures the application name automatically,
which is great, because you may have the same query from different applications, and you
may find that there are certain combinations of applications that don't play well together.
You can also see who is doing things, and the isolation level. So I hope this has been
informative, and hopefully you understand more about deadlocks, and how they occur now.
Thanks for watching!