CTEs, Views, and NOLOCK

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.

In the blue corner, weighing in at 100 rows, is a CTE.

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.

This is your brain on Read Committed

Lock of the draw

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.

Selecting data from the views behaves the same way.

This one finishes instantly!

This one hangs on for dear locks.

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!

, , ,
Previous Post
Confession: I recommended that a 5GB database get split up.
Next Post
Decrypting Insert Query Plans

12 Comments. Leave new

  • hi Erik, u can send me the database , is u demonstrated database stackoverflow, thanks very much!

    Reply
  • Don’t forgot that NOLOCK propagation kills query when you use CLR table UDF.

    Reply
  • 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.

    Reply
    • Erik Darling
      March 30, 2017 6:48 am

      Have you considered letting our lord and savior optimistic locking into your heart and database?

      Reply
    • LookingForANewJob
      March 30, 2017 10:45 am

      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.

      Reply
  • 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!

    Reply
  • A. Mauricio Repetto
    April 1, 2017 9:22 am

    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

    Reply
  • DirtyReader
    April 3, 2017 2:56 pm

    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?

    Reply
    • A. Mauricio Repetto
      April 3, 2017 3:11 pm

      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 😉

      Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}