“But NOLOCK Is Okay When The Data Isn’t Changing, Right?”

I’ve already covered how NOLOCK gives you random results when you’re querying data that’s changing, and that’s a really powerful demo to show folks who think NOLOCK is safe to use in production. However, I’ve gotten a question from several users:

But I’m querying data that isn’t changing – sure, OTHER rows in the table are changing, but not the rows I’m querying. Isn’t NOLOCK okay if I’m querying stable rows?

Nope, not even close. Get any Stack Overflow database, and start with the query from the last post – except this time we’ll modify it so that we’re updating some of the users, and querying others.

One of the more common DisplayNames is Alex. In one window, I’ll get a count of the number of Alexes:

And in the other window, I’m going to set everyone’s location and website – EXCEPT the Alexes, who I’m not going to touch:

Watch disaster unfurl:

The number of users named Alex appears to keep changing – EVEN THOUGH I’M NOT UPDATING THOSE ROWS! The reason: the location of Alex’s rows may be moved around by things that are happening to other rows.

I can’t emphasize this enough: with NOLOCK, you can:

  • See rows twice
  • Skip rows altogether
  • See data that was never committed
  • And have your query fail with an error

If you’re cool with that, great – NOLOCK is for you. If not, it’s time to consider other ways to get the performance you want while still getting the data accuracy that your users require.

Previous Post
What The Consultant Toolkit Does
Next Post
DBA Training Plan 8: What’s Inside Your Own Databases

