Locking Hints Make Troubleshooting Blocking And Deadlocks Confusing In SQL Server

Posted on by Erik Darling

King Of The DMV

Many people will go their entire lives without using or seeing a lock hint other than NOLOCK.

Thankfully, NOLOCK only ever leads to weird errors and incorrect results. You’ll probably never have to deal with the stuff I’m about to talk about here.

But that’s okay, you’re probably busy with the weird errors and incorrect results.

Fill The Void

It doesn’t matter who you are, or which Who you use, they all look at the same stuff.

If I run a query with a locking hint to use the serializable isolation level, it won’t be reflected anywhere.

SELECT 
    u.*
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;
GO 100

Both WhoIsActive and BlitzWho will show the query as using Read Commited.

EXEC sp_WhoIsActive 
    @get_task_info = 2,
    @get_additional_info = 1;

EXEC sp_BlitzWho 
    @ExpertMode = 1;

This isn’t to say that either of the tools is broken, or wrong necessarily. They just use the information available to them.

sp_WhoIsActive Locks
ah well

Higher Ground

If you set the isolation level at a higher level, they both pick things up correctly.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT 
    u.*
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;
GO 100
sp_WhoIsActive Locks
gratz

Deadlocks, Too

If we set up a deadlock situation — and look, I know, these would deadlock anyway, that’s not the point — we’ll see the same isolation level incorrectness in the deadlock XML.

BEGIN TRAN

UPDATE u
    SET u.Age = 1
FROM dbo.Users AS u WITH(HOLDLOCK)
WHERE u.Reputation = 2;

UPDATE b
    SET b.Name = N'Totally Tot'
FROM dbo.Badges AS b WITH(HOLDLOCK)
WHERE b.Date >= '20140101'

ROLLBACK

Running sp_BlitzLock:

EXEC sp_BlitzLock;
sp_BlitzLock
grousin’

 

Again, it’s not like the tool is wrong. It’s just parsing out information from the deadlock XML. The deadlock XML isn’t technically wrong either. The isolation level for the transaction is read committed, but the query is asking for more.

The problem is obvious when the query hints are right in front of you, but sometimes people will bury hints down in things like views or functions, and it makes life a little bit more interesting.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

4 thoughts on “Locking Hints Make Troubleshooting Blocking And Deadlocks Confusing In SQL Server”

  1. Is there ever a time and place (example) where you would (have) use(d) the WITH(HOLDLOCK) hint? What seems to be a consensus, with many hints, you shouldn’t use them (or if you don’t know all the details of what they do) and typically the problem is upstream or downstream (whichever direction you are facing) and these hints are used for workarounds and quick and dirty fixes before the root of the problem is resolved.

    1. I’ve found a use for nearly every hint in different scenarios. For HOLDLOCK specifically, consider that foreign keys with cascading actions use serializable locks under the covers to maintain referential integrity, and that we have no control over how SQL Server tries to implement that in the query plan. I’ve had to pull those out and write other code to do the same thing, but where join order and type were controllable, etc.

      Thanks!

  2. Speaking of sp_BlitzLock.. Rumor has it that you know a thing or two about that proc, Erik. Perhaps I could coax you into offering a suggestion or three on how to speed it up when it’s currently taking almost a half hour to complete. The (prod) server in question is of high quality (and quantity) CPU and memory and supports a dozen or so busy DBs that are all pretty big, old and inefficient. Still, it’s a Darling proc, from which we have all come to expect warp speed. Are there any parameter settings that might be tried which might grease the wheels? Or things on the server that I (accidental DBA, still working on my game) can check and (hopefully) tweak which would explain the slowness? Or can it simply just be this way sometimes, perfectly normally?

    Thanks for any ideas.

    1. If it’s the first-ish query that put the deadlock XML into a temp table, then no. That part has always sucked. If it’s the last-ish query that generates results, I’ve run into a few situations lately where unquoting the index creation (ctrl + f for “Break in case of emergency”), and adding a MAXDOP 1 hint to the query that does the INTO #deadlock_results is helpful. There have been some pesky exchange spills.

Comments are closed.