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 TINYINTASBEGINRETURN (SELECT CAST(((@maxval + 1) - @minval) * @random + @minval AS TINYINT))ENDGO-- Create Person TableCREATE 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 tableINSERT INTO Person (FirstName,LastName,CityId)SELECT TOP 1000000CASEWHEN 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,CASEWHEN 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 CityIdFROM sys.all_objects aCROSS JOIN sys.all_objects bGOSELECT * FROM Person;

堆表

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

非聚集索引表

在堆表的ID列创建非聚集索引：

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

场景1：

使用WITH (HOLDLOCK)而没有WHERE从句，来观察锁升级。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

场景2：

使用WITH(HOLDLOCK)和WHERE从句，从ID列索引查找。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

场景3：

使用WITH (UPDLOCK, HOLDLOCK)和WHERE从句，从ID列索引查找。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

场景4：

使用WITH (INDEX (0), UPDLOCK, HOLDLOCK)，强制表扫描。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

聚集索引表

删除掉非聚集索引，并创建ID列的聚集索引：

DROP INDEX Person.IX_Person_IDGOALTER TABLE dbo.PersonADD CONSTRAINT PK_PersonPRIMARY KEY CLUSTERED (ID)GO

场景1：

使用WIH (HOLDLOCK)而无WHERE条件。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

场景2：

使用WITH (UPDLOCK, HOLDLOCK)而无WHERE条件。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

场景3：

使用WITH (UPDLOCK, HOLDLOCK)和WHERE条件，走ID列聚集索引查找。

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

接着，在CityId列建立非聚集索引：

CREATE INDEX IX_Person_CityId ON Person(CityId);

查看CityId的数据分布情况：

SELECT CityId,COUNT(*) AS CNTFROM dbo.PersonGROUP BY CityIdORDER BY 2 DESC

场景4：

查询CityId为1

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

插入一个可选择性更强的CityId值：

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

场景5：

查询CityId为99

BEGIN TRANSACTIONSELECT * 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER BY [request_session_id],[resource_type];COMMIT TRANSACTION

接着，删除CityId列索引，创建该列包含索引。

DROP INDEX Person.IX_Person_CityId;GOCREATE INDEX IX_Person_CityIdON Person(CityId)INCLUDE(FirstName);GO

场景6：

同样查询CityID为99，单输出列在包含索引中，完全走非聚集索引的查找。（主键列默认包含在非聚集索引中）

BEGIN TRANSACTIONSELECT 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 aLEFT JOIN sys.[partitions] AS pON a.[resource_associated_entity_id]=p.[hobt_id]LEFT JOIN sys.[sysprocesses] AS cON a.[request_session_id]=c.[spid]WHERE c.[dbid]=DB_ID(DB_NAME()) AND a.[request_session_id]=@@SPIDORDER 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