A Real Dirty Read: Seeing a Value That Never Actually Existed

Two sessions, one uncommitted price change, and a value that gets read, trusted, and then turns out to have never really existed.

Session A: an uncommitted update, held open

-- session_a.sql
BEGIN TRAN;
UPDATE books SET price = 9999.99 WHERE book_id = 1;
WAITFOR DELAY '00:01:00';
ROLLBACK TRAN;

sqlcmd -S localhost -U sa -P '<password>' -C -i session_a.sql &

Running in the background, holding an exclusive lock on book_id = 1 for a full minute before rolling back — a deliberately uncommitted, deliberately temporary change.

Confirming the lock is real, not assumed

SELECT CAST(resource_type AS VARCHAR(10)) AS resource_type,
       CAST(request_mode AS VARCHAR(6)) AS request_mode,
       CAST(request_status AS VARCHAR(6)) AS request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('bookshop');

resource_type request_mode request_status
------------- ------------ --------------
DATABASE      S            GRANT
PAGE          IX           GRANT
KEY           X            GRANT
OBJECT        IX           GRANT

(4 rows affected)

A real X (exclusive) lock on the row’s key, actually held right now — not assumed from timing. Waiting for this to appear (rather than guessing how long session A takes to start) is what makes the rest of this lesson reliable regardless of how fast either session happens to run.

The bug: reading a value that will never really exist

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT price FROM books WHERE book_id = 1;

price
--------
9999.99

(1 rows affected)

9999.99 — session A’s uncommitted change, read directly while the exclusive lock is still held. READ UNCOMMITTED ignores other transactions’ locks entirely, so it sees writes that haven’t been (and might never be) committed.

What the default isolation level does instead

SELECT price FROM books WHERE book_id = 1;

[blocks -- no output within 5 seconds, timeout exit code 124]

No dirty read this time — the default READ COMMITTED isolation level requires a shared lock to read the row, and session A’s exclusive lock is incompatible with that. The read simply waits. This isn’t a failure; the 5-second timeout used to demonstrate it just ran out while the read was still genuinely blocked.

The value that never existed

-- after session A's WAITFOR DELAY elapses and it rolls back
SELECT price FROM books WHERE book_id = 1;

price
--------
11.99

(1 rows affected)

11.99 — the real, original price. 9999.99, read moments earlier under READ UNCOMMITTED, was never actually committed to the database at any point in its history. A dirty read isn’t “reading slightly stale data” — it can be reading data that turns out to have never truly existed at all.

Practice

SELECT price FROM books WITH (NOLOCK) WHERE book_id = 1;

price
--------
9999.99

(1 rows affected)

SELECT session_id, blocking_session_id, CAST(wait_type AS VARCHAR(20)) AS wait_type
FROM sys.dm_exec_requests
WHERE blocking_session_id != 0;

session_id blocking_session_id wait_type
---------- ------------------- --------------------
        56                  51 LCK_M_S

(1 rows affected)

SELECT COUNT(*) FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('bookshop') AND request_mode = 'X';

-----------
          0

(1 rows affected)

Next: stored procedures and triggers.