Bits of .NET

🔧 The Row Count That Changed Depending on Who Was Asking

Running what looks like the exact same SELECT statement from two different connections, or from the same report run twice a few seconds apart, and getting two different row counts back feels like data corruption or a flaky query – but it’s frequently just the session’s transaction isolation level doing exactly what it was configured to do. The default READ COMMITTED isolation level can let a long-running query see rows that were committed partway through its own execution (a phenomenon sometimes called a phantom read or a non-repeatable read), while a session running under SNAPSHOT or REPEATABLE READ sees a single consistent view for the whole query – so the same report run under two different session settings, or against a table being actively written to, can legitimately disagree with itself.

🔎 The Problem

-- Session A (default READ COMMITTED), query takes 8 seconds to run
-- against a large, actively-written orders table:
SELECT COUNT(*) FROM Orders WHERE Status = 'Pending';
-- Result: 14,208

-- Meanwhile, during those 8 seconds, 40 new pending orders are
-- inserted and committed by other sessions. Because READ COMMITTED
-- only guarantees each individual row read was committed at the
-- moment it was read - not that the whole result set is consistent
-- as of one single point in time - some of those 40 new rows can
-- be included, and some rows that existed at the start but were
-- updated out of 'Pending' partway through can be excluded.

-- Session B, same query, run under SNAPSHOT isolation a moment
-- later: 14,238 - a different, equally "correct" answer, because
-- it's answering a different, equally valid question: what did
-- the table look like at one consistent instant.

✅ Fix: Pick the Isolation Level That Matches What the Query Needs to Promise

• Switching reporting and analytics queries to SNAPSHOT isolation (after enabling ALLOW_SNAPSHOT_ISOLATION on the database) gives every row in the result set a consistent, single point-in-time view without taking the shared locks that READ COMMITTED’s default locking variant would, which is usually exactly what a report author actually wants when they say ‘why did the count change.’
• For a one-off query where a slightly stale but internally consistent snapshot is acceptable, running it inside an explicit transaction with REPEATABLE READ guarantees that any row read once stays the same for the rest of that transaction, trading some extra locking for a result that can’t shift under you mid-query.
• Documenting which isolation level a given report or query actually runs under, right alongside the query itself, turns ‘the numbers don’t match’ from a mystery into a one-line explanation the next person doesn’t have to rediscover from scratch.

⚠️ Why Two ‘Correct’ Queries Can Disagree

• READ COMMITTED (SQL Server’s default) only promises that each row it returns was committed data at the moment that row was read – it makes no promise that the result set as a whole reflects one single consistent snapshot of the table, which is precisely the gap that lets concurrent writes change the count mid-query.
• This is far more visible on large, slow-running queries against actively-written tables than on fast queries against quiet ones, which is why it tends to show up first in nightly batch reports or dashboards against high-traffic tables, not in quick ad hoc lookups during development.