Links

The Puzzle of U-Locks in Deadlock Graphs by Remus Rusanu

Transcript

Let’s go ahead and solve this thing.

I’m going to do a demo showing you and proving that we can resolve this deadlock but first…

I want to walk through just how I explore this deadlock graph and how I look for opportunities to solve the deadlock

When you’re looking at this deadlock graph, you can hover over the different circles and Microsoft will tell you, hey, here’s at least part of the query that is behind the deadlock.

The deadlock graph doesn’t always have the complete T-SQL of what was involved but we get usually at least a little bit of it and we can see that on the left here, the circle process is the SELECT query, who ended up being the victim, right?

They’ve got the X over on, they were killed off in the deadlock.

On the right, we have the circle for that transaction that was doing the updates and we have the T-SQL for the second update that was part of the deadlock.

One of the things you might notice when you’re looking at all these lines is that there’s these little letters that represent the lock request mode.

You may notice that this spells, if you range the letters in a certain way, S-U-X-X.

Well, one of these letters is particularly weird if you think about these queries.

When we’re looking at the SELECT query, that deadlock graph seems to be telling us that the SELECT query owns an UPDATE lock on one of the objects, that object at the bottom where it says, Owner Mode: U.

Why would our SELECT query have an UPDATE lock on the DimProductSubcategory table?

We’re in the read committed isolation level, we’re just doing a select.

One of the things to know about deadlock graphs is that the graphical display can lie about this UPDATE lock

Or maybe it’s not a lie. Maybe it’s just a mistake.

If you look at the XML behind that file, and you look at, okay, what are the lock modes in here?

You will not find and UPDATE lock for that SELECT query. Here I have highlighted the two different processes in different colors.

The SELECT query is highlighted in yellow. It has two shared locks, one of it, shared locks has been granted and one of it shared locks is waiting and then the query that’s doing the updates, it wants an exclusive lock and it has an exclusive lock. There isn’t an UPDATE lock being held by the shared lock.

So that is something that is just misrepresented by the graphical display of the deadlock in SQL Server Management Studio. There is a blog post on this (The Puzzle of U-Locks in Deadlock Graphs) by Remus Rusanu, who’s a member of the SQL Server engine team and he explains it is a puzzle when you’re looking at that graph.

Whenever you see something like that in a graph and you’re like, is it really? Looking in the XML can help confirm that in fact, whether or not it is an UPDATE lock or not.

So I’ve now made a little correction on our graph, I have drawn in and said, okay no, that’s actually a shared lock.

We might ask ourself: when I’m going in to solve this deadlock, one of the things I would ask myself is:

Who am I going to fix first?

If I have a deadlock like this where one of the processes in the deadlock is a SELECT query, I will look at it as my top candidate for, okay, I’m going to see if I can divert you because the query that’s doing an update, if I do things like adding indexes, yeah, I might be able to make the update, find the row easier but the UPDATE query is going to have to lock all of the indexes in which the column, it’s updating our present.

So I generally, if just for speed, I tend to say, okay, is there someone in here to evaluate who isn’t doing any modifications.

They may be the easiest one to cater to.

That means I’m going to look at the process on the left

It happens to be the victim in this case.

So what could I do for you?

I’ve just simplified the graph a little bit and said: okay, I’m only going to look at one of the objects. This is the one where it owns a shared lock on DimProductSubcategory. That’s part of the deadlock because this SELECT query owns it and then the second UPDATE statement says, oh I need this.

So what I want to know is, maybe there is a way where I can avoid taking out a shared lock on the primary key of this table.

If I look at the definition of the table, the primary key is actually the clustered index of the table.

Possibly, I can create a nonclustered index that could allow this to read just from a nonclustered index because it covers the query– and maybe, depending on what it needs from that table, maybe that is something that the UPDATE query won’t need to touch. It’s an avenue for me to explore.

That’s door number one.

What other options do I have?

All right, well sticking with our SELECT query who’s doing the reading, it is requesting a lock on DimProductCategory.

Is there a way that I could make it so it doesn’t need to have that lock on the primary key, the clustered primary key in this case of the table? Perhaps, if it needs to read from a nonclustered index, it could get a shared lock just on a nonclustered index and maybe that nonclustered index won’t have to be locked by the first UPDATE statement in the other process.

That is a second avenue to explore.

So what we’re going to do now, we’re going to dive into Management Studio and check out both of those avenues and test out whether they work.