LyraLearn AI Learning Platform
Exams
← Module 6 · Reporting Workloads and a Telerik Triage Playbook

Blocking, RCSI, and Why NOLOCK Is the Wrong Fix

When a report runs against a live transactional database, the report and the application get in each other's way. Someone will suggest adding WITH (NOLOCK). This lesson explains what that actually does, and what to do instead.

The mechanism

Under the default READ COMMITTED isolation level, a reader takes shared locks on the rows and pages it reads, and a writer takes exclusive locks on the rows it modifies. Shared and exclusive locks are incompatible. So:

And in reverse:

Confirming blocking is the problem

-- Who is blocked, and by whom, right now
SELECT  r.session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time / 1000.0 AS wait_seconds,
        r.wait_resource,
        DB_NAME(r.database_id) AS database_name,
        SUBSTRING(t.text, 1, 200)  AS blocked_query,
        bs.program_name            AS blocker_program,
        bs.host_name               AS blocker_host,
        SUBSTRING(bt.text, 1, 200) AS blocker_query
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
LEFT JOIN sys.dm_exec_sessions AS bs ON bs.session_id = r.blocking_session_id
OUTER APPLY (
    SELECT TOP (1) txt.text
    FROM sys.dm_exec_connections AS c
    CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
    WHERE c.session_id = r.blocking_session_id
) AS bt
WHERE r.blocking_session_id <> 0;

And the historical view — which lock types have cost you the most time since startup:

SELECT  wait_type,
        wait_time_ms / 1000.0     AS wait_seconds,
        waiting_tasks_count,
        wait_time_ms / NULLIF(waiting_tasks_count,0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LCK%'
  AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

Substantial LCK_M_S (shared-lock waits) means readers are being blocked. LCK_M_X and LCK_M_IX mean writers are being blocked, quite possibly by your report.

Why NOLOCK is the wrong fix

WITH (NOLOCK) is equivalent to READ UNCOMMITTED for that table. It takes no shared locks, so it never waits and never blocks. That is why it appears to work.

What it actually does is permit reading data that is in an inconsistent state. Three distinct failure modes, all of which produce wrong numbers in a report with no error and no warning:

Dirty reads. You read a row modified by a transaction that has not committed. If that transaction rolls back, your report contains a value that never existed in the database. For a financial report, that is a number someone may act on that has no basis in the data.

Missing rows. This one surprises people, because it is not about uncommitted data at all. When a page split occurs during your scan — a normal consequence of an insert into a full page — rows move to a new page. A NOLOCK scan following the page chain can pass the new page's position before the row arrives there, and the row is simply absent from your results. A committed row that was present before and after your query does not appear in it.

Duplicate rows. The mirror image. A row moves from a page you have not yet read to a page you have already read, and you count it twice. Your SUM is inflated by an amount that depends on concurrent activity, so it differs every time you run the report.

The last two mean SELECT COUNT(*) FROM Orders WITH (NOLOCK) on a busy table can return a number that is wrong in either direction, with no indication that anything happened.

NOLOCK can also fail outright with error 601, "Could not continue scan with NOLOCK due to data movement," which is at least honest about the problem.

The reason "just add NOLOCK" is such a persistent folk remedy is that it does solve the symptom immediately and its failure mode is silent. A report that is fast and occasionally wrong looks exactly like a report that is fast and right, until someone reconciles the numbers.

The correct fix: READ COMMITTED SNAPSHOT

RCSI changes how READ COMMITTED works: instead of taking shared locks, readers see the last committed version of each row, held in the version store. Readers never block writers, writers never block readers, and every reader sees a consistent, committed snapshot.

You get NOLOCK's concurrency with correct results.

-- Check current state
SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();

-- Enable it. Requires exclusive database access briefly, so schedule a window.
-- ROLLBACK IMMEDIATE terminates other connections: use with the usual approvals.
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Once on, existing queries need no changes — READ COMMITTED simply behaves differently. That is what makes it such a good fit for a legacy application: no code change, no deployment.

The costs, honestly stated:

Monitor the version store:

SELECT  SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
        SUM(user_object_reserved_page_count)   * 8 / 1024 AS user_objects_mb,
        SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- The longest-running transaction currently holding versions alive
SELECT TOP (5)
       t.transaction_id, t.transaction_begin_time,
       DATEDIFF(SECOND, t.transaction_begin_time, SYSDATETIME()) AS age_seconds,
       s.session_id, s.host_name, s.program_name, s.login_name
FROM sys.dm_tran_active_snapshot_database_transactions AS t
JOIN sys.dm_exec_sessions AS s ON s.session_id = t.session_id
ORDER BY t.transaction_begin_time;

SNAPSHOT isolation — the other option

ALLOW_SNAPSHOT_ISOLATION provides full statement-and-transaction-level consistency: every statement in the transaction sees the database as of the transaction's start. Useful for a multi-query report that must be internally consistent across all its datasets.

ALTER DATABASE [YourDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Then, per report connection:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    -- every dataset in the report sees the same consistent point in time
COMMIT;

It requires the connection to opt in, so it does need an application change, and it can raise update conflict errors if the session also writes. For a read-only report, that is not a concern.

If you cannot enable RCSI

Sometimes the change is blocked — a vendor application, a tempdb constraint, an approval you cannot get. In that case, in order of preference:

  1. Move the report off the primary. A readable secondary or a restored snapshot removes the contention entirely (next lesson). Best answer.
  2. Make the report shorter. A report holding locks for 2 seconds instead of 40 causes 5% of the blocking. Everything in Modules 3, 4 and Lesson 2 contributes here.
  3. Run it off-hours if the data freshness requirement permits.
  4. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED as an explicit, documented, temporary measure — with the report labelled as approximate, and never for financial or compliance figures. If you end up here, the honest framing is "we accepted possibly-wrong numbers to get speed," and that should be a decision someone signs off on, not a hint quietly added to a query.

The short version

NOLOCK works by not taking shared locks, so besides dirty reads it can miss committed rows or count them twice when pages split during the scan — a report can return a different total each run with no error at all. The right fix is READ COMMITTED SNAPSHOT: readers see the last committed version from the version store instead of taking locks, so readers and writers stop blocking each other and the numbers stay correct. It is a database-level setting, so the legacy application needs no code change — the cost is tempdb space for the version store, which you should size and monitor.

🧠 Quiz yourself on this lesson →

Ask the AI Tutor

Grounded in the course lessons — it cites its sources and says when it doesn't know.