What does WITH (NOLOCK) do, and why is it dangerous?
Quick Answer
WITH (NOLOCK) sets Read Uncommitted for that table — it reads without taking shared locks and ignores exclusive locks. It can return dirty data (rolled back later), duplicate rows, or missing rows, because a page split during your read can move data you've already passed or not yet reached. It is not a safe “go faster” switch.
NOLOCK is read-uncommitted for that table — it doesn't take shared locks and it doesn't respect exclusive ones. Everyone knows it can read dirty data that later gets rolled back, but the part that surprises people is that it can also return the same row twice or skip a row entirely, because if a page split happens while you're scanning, data can move behind or ahead of your position. So it's only acceptable for something approximate. The right fix for the blocking it's usually meant to solve is read-committed snapshot isolation — readers get a consistent committed view from row versions and never block writers, and it's actually correct.
- People add it to reduce blocking on reports. It works, but the results can be silently wrong — fine for a rough dashboard, unacceptable for anything financial or transactional.
- The “duplicate/missing rows” problem is the surprising one: it's not just about uncommitted data, it's that an allocation-order scan can read a row twice or skip it if pages move mid-scan.
- Better alternative: enable Read Committed Snapshot Isolation (RCSI) — readers get a consistent, committed snapshot via row versioning and don't block writers, with no correctness cost. Or use
SNAPSHOTisolation for specific transactions.