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

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

非聚集索引表

在堆表的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

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

场景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

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

场景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

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

场景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

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

聚集索引表

删除掉非聚集索引，并创建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

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

场景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

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

场景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

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

接着，在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

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

场景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

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

插入一个可选择性更强的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

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

接着，删除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

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

总结

对于查询：

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

分享文章：WITH(UPDLOCK,HOLDLOCK)提示与不同表类型
地址分享：http://chengdu.cdxwcx.cn/article/pjjjhd.html

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

首页

网站建设

网站制作案例

解决方案

网站设计报价

网站制作动态

关于我们

联系我们

成都网站建设设计将想法与焦点和您一起共享

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

其他资讯

C语言函数图库 c语言绘图函数库

c语言cmm函数是什么 c语言memcmp函数

c语言sin函数、 c语言sin函数参数为弧度还是角度

电脑c语言延迟函数电脑c语言延迟函数怎么弄

pow函数c语言格式 pow函数 c语言

甜橘子，专注成都网站制作网站设计与营销型网站建设与优化

成都网站建设设计 将想法与焦点和您一起共享

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

其他资讯

C语言函数图库 c语言绘图函数库

c语言cmm函数是什么 c语言memcmp函数

c语言sin函数、 c语言sin函数参数为弧度还是角度

电脑c语言延迟函数 电脑c语言延迟函数怎么弄

pow函数c语言格式 pow函数 c语言

成都网站建设设计将想法与焦点和您一起共享

电脑c语言延迟函数电脑c语言延迟函数怎么弄