How a Blocking Transaction Took Down a Listing Page — and How RCSI Fixed It

The Symptom

A listings page on a live project started hanging intermittently. Not on every request — only under load, and only for a few seconds at a time before resolving. The kind of behaviour that does not reproduce reliably in development and disappears before you finish writing the incident report.

The first instinct in this situation is usually to look at the query. Check the execution plan, verify the indexes, check whether a recent deployment changed the query. In this case, the query was simple:

SELECT
    l.Id,
    l.Title,
    l.Price,
    l.Location,
    l.CreatedAt
FROM Listings l
WHERE l.Status = 'active'
  AND l.CategoryId = @categoryId
ORDER BY l.CreatedAt DESC
OFFSET @offset ROWS
FETCH NEXT 20 ROWS ONLY;

The indexes were correct. The execution plan showed an index seek, not a scan. The table had fewer than 100,000 rows. There was no reason for this query to take more than a few milliseconds.

The problem was not the query. The query was waiting for something else to finish.

Diagnosing Blocking in SQL Server

SQL Server provides dynamic management views (DMVs) that expose active sessions, wait states, and blocking relationships in real time. When a query is blocked — waiting for a lock held by another session — it appears in sys.dm_exec_requests with a non-zero blocking_session_id.

Running this during the incident revealed the problem immediately:

SELECT
    blocking.session_id     AS blocking_session,
    blocked.session_id      AS blocked_session,
    blocked.wait_type,
    blocked.wait_time / 1000.0  AS wait_seconds,
    blocked.status,
    DB_NAME(blocked.database_id) AS database_name,
    blocked_text.text        AS blocked_query,
    blocking_text.text       AS blocking_query
FROM sys.dm_exec_requests        blocked
JOIN sys.dm_exec_sessions        blocking
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)    AS blocked_text
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle)   AS blocking_text
WHERE blocked.blocking_session_id > 0;

The output showed:

• One blocking session running a write transaction — inserting and updating rows in the Listings table as part of a batch import job
• Multiple blocked sessions — all SELECT queries on the same table, each waiting on a LCK_M_S lock (shared lock request, waiting for an exclusive lock to release)
• Wait times ranging from 800ms to over 4 seconds during peak import runs

The listing page timeout was not a slow query. It was a fast query waiting behind a locked table.

Why This Happens — SQL Server's Default Locking Behaviour

SQL Server's default isolation level is READ COMMITTED. Under this isolation level:

• Write operations (INSERT, UPDATE, DELETE) acquire exclusive locks on the affected rows for the duration of the transaction
• Read operations (SELECT) acquire shared locks on the rows they read
• A shared lock and an exclusive lock are incompatible — a reader must wait for a writer to finish, and a writer must wait for all active readers to finish

-- Session A: long-running import transaction
BEGIN TRANSACTION;
    INSERT INTO Listings (Title, Price, CategoryId, Status)
    VALUES ('New Property', 250000, 3, 'active');  -- exclusive lock held

    -- ... 200 more inserts, processing external data ...
    -- Exclusive lock held on Listings rows throughout
COMMIT;

-- Session B: listing page query, running at the same time
SELECT Id, Title, Price FROM Listings
WHERE Status = 'active' AND CategoryId = 3
ORDER BY CreatedAt DESC;
-- Waiting for Session A's exclusive lock to release before it can acquire a shared lock
-- Result: LCK_M_S wait — the listing page hangs

Under normal conditions with short transactions this resolves in milliseconds and users never notice. When the write transaction is long-running — a batch import, a bulk update, a report generation job that also writes — readers stack up behind it. Under concurrent load, the wait times compound and requests time out.

The Fix — Read Committed Snapshot Isolation (RCSI)

Read Committed Snapshot Isolation is a database-level setting that changes how SQL Server handles reads under the READ COMMITTED isolation level. Instead of acquiring shared locks, readers use row versioning — they read a consistent snapshot of the data as it existed before the current transaction started.

The result: readers never wait for writers. Writers never wait for readers. The blocking relationship is eliminated entirely.

Enabling RCSI requires a single command:

-- Check current RCSI status
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDatabase';

-- Enable RCSI
-- Note: this requires exclusive access to the database briefly
-- Run during low-traffic period or maintenance window
ALTER DATABASE [YourDatabase]
SET READ_COMMITTED_SNAPSHOT ON
WITH NO_WAIT;

If the database has active connections, use WITH ROLLBACK AFTER to gracefully handle them:

ALTER DATABASE [YourDatabase]
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK AFTER 30 SECONDS;

After enabling RCSI, verify the change took effect:

SELECT
    name,
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
FROM sys.databases
WHERE name = 'YourDatabase';

-- Expected output:
-- name            | is_read_committed_snapshot_on | snapshot_isolation_state_desc
-- YourDatabase    | 1                             | ON

What Changes After Enabling RCSI

From the application's perspective, nothing changes. No code modifications are required. Queries continue to run under READ COMMITTED — the isolation level that Entity Framework Core and most .NET database access libraries use by default. The difference is in how SQL Server fulfils those read requests internally.