53 Comments. Leave new

  • this looks like I should never use nolock.. ever again..

    Reply
  • Michael L John
    August 5, 2019 9:14 am

    With the possible exception of loading a bunch of lookup tables into a set of drop downs, there is no reason to use nolock. Ever. Period.

    Reply
  • > loading a bunch of lookup tables into a set of drop downs

    How much more performance do you expect to get w/ NOLOCK here?

    Reply
    • I would be astounded if there would be any measurable difference at all.

      It really should only be used on flat or nearly flat, static data, that doesn’t update itself – like constructed reporting tables that are built through an ETL process and are only updated every few hours.

      Reply
    • Michael L John
      August 5, 2019 9:34 am

      Likely very little.

      Reply
      • Troy Witthoeft
        August 5, 2019 9:53 am

        I’ve seen my fair share of NOLOCK, and I’ve fought quite a few battles trying to sway developers away from it. Telling devs “with nolock is bad” can meet resistance. Instead, I normally start with recommending they use STILRU. Set transaction isolation level read uncommitted. I pitch it by saying, it’s like writing with no lock everywhere and you only have to state it once. Of course, it’s not truly the same, but it normally nets the same *cough* benefits and it get the devs thinking about isolation levels. Later on, after we break the with nolock habit, we can have an earnest conversation about transaction isolation and perhaps discuss other transaction levels that are less likely to read dirty data and block less then the default transaction level. It’s a nice toe in the water that doesn’t completely remove their “with nolock” power.

        Reply
  • This drives me insane to absolutely no end. When asked about using a nolock hint, I used to answer that it is ok if none of the tables are being modified actively – but then would get used all over the place. Recently I have been telling analysts that its only ok if they know all the data in a database is static, and if they are at all uncertain, with even the most infinitesimal amount of doubt, that they need to use read committed. But use of the nolock hint still seems to replicate like extremely horny cats.

    I have a third party application that uses a bunch of nested views that someone took it upon themselves to modify all the views to use nolock hints, in an EXTREMELY active OLTP application. Sometimes our ETL job that pulls data out of it for regular use will have to query it 10+ times to get clean data. When I tried to undo it, a bunch of reports started breaking due to horrendous queries that relied on the nolock to even run. All the while our SQL developer has more than 2 years of work on his plate with critical organizational compliance issues and the organization almost died when they saw the quote for 2500 hours of consulting work the fix all the reports. :-@

    I really wish MS would provide a way to make SQL ignore nolock hints the way Postgres does, or add an option to make the query optimizer instead rcsi as someone on the 2019 thread suggested.

    Reply
    • Martin Smith
      August 5, 2019 9:36 am

      If the tables aren’t being modified at all I doubt there would be much if any benefit. By definition it isn’t going to encounter blocking from modifications. Sql Server can already skip taking S locks at read committed in this type of case. https://www.sql.kiwi/2010/11/read-committed-shared-locks-and-rollbacks.html. Only other benefit I can think of would be that it allows allocation ordered scan to be considered.

      Reply
      • It skips taking out the shared lock though, which takes a small amount of time. We had a report which would get run about 40 thousand times per day, 90+% of which was in a period of about 6-7 hours. It did have a noticeable (but small) difference before the analysts finally enabled caching after discovering the ‘live’ data behind it wasn’t actually live and fewer than .1% of the executions were actually querying new data.

        Reply
        • Martin Smith
          August 5, 2019 1:01 pm

          “It skips taking out the shared lock though, which takes a small amount of time”. Did you look at the link? SQL server doesn’t need NOLOCK to do that in the case the object isn’t being modified

          Reply
          • I totally missed that section! That is extremely interesting. The rollback section makes more sense to me now as it seemed weird he was emphasizing something that at the time was obvious.

            I’m curious why that hint did anything, and even more curious whatever else the analysts may have done without a change request.

            Thank you for sharing this….essentially the nolock hint is entirely worthless unless you are in a condition where you are going to get some bad data back and you don’t care.

    • Wilfred van dijk
      August 13, 2019 12:00 am

      traceflag 8755 disables ALL locking hints

      Reply
  • Martin Smith
    August 5, 2019 9:20 am

    To be fair this type of anomaly can also happen at read committed isolation level (locking style rather than RCSI) as well as at read uncommitted level. It is not unique to NOLOCK.

    At pessimistic read committed level locks are taken and released as and when the rows are read so there is opportunity for concurrent activity to move them around during the scan.

    Reply
    • Yeah, but it is much more difficult for it to happen. I only have one app that is active enough to cause data quality problems for our purposes, but using a higher isolation level becomes disruptive to the app when both the app and reports are busy. In that case, data is just getting entered as it comes in, and is known to be incomplete or inaccurate initially until more discovery is done and its really only important for the data to be very clean after the data has left it’s active state and stabilizes. Technically, its data can change for as long as 2 or 3 years after it was initially entered, but virtually all of the change happens within the first 3-5 days and its almost unheard of for anything to change after about a month.

      There are 2-3 other apps we have where I am sure it happens as well, but in those each record has a record status column, and when the record is active, the data in the record itself is known to be of suspect quality until the status changes to posted or locked or whatever, indicating that it wont be changed any further.

      I would think if you needed queries to be that clean at a level of data activity that would cause dirty reads at any level of frequency, you would need to offload those queries onto a replica where it can better isolate transactions without hurting the app.

      Reply
  • I will follow this discussion with great interest. I have used nolock after discussing with stakeholders both the risks and the benefits. In a number of years running dozens of SPs that use nolock against an accounting app experiencing frequent updates througout the workday, we never did see any evidence (complaints from users, inconsistent reports, etc.) of read anomalies. We all know they can happen, but we never did see them live. Perhaps this is because the updates were always just a very few rows out of 30 million rows, representing one transaction? Not “bulk” updates at any time during business day as per Brent’s test? I wonder what the likelihood of read anomalies really is with very tiny row updates relative to table row counts.

    Reply
    • You could probably calculate the odds, but however small the odds are, it would only take one bad read to have a vendor get paid a 6 figure bill twice (or more), or have payroll look cheaper than it does because some of the records were skipped, to have an employee get paid on a million dollar salary when someone accidentally put the trailing zeroes to the left of the decimal, or for your book keeper to not pay all the bills, or pay them incorrectly from a report that was wrong.

      Reply
      • Of course, we throught of that. We were careful to be sure those queries were not responsible for billings. They are “overview” queries designed to give an overall estimate of office “status” comparitive to other offices. Not transactional and not used for critical business decisions.

        Reply
    • Brian Beuning
      August 5, 2019 1:50 pm

      Our app is like yours. OLTP and query single rows and update single rows.
      When I showed the devs Brent’s last demo, they said “but we don’t do mass updates like that”.
      I need to make a test that updates row with key=N and query row with key=N+1 and show that the update can mess up the query. Need some way to keep the two SPID in sync on N…

      Reply
  • I have heard a few times over the years to use NOLOCK when querying transactional data; essentially that it provides optimal means to avoid “interfering” with an ongoing simultaneous Transaction. Any sound reasoning to this?

    Reply
    • If you don’t want to interfere with the write transaction, yes, but that is the absolute best way to get dirty data, or just have a query completely fail. If the quality of the data doesn’t matter that much for whatever purpose you are using the nolock, that can be fine, but I’d be extremely suspicious of any thoughts that it would be ok – the way you think the data is used can change over time, or even just have user expectations change, when they ‘forget’ the data is supposed to be regarded as dirty.

      Reply
  • The only place I really like NOLOCK is when I’ve got a long-running process inside of a transaction and I want to keep tabs on its progress. (How many rows inserted or updated? How many invoices generated?) Of course,
    exact results are not critical and I’m the only one affected, so that makes it OK.

    Reply
  • Shaun Austin
    August 5, 2019 1:41 pm

    Really interesting example, thanks. READ UNCOMMITTED was introduced by my old boss as part of our coding standards on the basis that it gave a performance boost, and given the nature of the nature of the system we were supporting, there was little risk.

    From that point I always considered the risk with NOLOCK to be limited to how likely it is for a transaction to be rolled back (and therefore you read dirty data). If that risk is very small, then the (perceived) performance benefit was worth it. However I hadn’t considered that you could actually see the same row twice!

    I’ve recently started testing with Read Committed Snaphot (RCSI) as an alternate way of preventing update locks taken by replication from slowing down my reads in a reporting environment. RCSI comes with it’s own considerations of course (namely tempdb growth, potential long version chains slowing down SELECTs), however for most systems this is the more sensible way of tacking the problem, providing you test of course!

    Reply
  • also, non-matching rows may be returned (when you shuffle those rows, how do you know ‘Alex’ is still in that slot?)

    Reply
  • NOLOCK hints are fine in a world of data warehousing for reporting tables that are reloaded nightly. I’ve seen significant performance gains in a well-used data warehouse from using them. However, totally agree with Brent that I’d never use them in the application (OLTP) world for all the good reasons he spells out.

    Reply
    • DW – if you’re referring to reports that run during the day when data isn’t changing, why would NOLOCK help you at all?

      If you’re referring to environments where querying happens at the same time you’re loading data, might wanna reread the post a little more carefully.

      Reply
      • I believe he’s referring to a DW refresh that happens off-hours, when there are no updates happening.

        Reply
      • Please don’t patronise me, I read the post fine. This article, while old, demonstrates the sort of gains that I was talking about: https://www.sqlservercentral.com/articles/the-effect-of-nolock-on-performance. While it may no longer be true in more recent optimisers and with better servers, the advent of columnstore and so forth, it does demonstrate that it was considered worth doing. If you have a link to any evidence that it is no longer worth doing then I’ll gladly read it. Thanks.

        Reply
        • Your best bet there is to note the author’s repeated cautions at the top of his very own post telling you not to do it for performance reasons.

          Reply
        • DW Dev Dude – I only learned this from a comment in this post as well, but if the data isn’t changing, SQL doesn’t take out a lock at all and nolock doesn’t do anything. I have had analysts work on reports (that use static data) that ran better after they claimed they only added nolock hints, but these may have been from them changing other stuff in the report. I’ve had them go in and tinker with stuff quietly before.

          Until this post I had always believed that SQL always took out an S lock at the standard isolation level, but it seems at least in 2008 and up, that is no longer the case. Since your article test was done using 2005, it is possible that S locks were still taken on reads at that point, when the data isn’t changing, but no longer.

          Now, the only real performance gain you can get by using nolock is when the data is changing and you are probably going to get back dirty data.

          Wondering if setting the database compat level to 2005 would allow reproduction of what was talked about in his post…I may play with that if I have a sample database I can set that low somewhere

          Reply
          • Locking isn’t the only difference. With NOLOCK an allocation ordered scan can be used which may have better performance in some situations. https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/11/08/when-can-allocation-order-scans-be-used/.

            This may be a factor in the linked SSC example, at some point I’ll probably have a look at it to see if this is still reproducible, and if so what exactly is going on there. But not this week as am on vacation.

          • But if the data isn’t changing, why would it matter? the index is already going to be ordered assuming it is a copy that is constructed for reporting. If its the live copy, you’re just hoping that no one is writing to the data outside of normal hours or that the report or whatever only ever gets run during the off hours

          • Actually I see that you were the one that shared the link about skipping the locks before. Would be curious what you thought – I would think that if the index is already in order that even if it does an unordered scan against an index that is already in order, it wouldn’t be jumping around and effectively do an ordered scan from disk

  • I wonder if the same error would occur if sum(1) or count(displayname) was used instead of count(*)

    Reply
  • > And have your query fail with an error

    That is kind of scary. In what situations query with nolock may fail with error?

    Reply
    • I’ll leave that one for another post, but the error is, “could not continue scan with nolock due to data movement.”

      Reply
      • Funny, I have never seen the “could not continue scan with nolock due to data movement” error, and I have debugged & modified hundreds of SQL stored procedures in my production environment that use it. Maybe I don’t know where to look for the error. Oh, and “Funny” meaning scary, not “ha ha.”

        Reply
  • Johannes VInk
    August 7, 2019 10:17 pm

    That is creepy Brent… I read the blog and suddenly a data warehouse extract from a busy OLTP system started to fail… NOLOCK duplicate key galore. We’re looking into rcsi, but that also comes with some drawbacks (step 1, is that even enabled on the source?).

    Power BI direct query by the way… does not support ‘set transactional level x’ statements (nor CTE’s, like wut?). I have not figured out yet how to handle that… Should look into the connection string, but seeming how Power BI supports direct query I guess there are no options other than NOLOCK.

    Reply
  • Alex Friedman
    August 11, 2019 3:01 am

    Yeah, if you’re selecting me, you better lock well! 😀

    Reply
  • We have a vendor that uses NOLOCK everywhere – every single query in every procedure. Reading their code makes my stomach hurt. In your post, you mention the location of rows changing. Is the risk of NOLOCK reduced (or maybe eliminated) if the schema is consistently clustering on identity primary keys? Would this keep rows from moving?

    Reply
    • Tom – you mean like the Users table that I use in the demo above?

      Reply
      • There are tons of tables. Each has a clustered PK on an identity column. Every query to every table uses NOLOCK. Is that what you meant?

        Reply
        • No, I meant that the very table I use in this post is clustered on an identity. You may want to download the database and run the demo yourself before following up with more questions. That’s why I work so hard to give y’all the exact database and demo scripts. Hope that helps!

          Reply
          • Oh, thanks! I should have looked more carefully at your post. Overlooking things is what happens when we have too much to do in too little time 😉 That’s my story anyway!

  • I have a baaaaaad feeling that if I modified every stored procedure to take out the NOLOCK in every SELECT statement, the company where I’m employed would experience locking issues that would cripple productivity and make the users hate our IT group even more than they do now. Ugh.

    Reply
  • Very aggressive, radicalized statement. One can turn everything into an absurd taking it out of context as with this post. Actual example is good one, but hardly a justification of “never ever do it”.

    Reply
  • Weathered Backspace
    October 22, 2019 1:39 pm

    Thanks for this. You hit the nail on the head of what my assumption has been for 15 years – that as long as the rows that I was pulling back in my query weren’t changing I was okay….. which is not the case at all.

    You’re doing consulting work for our company right now and my boss made mention of this along with your other advice about functions crippling the optimizer and creating a thread bottleneck.

    Thanks for the help, reading through a bunch of your stuff now to see what else I think I have a handle on but actually don’t!

    Reply
  • I said it once in a meeting to don’t use NoLock and the Solution Archetict looked at me i was scared for life. It’s used every where even in SPs. Even the DBA handed over the DBs to me asked me strickly to never run anything without a NoLOCK and to be included in all code. But I listen to Brent Ozar 🙂

    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":""}