Everybody knows NOLOCK can show you uncommitted data. Far fewer people know it can count the same row twice, or skip a row completely, with every transaction committed and nothing rolled back. That is all I want to explain today, and I want to explain it with two small animations.
This post is only about those two things. Not what to use instead, not snapshot isolation, not how to fix it. Just why the number comes back wrong.
One Fact You Need First
For some scans, SQL Server reads data pages in allocation order, roughly the order the pages are laid out in the database file, rather than following the rows by key. That can be faster when the query does not require ordered output.
Meanwhile, other people are still writing. Somebody updates a row and it gets longer. If its page has no room left for the bigger version, the page splits, and some of those rows move to a new page somewhere else in the file.
There is a second way a row moves, and it is just as ordinary. Update the column the index is sorted by, and the row cannot stay where it is. It has to go and sit where its new value belongs, which may be a long way from where the scan is working.
So the scan is walking across the pages in one direction, while rows are quietly moving between pages behind it and ahead of it. That is the whole setup. Everything else follows from it.
One. The Row That Gets Counted Twice
Picture twelve rows in four pages. The scan starts at page 1 and works right.
It reads page 1 and counts the three rows there. A moment later one of those rows moves to page 4, which the scan has not reached yet. When the scan arrives at page 4, there it is again.
If you prefer a still image, here is the same story in one picture.
The table held twelve rows before, during, and after. The answer comes back as thirteen.
Two. The Row That Is Never Counted
Same twelve rows, same four pages. This time the row moves the other way.
The scan has already read page 1 and moved on. Now a row from page 4 moves back to page 1, into ground the scan has already covered. The scan never goes back to look.
And again as a single picture.
That row was committed. It was valid. It was sitting right there the whole time. The answer comes back as eleven.
One simplification worth admitting. A split does not push rows into an existing page like the pictures show. It creates a brand new page, and that new page can be allocated anywhere in the file, including behind the scan. I have drawn it landing on page 1 because what matters is where it ends up, not how it got the address.
Why This Is Worth Knowing
Notice what did not happen in either picture. Nobody inserted a row. Nobody deleted one. Nothing rolled back. The table held exactly twelve rows from start to finish, and both answers were still wrong.
Something did happen, of course, or the row would not have moved. Somebody updated a row. It grew and its page ran out of space, or the value it is sorted by changed and it had to move. Those are ordinary events in an active database. They do not affect every table or every scan, but when they overlap the kind of scan described above, they are enough to make the count wrong.
This is why the usual defense of NOLOCK is incomplete. People say they are fine with a slightly stale number. Stale or uncommitted data is only part of the risk. They can also get a number that was never true at any moment, and one that can land on either side of the truth. Too big is just as possible as too small.
It also explains why nobody catches it. Almost always there is no error and nothing in the log. You get a number that looks perfectly reasonable, on a report that has worked for years, and the only sign that anything went wrong is that it does not quite reconcile.
Those are the two things. I will come back another day to what to do about it.
NOLOCK is not a safe performance fix, it is a decision to reduce blocking by accepting an answer that may never have been true. The whole problem is that nobody who types it thinks they are making that trade.
Reference: Pinal Dave (https://blog.sqlauthority.com/), NOLOCK and Read Uncommitted, X
