SQL SERVER – Locking Hints with Correct Scope and Safe Examples

Locking Hints change how a table reference participates in locking. I choose the required consistency before changing the query.

I previously included DBLOCK in this list. It isn’t a valid SQL Server table hint. Valid granularity hints include ROWLOCK, PAGLOCK and TABLOCK. They don’t eliminate lock escalation or other locking requirements.

Choose the behavior deliberately

• UPDLOCK requests update locks for reads and holds them to transaction completion.
• HOLDLOCK applies serializable semantics to that table reference for the transaction.
• XLOCK requests exclusive locks held to transaction completion.
• NOLOCK is equivalent to read uncommitted for data access. It permits inconsistent results and still takes schema-stability locks.

NOLOCK isn’t the default isolation level. It doesn’t provide a supported escape from UPDATE or DELETE target-table locking. SQL Server ignores those target-table hints.

A bounded syntax example

CREATE TABLE #Orders (OrderID int PRIMARY KEY, CustomerID int NOT NULL);
INSERT INTO #Orders VALUES (1,10),(2,20);
BEGIN TRANSACTION;
SELECT OrderID, CustomerID
FROM #Orders WITH (UPDLOCK, HOLDLOCK)
WHERE OrderID = 1;
ROLLBACK TRANSACTION;
DROP TABLE #Orders;

This temporary-table example demonstrates syntax and cleanup. One session can’t prove a concurrency or blocking claim. A real concurrency demonstration requires a second session and an explicit transaction timeline.

Reference: SQL Server table hints.

Related reading

• SQL in Sixty Seconds
• Copy Database – SQL in Sixty Seconds #169
• 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
• Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
• 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
• Get Memory Details – SQL in Sixty Seconds #165
• Get CPU Details – SQL in Sixty Seconds #164
• Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
• SQL Server on Linux – SQL in Sixty Seconds 162
• Query Ignoring CPU Threads – SQL in Sixty Seconds 161
• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

A locking hint is not a concurrency guarantee, it is a request with specific transaction semantics.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.