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

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

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

UPDLOCK

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

HOLDLOCK

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

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

首先，創(chuàng)建一個(gè)堆表，插入一些測(cè)試數(shù)據(jù)：

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列創(chuàng)建非聚集索引：

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

場(chǎng)景1：

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

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

場(chǎng)景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

場(chǎng)景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

場(chǎng)景4：

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

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

聚集索引表

刪除掉非聚集索引，并創(chuàng)建ID列的聚集索引：

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

場(chǎng)景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

場(chǎng)景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

場(chǎng)景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的數(shù)據(jù)分布情況：

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

場(chǎng)景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

插入一個(gè)可選擇性更強(qiáng)的CityId值：

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

場(chǎng)景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列索引，創(chuàng)建該列包含索引。

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

場(chǎng)景6：

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

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

總結(jié)

對(duì)于查詢：

SELECT * FROM tblTest WITH (UPDLOCK, HOLDLOCK)

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

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

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

因?yàn)槟闶褂昧薍OLDLOCK，它阻止了幻象讀。如果你的查詢讀取了整個(gè)表，那么阻止了范圍的幻象讀，意思是它不允許任何行被插入。為了獲得一個(gè)鍵范圍鎖你的查詢需要合適的索引和WHERE從句。

參考

表提示

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

鎖升級(jí)

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 的事務(wù)和鎖（二）-Range S-S鎖

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