What is WITH (NOLOCK)?
In SQL Server, when you run a SELECT query, the database engine usually places a Shared Lock on the data. This prevents other processes from changing that data while you are reading it.
WITH (NOLOCK) is a table hint that tells SQL Server to ignore these locks. It allows your query to read data even if another transaction is currently updating or deleting it. This is known as a Dirty Read.
Why Use It? (The Benefits)
Prevents Blocking: Your query won't wait for other transactions to finish. This is vital in high-traffic applications.
Reduces Deadlocks: Since your query isn't requesting locks, it is much less likely to be part of a deadlock.
Higher Performance: By skipping the overhead of lock management, the query execution is faster and uses fewer resources.
The Hidden Danger: Dirty Reads
The biggest risk is Data Inaccuracy. Because you are reading uncommitted data, you might encounter:
Dirty Reads: Reading data that a user has changed but might still "Roll Back" (cancel). You end up reporting data that technically never existed.
Phantom Reads: A row might appear or disappear while your query is scanning the table.
Double Counting: If a row moves during a page split while you are reading, you might count the same row twice or skip it entirely.
Practical Examples
1. Basic Syntax
To use it, simply add the hint after the table name in your FROM clause:
2. Using it with Joins
When joining multiple tables, you must apply the hint to each table individually:
When SHOULD You Use It?
Reporting: When you are running a report on millions of rows and a 1% margin of error is acceptable.
Log Data: Checking logs or audit trails where data is rarely updated once written.
Non-Critical Data: Displaying "Total Likes" or "View Counts" where perfect accuracy isn't life-or-death.
When SHOULD You AVOID It?
Financial Transactions: Never use
NOLOCKwhen calculating bank balances or processing payments.Ordering Systems: Avoid it when checking inventory levels (you might sell the same item twice!).
Data Migration: If you are moving data from one table to another, accuracy is mandatory.
A Modern Alternative: Read Committed Snapshot (RCSI)
Instead of sprinkling NOLOCK everywhere, many modern developers enable RCSI at the database level.
This allows readers to see the last "consistent" version of the data without blocking writers, providing the speed of NOLOCK without the risk of dirty reads.