Bits of .NET

🗄️ The Two Sessions That Disagreed About What the Database Actually Said

Two connections querying the exact same table at nearly the same moment can legitimately see different data – not due to a bug in either query, but because the database’s isolation level governs exactly what a transaction is allowed to see of another transaction’s uncommitted or concurrently-changing work, and the default isolation level (READ COMMITTED) still allows a fair amount of variation between what two overlapping transactions observe, especially across multiple statements within the same transaction.

🔎 The Problem

-- Session A, inside a transaction, reads the same row twice:
BEGIN TRAN;
SELECT Status FROM Orders WHERE OrderId = 501;   -- Returns '"'"'Pending'"'"'
-- ... some other work happens here ...
SELECT Status FROM Orders WHERE OrderId = 501;   -- Returns '"'"'Shipped'"'"'!
COMMIT;

-- Session B, running concurrently, committed an UPDATE to that exact
-- row BETWEEN Session A'"'"'s two reads. Under READ COMMITTED (SQL Server'"'"'s
-- default), this is completely expected and correct behavior - each
-- individual read sees whatever was committed at the moment it ran,
-- with no guarantee that two reads within the same transaction see a
-- consistent snapshot of the data.

✅ Fix: Choose the Isolation Level That Matches What Consistency You Actually Need

• REPEATABLE READ (or SNAPSHOT isolation, which avoids some of REPEATABLE READ’s locking cost) guarantees that a value read once within a transaction reads the same on a second read within that same transaction – the right choice specifically when a transaction’s logic depends on a value not changing out from under it mid-transaction.
• SNAPSHOT isolation specifically (enabled at the database level, then requested per-transaction) gives each transaction a consistent point-in-time view of the whole database for its entire duration, without the same blocking behavior REPEATABLE READ’s locks introduce – often the better fit for read-heavy reporting-style transactions that need consistency without wanting to block writers.
• For the specific, common case of ‘read a value, then act based on it, and nobody else should be able to change it in between,’ an explicit UPDLOCK/HOLDLOCK hint (or just wrapping the read-then-write in a properly isolated transaction to begin with) is worth reaching for directly, rather than assuming the default isolation level provides that guarantee on its own.

⚠️ Why READ COMMITTED’s Behavior Surprises People

• ‘Read committed’ sounds like it should mean ‘a consistent, committed view of the database’ – and it does guarantee that anything READ was genuinely committed at the time it was read, but it makes no promise at all that two separate reads within the same transaction will see the same committed data, since a competing transaction is free to commit a change in between them.
• This is a very reasonable thing to overlook because most individual queries genuinely don’t care about this distinction – the bug only becomes visible in a transaction whose logic specifically depends on a value staying the same across more than one read within its own boundaries, which is a narrower and easier-to-miss scenario than it initially sounds.