Admittedly, I use NOLOCK all the time in my queries. But in my defense, most of the queries that I write ad-hoc are returning information that I’m not that concerned about. Using the NOLOCK table hint in SQL Server can have significant implications, both positive and negative, depending on the use case. While it is commonly used to improve performance by avoiding locks on a table, it has several risks and potential downsides that make it unsuitable in many scenarios. Here’s an overview of why you shouldn’t use NOLOCK indiscriminately:
1. Dirty Reads (Uncommitted Data)
- Risk: The
NOLOCKhint allows the query to read data that is not yet committed by other transactions. This means you may read data that could be rolled back or invalid, leading to inaccurate or inconsistent results. - Example: If a transaction updates a table and then rolls back, your
NOLOCKquery might still read the uncommitted (and ultimately invalid) changes.
2. Non-Repeatable Reads and Phantom Reads
- Risk: Data can change while the query is executing. For example, rows might be updated or deleted after your query has read them, or new rows might be added during your query, leading to inconsistent results.
- Impact: If consistency is critical, these issues can cause unexpected behavior, especially in reports or calculations.
3. Incorrect Query Results (Missing or Duplicate Rows)
- Risk: SQL Server may read pages that are in the process of being updated or moved. This can result in:
- Missing rows: Rows might not be visible because they are being moved to a new page due to an update.
- Duplicate rows: Rows might appear twice if your query reads the same row from both the old and new pages during a move operation.
- Example: In a clustered index, rows might be moved during an update, and
NOLOCKcould result in reading both the old and new versions of the data.
4. Schema Instability
- Risk: If the table schema is being modified (e.g., an index is being rebuilt), a
NOLOCKquery might fail or return inconsistent results.
5. Undocumented Behavior
- Risk: The behavior of
NOLOCKis not always predictable in highly concurrent environments. For example:- It might return data from a heap table that is in an intermediate state, resulting in corrupted or invalid results.
- It may behave differently depending on the SQL Server version and the underlying physical storage engine.
6. False Sense of Performance Improvement
- Risk: While
NOLOCKcan reduce blocking and improve performance in high-concurrency environments, it often masks underlying issues like:- Poor indexing.
- Inefficient query design.
- Overly long transactions.
Rather than using NOLOCK, it’s better to address the root causes of performance issues.
7. Inappropriate for Critical Operations
- Risk: For operations where data integrity and accuracy are essential (e.g., financial reports, audits, inventory counts), using
NOLOCKcan lead to incorrect decisions or outcomes based on invalid data.
When Should You Use NOLOCK?
There are rare cases where NOLOCK might be acceptable:
- Non-critical queries, such as exploratory queries or when generating reports that tolerate occasional inaccuracies.
- Situations where performance is more important than 100% accuracy, and you explicitly understand and accept the risks.
However, even in these cases, consider alternatives like READ COMMITTED SNAPSHOT ISOLATION (RCSI) or query tuning to achieve better performance without compromising accuracy.
Conclusion
While NOLOCK can improve performance by reducing blocking, it comes with significant risks to data accuracy and consistency. It should be used only after understanding and accepting its implications, and only when alternatives like query tuning or isolation-level changes are insufficient or impractical. For critical systems or operations, avoid using NOLOCK to ensure reliable and accurate results.
Now, we should all go look at Brent Ozar’s post about NOLOCK here, where he gives examples of how to produce those bad things that I mentioned above (Brent is a SUPER smart guy by the way!).
The EckMan 