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

本文出自 “SQL Server Deep Dive” 博客，请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1845269

WITH (UPDLOCK,HOLDLOCK)提示与不同表类型