Safe to use WITH (NOLOCK) in this case?

I'm not a DBA, but a software dev with a few years of DB experience. I'm only "textbook-level" familiar with the inner workings of locking, pages, hints,

Then you should use SNAPSHOT isolation, or set your database to READ COMMITTED SNAPSHOT, because it's fundamentally simpler to write correct, scalable, deadlock-free code.

It's a common misconception that NOLOCK/ReadUncommited relaxes the concurrency model in a predictable way. IE that it simply allows you to read data in a state that might eventually be rolled back. This is not the case. Rows sometimes need to move around when they are changed, and a NOLOCK query might miss such rows or read them multiple times. Or a NOLOCK query might read a non-clustered index and the underlying table when one was updated but the other was not. Both of these can cause results that are just wrong.

The cost of READ COMMITTED SNAPSHOT/SNAPSHOT is that rows need a little extra bookkeeping. There's an additional 14 byte field added to updated and deleted rows to point to the previous row version, and the row versions are stored either in TempDb or in the user database. But there's a performance benefit too. Your workload can scale more easily, as there is less locking contention, and sessions are able to run more concurrently.


It is not possible for you to use the WITH (NO LOCK) query hint and be guaranteed to always have 100% accurate data. I've maintained queries in the past that do use it and very rarely have had an issue, but my personal choice (unless data accuracy is not a concern) is to avoid it. Please see Microsoft's David Browne's answer to my question for a conclusive reason why the data accuracy isn't guaranteed.

In short, the rows you're selecting against may be part of a changing data structure because of the other rows that are being DELETED also being part of that same data structure, e.g. B-Tree for a particular index both rows are a part of.

If you truly don't care about reading uncommitted data that could get rolled back (e.g. if an INSERT is in process and gets deadlocked) that uncommitted record would show, duplicate data from nonrepeatable reads, or missing records, then you don't care enough about the data accuracy that using the WITH (NOLOCK) hint should be a concern. But then it's usually recommended to change the server isolation level to something more lax like READ UNCOMMITTED isolation instead of using query hints.