LLI Wiki — My Digital Knowledge Base

Why a SELECT waits on row locks held by an INSERT or UPDATE

Understand why a SELECT is blocked by an INSERT or UPDATE, compare READPAST, NOLOCK and RCSI, and find the session holding the lock.

A SELECT can wait on an INSERT or UPDATE because the writer holds a lock on a row or page that the reader needs. This article describes the options and how to find the session that holds the lock. It is a summary of a Stack Exchange answer on the topic, rewritten: How to get SQL INSERT and/or UPDATE to not lock entire table on MS SQL Server.

Why a reader waits

Options

Option INSERT UPDATE Trade-off
READPAST hint Skips the locked rows. Skips the locked rows, so the reader does not see the committed version of a row that is being updated. Results can be incomplete.
NOLOCK hint Reads without waiting. Reads without waiting. Can return uncommitted (dirty) data.
Read Committed Snapshot Isolation (RCSI) Readers see the last committed version of each row and do not wait. Same as INSERT. Row versions are stored in tempdb, which uses more space.

RCSI gives the result most people expect, at the cost of more tempdb usage.

Find the session that holds the lock

-- Locks held by the sessions of the instance
EXEC sp_lock;

-- Sessions, blockers and their locks (sp_WhoIsActive must be installed first)
EXEC sp_WhoIsActive @get_locks = 1;

Find the row or page behind a lock

%%LOCKRES%% is an undocumented virtual column that returns the lock resource hash of each row. Use it to find the row behind a key lock, then locate its page.

SELECT %%LOCKRES%%, *
FROM dbo.Orders
WHERE %%LOCKRES%% = '(64e4c5fecce7)';

-- List the pages of index 4 of the table, then dump one page
DBCC IND (AppDb, 'dbo.Orders', 4);
DBCC PAGE (AppDb, 1, 22708, 3) WITH TABLERESULTS;

Row counts per partition

SELECT t.object_id, t.name, p.partition_id, p.partition_number, p.rows
FROM sys.partitions AS p
    INNER JOIN sys.tables AS t
        ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
    AND t.name = 'Orders'
ORDER BY p.partition_number;

-- Row count per partition with the $PARTITION function
SELECT $PARTITION.FullOrderDateKeyRangePFN(FULLDATE) AS PARTITIONID,
       COUNT(*) AS ROW_COUNT
FROM dbo.FactInternetSales_Partitioned
GROUP BY $PARTITION.FullOrderDateKeyRangePFN(FULLDATE)
ORDER BY PARTITIONID;

Notes

Mastodon LLIMastodon SocialFosstodon