-1

Link: https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/

The article mentions some problems with using NOLOCK:

  • You can see rows twice
  • You can skip rows altogether
  • You can see data that was never committed
  • Your query can outright fail with an error, “could not continue scan with nolock due to data movement”

Then a fix is mentioned as:

Create an index on the table (any single-field index would have worked fine in this particular example, giving SQL Server a narrower copy of the table to scan)

How does creating an index help with NOLOCK problems?

1 Answer 1

9

Just creating any non-clustered index on a table doesn't magically make all NOLOCK issues go away.

In this particular case, for this particular query, an index on any column except the WebsiteUrl column will help, though.

Why?

Think about how SQL Server can do COUNT(*). "How many rows are there?". You can answer that reading any index leaf level (except a filtered index) and just count the number of rows. I.e., get the first page in the linked list, count number of rows on each page, page by page.

Say you have a non-clustered index on the Age column, for instance. SQL Server finds the first page in the linked list, follows the linked list to count numer of rows in the table. This index is not affected by the UPDATE statement, since the UPDATE statement doesn't modify the Age column.

But, what is missing from Brent's comment is that an index on the WebsiteUrl will not help. That is because we are modifying that column in the UDPATE, so again we are victims of data movements during our SELECT statement. To demonstrate that, I created below index:

CREATE INDEX x ON dbo.Users(WebsiteUrl)

And ran that UPDATE and SELECT, and here are (some of the rows from) the result:

enter image description here

2
  • So an index on any column, other than the one that is being updated solves the problems with NOLOCK? Can you please explain why? Is it only applicable to non clustered index or also for a clustered index?
    – variable
    Mar 1 at 9:08
  • 5
    Please re-read my reply, the answer is in there: That index isn't modified by the UPDATE, no data in that index is being modified, hence the index is stable. This only applied to non-clustered index. And, as I mentioned, this is not a universal "fix", it just happen to "fix" this particular situation (COUNT(*)). Mar 1 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.