A developer put WITH (NOLOCK) on every table in our reports to make them faster. Is that safe?
NOLOCK is the same as READ UNCOMMITTED. A query with it does not take shared locks and ignores locks from other sessions. That brings problems.
• Dirty reads: you can see data that is not committed yet and that may be rolled back.
• Missing or duplicate rows: during page splits a scan can read the same row twice or skip rows.
• It can fail with errors about data movement.
It is acceptable for rough counts on a monitoring page where exact numbers do not matter. For reports that people trust, it is risky.
A better fix for blocking between readers and writers is READ_COMMITTED_SNAPSHOT isolation on the database. Readers then see the last committed version of the row and do not block writers. Test it in a copy first, since it uses tempdb.
Will test RCSI instead. Thanks for the clear list of risks.