WITH (UPDLOCK,HOLDLOCK)提示與不同表類型

WITH (UPDLOCK,HOLDLOCK)提示與不同表類型

我們先來了解下UPDLOCK和HOLDLOCK的概念。

UPDLOCK

指定采用更新鎖并保持到事務完成。 UPDLOCK 僅對行級別或頁級別的讀操作采用更新鎖。 如果將 UPDLOCK 與 TABLOCK 組合使用或出于一些其他原因采用表級鎖，將采用排他 (X) 鎖。

HOLDLOCK

等價于SERIALIZABLE。保持共享鎖直到事務完成，使共享鎖更具有限制性；而不是無論事務是否完成，都在不再需要所需表或數據頁時立即釋放共享鎖。并且至少整個查詢覆蓋的范圍會被鎖定，以阻止導致幻象讀的插入。

一個U鎖是與其他的S鎖兼容的，但是與其他的U鎖不兼容。（查看鎖兼容性）。因此，如果鎖在行級別或者頁級別采用，這將不會阻塞其他讀操作，除非他們也使用UPDLOCK提示。

首先，創建一個堆表，插入一些測試數據：

CREATE FUNCTION dbo.RANDBETWEEN(@minval TINYINT, @maxval TINYINT, @random NUMERIC(18,10))
RETURNS TINYINT
AS
BEGIN
RETURN (SELECT CAST(((@maxval + 1) - @minval) * @random + @minval AS TINYINT))
END
GO
-- Create Person Table
CREATE TABLE Person(ID int NOT NULL IDENTITY,FirstName varchar(32) NULL,LastName varchar(32) NULL,CityId int NULL);
GO
-- Insert 1 million records into the Person table
INSERT INTO Person (FirstName,LastName,CityId)
SELECT TOP 1000000
CASE
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 0 THEN 'John'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 1 THEN 'Jack'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 2 THEN 'Bill'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 3 THEN 'Mary'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 4 THEN 'Kate'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 5 THEN 'Matt'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 6 THEN 'Rachel'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 7 THEN 'Tom'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 8 THEN 'Ann'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 9 THEN 'Andrew'
ELSE 'Bob' END AS FirstName,
CASE
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 0 THEN 'Smith'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 1 THEN 'Morgan'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 2 THEN 'Simpson'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 3 THEN 'Walker'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 4 THEN 'Bauer'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 5 THEN 'Taylor'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 6 THEN 'Morris'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 7 THEN 'Elliot'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 8 THEN 'White'
WHEN dbo.RANDBETWEEN(0,9,RAND(CHECKSUM(NEWID()))) = 9 THEN 'Davis'
ELSE 'Brown' END AS LastName,
dbo.RANDBETWEEN(1,15,RAND(CHECKSUM(NEWID()))) as CityId
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
SELECT * FROM Person;

堆表

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (UPDLOCK, HOLDLOCK) WHERE ID = 1;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

非聚集索引表

在堆表的ID列創建非聚集索引：

CREATE NONCLUSTERED INDEX IX_Person_ID ON dbo.Person (ID);

場景1：

使用WITH (HOLDLOCK)而沒有WHERE從句，來觀察鎖升級。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (HOLDLOCK);
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

場景2：

使用WITH(HOLDLOCK)和WHERE從句，從ID列索引查找。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (HOLDLOCK) WHERE ID = 1;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

場景3：

使用WITH (UPDLOCK, HOLDLOCK)和WHERE從句，從ID列索引查找。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (INDEX (0), UPDLOCK, HOLDLOCK) WHERE ID = 1;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

場景4：

使用WITH (INDEX (0), UPDLOCK, HOLDLOCK)，強制表掃描。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (INDEX (0), UPDLOCK, HOLDLOCK) WHERE ID = 1;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

聚集索引表

刪除掉非聚集索引，并創建ID列的聚集索引：

DROP INDEX Person.IX_Person_ID
GO
ALTER TABLE dbo.Person
ADD CONSTRAINT PK_Person
PRIMARY KEY CLUSTERED (ID)
GO

場景1：