Before RCSI:

-- Reader acquires shared lock, waits if exclusive lock is held
SELECT * FROM Listings WHERE Status = 'active';
-- ↑ blocked by concurrent INSERT/UPDATE transaction

After RCSI:

-- Reader reads from row version store — no lock acquired
SELECT * FROM Listings WHERE Status = 'active';
-- ↑ returns immediately with the committed state before the active transaction
-- Writer continues uninterrupted

The read returns the last committed state of the data — consistent and correct, just not including the in-progress transaction. This is the same guarantee READ COMMITTED has always provided; RCSI just fulfils it without blocking.

The Trade-Off — tempdb Usage

Row versioning has a cost. SQL Server stores previous row versions in tempdb so that concurrent readers can access the snapshot. This increases tempdb I/O and storage usage proportionally to the volume and duration of write transactions.

In practice, for most OLTP applications, this overhead is modest and predictable. The alternative — reader-writer blocking under load — is unpredictable and directly affects user-facing response times.

Monitor tempdb version store size after enabling RCSI:

-- Check version store size and activity
SELECT
    reserved_page_count,
    reserved_space_kb  = reserved_page_count * 8,
    used_page_count,
    user_object_reserved_page_count
FROM sys.dm_db_file_space_usage;

-- Check version store cleanup rate
SELECT
    version_store_reserved_page_count  AS version_store_pages,
    version_store_reserved_page_count * 8 AS version_store_kb
FROM sys.dm_db_file_space_usage;

SQL Server automatically cleans up row versions once no active transaction needs them. If tempdb grows unexpectedly, the usual cause is long-running transactions that prevent version cleanup — which is another argument for keeping transactions short.

Monitoring for Blocking — Keep This Query Handy

Even with RCSI enabled, blocking can still occur between concurrent write transactions (writer-writer contention). Keep a blocking monitor query available:

-- Real-time blocking monitor
SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0   AS wait_seconds,
    r.status,
    t.text                 AS current_query,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions  s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

-- Find sessions with open transactions
SELECT
    s.session_id,
    s.login_name,
    s.status,
    t.transaction_begin_time,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS open_seconds,
    st.text AS last_query
FROM sys.dm_exec_sessions          s
JOIN sys.dm_tran_session_transactions tst ON s.session_id = tst.session_id
JOIN sys.dm_tran_active_transactions  t   ON tst.transaction_id = t.transaction_id
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) st
ORDER BY t.transaction_begin_time;

Entity Framework Core — Keeping Transactions Short

RCSI eliminates reader-writer blocking. It does not eliminate writer-writer blocking. Long-running write transactions still block other writers on the same rows. The complementary habit is keeping transactions as short as possible in application code:

// Long transaction — holds locks for the duration of external calls
await using var tx = await _db.Database.BeginTransactionAsync();

var listing = await _db.Listings.FindAsync(listingId);
listing.Status = "processing";
await _db.SaveChangesAsync();

// External API call — could take 2-5 seconds
// Write lock on the listing row is held throughout this wait
var result = await _externalService.ValidateListingAsync(listing);

listing.Status = result.IsValid ? "active" : "rejected";
await _db.SaveChangesAsync();
await tx.CommitAsync();

// --------------------------------------------------------

// Short transaction — external call outside the transaction
var result = await _externalService.ValidateListingAsync(listingId);

await using var tx = await _db.Database.BeginTransactionAsync();
var listing    = await _db.Listings.FindAsync(listingId);
listing.Status = result.IsValid ? "active" : "rejected";
await _db.SaveChangesAsync();
await tx.CommitAsync();
// Lock held for milliseconds, not seconds

The Outcome

After enabling RCSI on the database, the listing page timeouts stopped. The import job continued running on its normal schedule. Concurrent reads on the Listings table returned immediately regardless of what write transactions were in progress.

The diagnostic queries confirmed zero blocking sessions on the Listings table during subsequent import runs. The only change was a single ALTER DATABASE command and no application code modifications.

RCSI is not a silver bullet for all database performance problems. But for any SQL Server database with concurrent reads and writes on the same tables — which is nearly every production application — it should be enabled by default. The reader-writer blocking it eliminates is a reliability problem waiting to surface under load.

Checklist for Similar Incidents

• Slow query under load that is fast in isolation — check for blocking before optimising the query
• Use sys.dm_exec_requests with blocking_session_id > 0 to identify the blocker
• Check wait_type — LCK_M_S is a shared lock wait, confirming reader-writer contention
• Enable RCSI with ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON
• Monitor tempdb version store after enabling — size should be stable and small for short transactions
• Keep write transactions short — do external calls before opening the transaction, not inside it

For more on database configuration and how it affects application performance, see the post on database configuration done right. For database indexing and query performance, see database indexing in PostgreSQL and MySQL. If you are dealing with a performance issue on a .NET backend, see the .NET backend development services page or get in touch.