This is a post because it surprised me
It might also save you some time, if you’re the kind of person who uses NOLOCK everywhere. If you are, you’re welcome. If you’re not, thank you. Funny how that works!
I was looking at some code recently, and saw a CTE. No big deal. The syntax in the CTE had no locking hints, but the select from the CTE had a NOLOCK hint. Interesting. Does that work?!
Oddly, it does. It also appears to be true for views.
Let’s construct a silly test!
In the red corner, weighing in at 5,899 rows, we have an uncommitted update.
BEGIN TRAN; UPDATE u SET u.Reputation += 100 FROM dbo.Users AS u WHERE u.Id BETWEEN 1 AND 10000; ROLLBACK;
In the blue corner, weighing in at 100 rows, is a CTE.
WITH c AS ( SELECT TOP 100 u.DisplayName FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id) SELECT * FROM c WITH ( NOLOCK );
If we run that query as-is, it finishes about as instantly as you’d expect. If we run it without the NOLOCK hint, it gets blocked as you’d expect. Running sp_WhoIsActive with @get_locks = 1, we can see the locks taken out by the update.
In this case, you can see the PK/CX is locked, along with a couple nonclustered indexes I had for another demo. They had Reputation in the definition, so they got locked by the update modifying Reputation.
As the title suggests, this also applies to views
Here’s the CTE as a simple view. Again, no hints in the view itself.
CREATE VIEW dbo.Top100Nonsense AS SELECT TOP 100 u.DisplayName FROM dbo.Users AS u JOIN dbo.Posts AS p ON p.OwnerUserId = u.Id; GO
Selecting data from the views behaves the same way.
This one finishes instantly!
SELECT * FROM dbo.Top100Nonsense AS tn WITH (NOLOCK)
This one hangs on for dear locks.
SELECT * FROM dbo.Top100Nonsense AS tn
Are there any differences?
The only difference is which objects the select queries are waiting for locks on.
The select from the view has an additional granted request on the view name as well.
The CTE doesn’t care about the view, obviously.
Now with less nolock
So, for all you NOLOCKers out there, you can now save yourselves oodles of time by only using the hint in outer references to your CTEs and Views.
Congratulations, I suppose.
(Please stop using NOLOCK.)
Thanks for reading!