使用WIH (HOLDLOCK)而無WHERE條件。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (HOLDLOCK);
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

場景2：

使用WITH (UPDLOCK, HOLDLOCK)而無WHERE條件。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (UPDLOCK, HOLDLOCK);
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

場景3：

使用WITH (UPDLOCK, HOLDLOCK)和WHERE條件，走ID列聚集索引查找。

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (UPDLOCK, HOLDLOCK) WHERE ID = 1;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

接著，在CityId列建立非聚集索引：

CREATE INDEX IX_Person_CityId ON Person(CityId);

查看CityId的數據分布情況：

SELECT CityId,COUNT(*) AS CNT
FROM dbo.Person
GROUP BY CityId
ORDER BY 2 DESC

場景4：

查詢CityId為1

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (UPDLOCK, HOLDLOCK) WHERE CityId=1;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

插入一個可選擇性更強的CityId值：

INSERT Person(FirstName,LastName,CityId)
SELECT 'ryan','xu',99
UNION ALL
SELECT 'koko','xu',99
UNION ALL
SELECT 'jerry','xu',100
GO

場景5：

查詢CityId為99

BEGIN TRANSACTION
SELECT * FROM dbo.Person WITH (UPDLOCK, HOLDLOCK) WHERE CityId=99;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

接著，刪除CityId列索引，創建該列包含索引。

DROP INDEX Person.IX_Person_CityId;
GO
CREATE INDEX IX_Person_CityId
ON Person(CityId)
INCLUDE(FirstName);
GO

場景6：

同樣查詢CityID為99，單輸出列在包含索引中，完全走非聚集索引的查找。（主鍵列默認包含在非聚集索引中）

BEGIN TRANSACTION
SELECT ID,FirstName FROM dbo.Person WITH (UPDLOCK, HOLDLOCK) WHERE CityId=99;
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],
OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
--,p.*
FROM sys.[dm_tran_locks] AS a
LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c
ON a.[request_session_id]=c.[spid]
WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPID
ORDER BY [request_session_id],[resource_type];
COMMIT TRANSACTION

總結

對于查詢：

SELECT * FROM tblTest WITH (UPDLOCK, HOLDLOCK)

如果查詢計劃顯示了一個堆表上的掃描，那么你總是獲得一個對象上的X鎖。如果是一個索引掃描，它依賴于使用的鎖粒度。（單個 Transact-SQL 語句在單個無分區表或索引上獲得至少 5,000 個鎖，將觸發鎖升級）

對于非聚集索引表，HOLDLOCK在(ffffffffffff)上采用了RangeS-S鎖，UPDLOCK, HOLDLOCK采用了 RangeS-U鎖。兩個查詢都通過ID列執行了索引查找。當我使用WITH (INDEX (0), UPDLOCK, HOLDLOCK)強制執行計劃執行表掃描時，看到對象上采用X鎖。如果索引可以用于在執行計劃中識別范圍查詢，將使用鍵范圍鎖。

對于聚集索引表，當WHERE條件走聚集索引查找，UPDLOCK, HOLDLOCK采用了KEY上的U鎖。只有純粹只走非聚集索引查找時，才用了KEY上的Ranges-U鎖。

因為你使用了HOLDLOCK，它阻止了幻象讀。如果你的查詢讀取了整個表，那么阻止了范圍的幻象讀，意思是它不允許任何行被插入。為了獲得一個鍵范圍鎖你的查詢需要合適的索引和WHERE從句。

參考

表提示

https://msdn.microsoft.com/zh-cn/library/ms187373.aspx

鎖升級

https://msdn.microsoft.com/zh-cn/library/ms184286(v=sql.105).aspx

How to resolve blocking problems that are caused by lock escalation in SQL Server

https://support.microsoft.com/en-us/kb/323630

鍵范圍鎖定

https://technet.microsoft.com/zh-cn/library/ms191272(en-us,SQL.110).aspx

SQL Server 的事務和鎖（二）-Range S-S鎖

http://www.cnblogs.com/lxconan/archive/2011/10/21/sql_transaction_n_locks_2.html