Locking Hints Make Everything Confusing

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.

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.

ah well

Higher Ground

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


WHERE u.Reputation = 2;
GO 100

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.


    SET u.Age = 1
WHERE u.Reputation = 2;

    SET b.Name = N'Totally Tot'
WHERE b.Date >= '20140101'


Running sp_BlitzLock:

EXEC sp_BlitzLock;


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!

A Word From Our Sponsors

First, a huge thank you to everyone who has bought my training so far. You all are incredible, and I owe all of you a drink.

Your support means a lot to me, and allows me to do nice stuff for other people, like give training away for free.

So far, I’ve donated $45k (!!!) worth of training to folks in need, no questions asked.

Next year, I’d like to keep doing the same thing. I’d also like to produce a whole lot more training to add value to the money you spend. In order to do that, I need to take time off from consulting, which isn’t easy to do. I’m not crying poor, but saying no to work for chunks of time isn’t easy for a one-person party.

I’m hoping that I can make enough in training bucks to make that possible.

Because this sale is extra sale-y, I’ve decided to name it after the blackest black known to man.

From today until December 31st, you can get all 25 hours of my recorded training content for just $100.00. If you click the link below to add everything to your cart, and use the discount code AllFor100 to apply a discount to your cart.




Some fine print: It only works if you add EVERYTHING. It’s a fixed amount discount code that you need to spend a certain amount to have kick in.

Thank for reading, and for your support.

4 thoughts on “Locking Hints Make Everything Confusing

  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.


  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.

Leave a Reply

Your email address will not be published. Required fields are marked *