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
- A writer holds an exclusive lock on the rows it changes until its transaction ends.
- Under the default READ COMMITTED isolation level, a scan that reads a locked row or page waits for the lock to be released.
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
- The
$PARTITIONexample uses object names from the Microsoft AdventureWorks sample database. DBCC PAGEoutput is useful for diagnosis only. Its arguments are database, file id, page id and print option.