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
