Why does UPDLOCK cause SELECTs to hang (lock)?

Question

I've got a select in SQL SERVER that locks the whole table.

Here's the setup script (make sure you don't overwrite anything)

USE [master]
GO

IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = 'LockingTestDB')
DROP DATABASE LockingTestDB
GO

CREATE DATABASE LockingTestDB
GO

USE [LockingTestDB]
GO
IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'LockingTestTable')
  DROP TABLE LockingTestTable
GO

CREATE TABLE LockingTestTable (
  Id int IDENTITY(1, 1),
  Name varchar(100),
  PRIMARY KEY CLUSTERED (Id)
)
GO

INSERT INTO LockingTestTable(Name) VALUES ('1')
INSERT INTO LockingTestTable(Name) VALUES ('2')
GO

Open a new query window and run the following transaction (which has a wait in it):

USE [LockingTestDB]
GO

BEGIN TRANSACTION
  SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '1'
  WAITFOR DELAY '00:01:00'

COMMIT TRANSACTION
--ROLLBACK
GO

USE [master]
GO

And another one that will run (make sure they run at the same time):

USE [LockingTestDB]
GO

SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '2'

USE [master]
GO

You'll notice the second query will be blocked by the first one. Stop the first query and execute the ROLLBACK and the second will complete.

Why is this happening?

PS: Adding a non-clustered index (with full coverage) over Name will fix it:

USE [LockingTestDB]
GO

CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[LockingTestTable] 
(
  [Name] ASC
)
INCLUDE ( [Id]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Again why?

Paul White · Accepted Answer · 2022-02-04T10:59:02.420

As documented in Books Online, UPDLOCK takes update locks and holds them to the end of the transaction.

Without an index to locate the row(s) to be locked, all tested rows are locked, and locks on qualifying rows are held until the transaction completes.

The first transaction holds an update lock on the row where name = 1. The second transaction is blocked when it attempts to acquire an update lock on the same row (to test if name = 2 for that row).

Note: SQL Server always takes an update lock. If the row does not match the predicate(s) in the query, the lock may be released just before testing the next row.

With an index, SQL Server can quickly locate and lock only those rows that qualify, so there is no conflict.

You should review the code with a qualified database professional to validate the reason for the locking hint, and to ensure appropriate indexes are present.

Related information: Data Modifications under Read Committed Snapshot Isolation

Why does UPDLOCK cause SELECTs to hang (lock)?

1 Answers1

Linked