Bits of .NET

🗄️ NOLOCK = Maybe-Correct Results

WITH (NOLOCK) is everywhere as a quick fix for blocking, but it doesn’t just risk reading uncommitted data — under real concurrent writes it can return the SAME row twice or skip a row entirely, with no error to tell you it happened.

🐞 The Problem

SELECT OrderId, CustomerName, Total
FROM Orders WITH (NOLOCK)
WHERE OrderDate >= '2026-08-01';
-- Under concurrent inserts/updates during a page split,
-- this can silently return duplicate OR missing rows --
-- not just "possibly stale" data.

🔍 Why This Happens

NOLOCK = READ UNCOMMITTED isolation: no shared locks are taken,
so the scan doesn't block writers, but it also doesn't guarantee
a consistent snapshot. If a page splits (rows physically move to
a new page) WHILE your scan is mid-flight, your scan can revisit
rows that already moved (duplicate) or miss rows that moved before
the scan reached their old location (skipped).

✅ The Fix: READ COMMITTED SNAPSHOT Isolation

• Gives you a consistent, non-blocking read using row versioning (tempdb) instead of shared locks.
• No duplicate/missing row risk, no dirty reads — and no blocking of writers either.
• One-time database-level setting, no query changes needed afterward.

⚙️ Enable Once at the Database Level

ALTER DATABASE MyDb SET READ_COMMITTED_SNAPSHOT ON;
-- Requires no other active connections to the DB when run.
-- After this, plain SELECTs (no hint needed) read a consistent
-- snapshot without blocking or being blocked by writers.

⚠️ When NOLOCK Is Still Fine

• Rough dashboards/analytics where a rare duplicate or missing row genuinely doesn’t matter.
• Never on financial totals, inventory counts, or anything a decision gets made from.