SQL query hint NOLOCK is similar as using READ UNCOMMITTED isolation level. People often under the impression that this query hint will make query run faster (as no blocking or lock allocation required). However, one needs to consider carefully the following big drawback with NOLOCK query hint and better step away from using it.
The drawbacks/issues with using NOLOCK query hint:
- Query may return the same row twice
- Query may skipping rows
- Query may see rows not actually commited
- Query may fail with error “could not continue scan with nolock due to data movment”
Unless inaccurate query results is not an issue, we should never use the NOLOCK hint. To prevent/limit locking/blocking issue, might consider using either READ COMMITTED SNAPSHOT or SNAPSHOT isolation level. Create appropriate index.
Brent Ozar has some examples you can reproduce the above issues:
NOLOCK Is Bad And You Probably Shouldn’t Use It
Using NOLOCK? Here’s how you’ll get the wrong query results
But NOLOCK is okay when my data isn’t changing, right?
But surely NOLOCK is okay if no one’s changing data, right?