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.
|
1 2 3 4 5 6 7 8 |
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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 |
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!
|
1 2 |
SELECT * FROM dbo.Top100Nonsense AS tn WITH (NOLOCK) |
This one hangs on for dear locks.
|
1 2 |
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!
12 Comments. Leave new
hi Erik, u can send me the database , is u demonstrated database stackoverflow, thanks very much!
It’s a bit large for email at 110 GB, but you can download it here: https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/
Don’t forgot that NOLOCK propagation kills query when you use CLR table UDF.
I don’t use CLR much. Good to know.
Sensitive subject for me. One of the systems I am forced to work on is fraught with NOLOCK, Redgate SQLSearch reported 482 results (views and stored procs with NOLOCK). One of these can have 6 or more NOLOCK statements depending on joins. That is not including the inline code statements of the application. Unfortunately, the global mission critical system grew over 10 years to be the monster it is now… and no redesign in sight.
Have you considered letting our lord and savior optimistic locking into your heart and database?
Hold my beer…
The company I work for started using it long before I ever joined and it’s out of control, I searched just one database, not even the entire server, and SQLSearch found 5,263 procs/views/functions with at least one NOLOCK. They didn’t even have the decency to add the “WITH” in most cases.
Lol and I thought ours was bad…
I am in the same boat that LookingForAJob is at (I have not counted them in fear or the result)…in so that our SQL Code Review SOP has it mandated that NOLOCK is used on EVERY query…NO exceptions. I have since changed that SOP…but…how can one convert that mind set. I tell them to stop drinking the kool-ade but I sometimes think it may be too late. Aaarrrrgggghhhhh!
I’m working on a project that has an intensive use of upserts, and also searches on the data. So our solution to avoid pages without response and angry users because of the locks was to read uncommitted records. The business is aware of it and they does not care about it, they prefer an app that quickly finds what they are consulting no matter if the data is going to change on the next second. But I hate nolock hints, they look awful! And makes code uglier. For those who have seen it or they use it on a daily basis I strongthly recomend to set transaction isolation level to read uncommitted. That will do all the work, also I consider that it is easier to maintain code with it because if you want some day to remove dirty reads it will be enough with removing that single line. So I invite you all to use this approach 🙂 https://msdn.microsoft.com/es-es/library/ms173763.aspx
Oh my … I get why this may “seem” like a good idea to make users happy….but why do I feel a sense of sheer panic when I read this suggestion to change to read uncommitted? Tell me that it isn’t akin to shooting oneself in both feet with a shotgun?
It’s out of discussion that dirty reads are evil.. You know.. There will be always better ways to do things wrong 🙂 I think this is the best of the worst, but it’s all up to you to choose 😉