Quick Scan Report – UPDLOCK Hint

What this check looks for

Blocking history collected in DBHealthHistory.dbo.blockingOverTime, looking for blocking chains whose head is a query containing the UPDLOCK hint. It reports the database and the table involved.

This check needs the DBHealthHistory database and its blocking collection. Without the collected history there is nothing to look at, so the check does nothing on an instance where historic collection is not set up.

This page also covers issue 223, Updlock causing waiting, which is the same hint producing waits rather than a full blocking chain.

Why it matters

UPDLOCK does exactly what it says, and that is the problem. It is working as designed and the design is being applied too widely.

Normally a SELECT takes a shared lock, which other readers can also take. WITH (UPDLOCK) takes an update lock instead, and update locks are not compatible with each other. So two sessions running the same SELECT ... WITH (UPDLOCK) serialize: the second waits for the first, even though neither has modified anything yet.

The hint exists for a genuine reason, and it is a good one: the read then update race.

-- without the hint, two sessions can both read the same row and both decide to act on it
BEGIN TRANSACTION;
    SELECT @next = MIN([id]) FROM [dbo].[Queue] WITH (UPDLOCK, READPAST) WHERE [status] = 'ready';
    UPDATE [dbo].[Queue] SET [status] = 'taken' WHERE [id] = @next;
COMMIT;

That is the correct use: a small, targeted read that is about to become a write, holding the lock for the shortest possible time.

What goes wrong is scope and duration:

• Applied to a whole table or a large range, where every row is locked rather than the one about to change.
• Held for a long transaction, so the serialization lasts as long as the whole unit of work rather than as long as the row is at risk.
• Copied into queries that never update anything, because it appeared to fix a problem once and was carried into a template.
• Combined with a missing index, so the query scans and takes update locks on every row it touches rather than seeking to one.

That last one is the most common and the most fixable: the hint is fine and the access path is wrong.

How to confirm it yourself

Where the collection exists, what it recorded:

SELECT TOP (100)
       [collectionTime],
       [blockDatabase],
       [topBlock],
       [blockedCount]
  FROM [DBHealthHistory].[dbo].[blockingOverTime] WITH (NOLOCK)
 WHERE LOWER([topBlock]) LIKE '%updlock%'
 ORDER BY [collectionTime] DESC;

Blocking happening right now, with the hint visible in the text:

SELECT r.[session_id],
       r.[blocking_session_id],
       r.[wait_type],
       r.[wait_time],
       DB_NAME(r.[database_id]) AS [database_name],
       t.
  FROM sys.dm_exec_requests AS r WITH (NOLOCK)
 OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE r.[blocking_session_id] <> 0
 ORDER BY r.[wait_time] DESC;

Find every module that uses the hint, which is the audit worth doing once:

SELECT OBJECT_SCHEMA_NAME(m.[object_id]) AS [schema_name],
       OBJECT_NAME(m.[object_id])        AS [object_name],
       o.[type_desc]
  FROM sys.sql_modules AS m WITH (NOLOCK)
 INNER JOIN sys.objects AS o WITH (NOLOCK) ON o.[object_id] = m.[object_id]
 WHERE m.[definition] LIKE '%UPDLOCK%'
 ORDER BY [schema_name], [object_name];

The Schema Search report does the same across every database.

How to fix it

Do not simply remove the hint. It was added to prevent a race, and removing it reintroduces whatever bug it was fixing, usually as duplicate processing or a lost update that is far harder to diagnose than blocking.

In order:

1. Check the access path first. Run the query and look at the plan. If it scans, the hint is locking every row it reads rather than the one it intends to change. An index that turns the scan into a seek fixes the blocking without touching the hint, and it is the best outcome available.
2. Narrow the scope. WITH (UPDLOCK, ROWLOCK) on a targeted predicate rather than the hint on a broad range.
3. Shorten the transaction. Take the lock as late as possible and commit as soon as possible. A transaction that takes the update lock and then does other work is holding it for no reason.
4. Add READPAST for a queue. WITH (UPDLOCK, READPAST) is the classic queue pattern: readers skip rows another session has claimed instead of queuing behind them. It converts blocking into throughput for exactly the workload this hint is usually protecting.
5. Consider READ COMMITTED SNAPSHOT at the database level if readers blocking readers is a broader problem. It does not remove the need for UPDLOCK in a read then update pattern, and it removes a great deal of other blocking.

For a modern alternative, SQL Server 2022’s optimized locking reduces lock footprint substantially, and there is a report on whether it is enabled.

How long it takes

About half an hour to identify the query and its plan. Adding an index or restructuring a transaction is a change that needs testing.

Related reports

Related checks

Frequently asked questions

Why does a SELECT block another SELECT? Because UPDLOCK takes an update lock rather than a shared lock, and update locks are not compatible with one another. That is the hint’s purpose.

Can I just remove it? Not without knowing what it was preventing. It is nearly always there to stop a read then update race, and removing it brings that back as an intermittent data bug.

Nothing is reported but we have blocking. This check reads collected blocking history from DBHealthHistory. Without that collection it finds nothing, and the live query above is the way to look.

Is READPAST safe? For a queue, yes, and that is what it is for: it skips rows locked by another session. It is not safe where the query has to see every matching row, because it silently skips some.