NOLOCK is bad and you probably shouldn’t use it, but every time I mention that publicly, the pushback just keeps coming. I don’t know why people so firmly believe that their situation couldn’t possibly be affected by bad/random data from NOLOCK.

Today’s misconception comes from a LinkedIn commenter telling me it’s safe to use if you’re doing index seeks. Hoo boy. Let’s whip up a table with an index:

The contents of the table are pretty straightforward: a bunch of rows with Odd & 1, Even & 0:

There are no rows that say Odd & 0, and no rows that say Even & 1. You can revisit the table population script above if you’re not sure.

Let’s start a transaction that flips the Even rows over to Odd:

While that query is actively running, changing rows, run this in another window. I’m purposely using an index hint here to make the demo easier for y’all to reproduce without worrying about how many rows will come back, what your compat level is set to, etc. I’m just guaranteeing it’ll get an index seek because the SQL Server and Azure SME said those were invulnerable:

That query SHOULDN’T return any rows. It should be impossible to see any rows where the label says Even, and the score says 1, and yet, there are thousands:

The reason NOLOCK gets bogus results here is due to the index seek itself, and the way it works. Here’s the query plan:

SQL Server works through this query plan from right to left, in order. The first thing it does is seek on the index on Label, making a list of “Even” rows that match. The second thing it does for each of those rows is the key lookup, because our query wants more columns than are present in the index. Under most isolation levels, the results are still accurate because SQL Server honors the locks being held by in-flight transactions.

With NOLOCK, not so much: SQL Server ignores the locks on in-flight transactions, reading rows as they’re being modified. So when it opens the index, it may find rows that say “Even” – but that have already been changed on the clustered index to be “Odd”, with a Score = 1! At any given time, you can have rows where the values on an index can be DIFFERENT from the values on the clustered index, and that’s fine, and that’s by design.

Shout out to Kendra Little, who first showed me a seek-plus-key-lookup demo like that years ago and I nearly fell off my chair with happiness at the elegance of that demo.

Right about now, someone out there is shaking their fist at the screen, yelling, “You could fix this problem by making the index covering!” In the real world, we can’t just make every index a covering index for every other query.

I say this same thing every time NOLOCK comes up: if you’re okay with incorrect query results, NOLOCK is completely fine. It sounds like I’m being sarcastic, but yes, there are scenarios out there where accuracy in the result set doesn’t really matter. Data warehouse reports for executives are the typical use case – after all, let’s be honest, your executives are going to make their own bad decisions no matter what the data says. (Some of you are laughing, and you shouldn’t be, because some of you have slathered NOLOCK all over your data warehouse queries.)

Otherwise, if you need any kind of accuracy in your data, NOLOCK is bad and you probably shouldn’t use it. Read the contents of that blog post again.