為什麼 UPDLOCK 會導致 SELECT 掛起（鎖定）？

我在 SQL SERVER 中有一個鎖定整個表的選擇。

這是安裝腳本（確保你沒有覆蓋任何東西）

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

打開一個新的查詢視窗並執行以下事務（其中有一個等待）：

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

另一個將執行（確保它們同時執行）：

USE [LockingTestDB]
GO

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

USE [master]
GO

您會注意到第二個查詢將被第一個查詢阻止。停止第一個查詢並執行 ROLLBACK，第二個將完成。

為什麼會這樣？

PS：在 Name 上添加一個非聚集索引（完全覆蓋）將修復它：

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

又是為什麼？

如Books Online中所述，UPDLOCK獲取更新鎖並將它們保持到事務結束。

如果沒有索引來定位要鎖定的行，所有測試的行都將被鎖定，並且對符合條件的行的鎖定將一直保持到事務完成。

第一個事務在 name = 1 的行上持有更新鎖。第二個事務在嘗試獲取同一行上的更新鎖時被阻塞（以測試該行的 name = 2）。

注意：SQL Server 總是使用更新鎖。如果該行與查詢中的謂詞不匹配，則可以在測試下一行之前釋放鎖。

使用索引，SQL Server 可以快速定位並鎖定那些符合條件的行，因此不會發生衝突。

您應該與合格的數據庫專業人員一起檢查程式碼，以驗證鎖定提示的原因，並確保存在適當的索引。

相關資訊：Read Committed Snapshot Isolation 下的數據修改

引用自：https://dba.stackexchange.com/questions/114572