资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型

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)提示与不同表类型
URL链接：http://cdkjz.cn/article/pjjjhd.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
联通机柜租用晟和广告木包装箱重庆主机托管犀浦雨棚定制成都展柜制作成都橡塑保温管路面机械及配件简阳网站建设成都手机网站建设

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图