Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to use NOLOCK in every single query.

Now, I work with a DBA who has banned NOLOCK under any circumstance - even when a report of mine (due to a considerable lack of indexes on a couple of tables) is stopping replication and system updates. In my opinion, in this case, a NOLOCK would be a good thing.

Since most of my SQL training has come various DBAs with very different opinions, I wanted to ask this to a wide variety of DBAs.

It isn't always bad.

Of course it allows you to read uncommitted values (that may be rolled back and hence never logically existed) as well as allowing phenomena such as reading values multiple times or not at all.

The only isolation levels that guarantee that you won't encounter any such anomalies are serializable/snapshot. Under repeatable read values can be missed if a row is moved (due to a key update) before the scan reaches this row, under read committed values can be read twice if a key update causes a previously read row to move forward.

These issues are more likely to arise under nolock however because, by default, at this isolation level it will use an allocation ordered scan when it estimates there is more than 64 pages to be read. As well as the category of issues that arise when rows move between pages due to index key updates these allocation ordered scans are also vulnerable to issues with page splits (where rows can be missed if the newly allocated page is earlier in the file than the point already scanned or read twice if an already scanned page is split to a later page in the file).

At least for simple (single table) queries it is possible to discourage the use of these scans and get a key ordered scan at nolock by simply adding an ORDER BY index_key to the query so that the Ordered property of the IndexScan is true.

But if your reporting application doesn't need absolutely precise figures and can tolerate the greater probability of such inconsistencies it might be acceptable.

But certainly you should not be chucking it on all queries in the hope that is a magic "turbo" button. As well as the greater probability of encountering anomalous results at that isolation level or no results at all ("Could not continue scan with NOLOCK due to data movement" error) there are even cases where the performance with nolock can be much worse.

StackExchange Database Administrators Q#10655, answer score: 34