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
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.
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
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
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!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on 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.