Shulou(Shulou.com)06/01 Report--

WITH (UPDLOCK,HOLDLOCK) hint and different table types

Let's first look at the concepts of UPDLOCK and HOLDLOCK.

UPDLOCK

Specifies that the update lock is adopted and held until the transaction is completed. UPDLOCK uses update locks only for row-level or page-level reads. If UPDLOCK is used in combination with TABLOCK or table-level locks are used for some other reason, exclusive (X) locks are used.

HOLDLOCK

Equivalent to SERIALIZABLE. Keep the shared lock until the transaction is complete, making the shared lock more restrictive, rather than releasing the shared lock immediately when the required tables or data pages are no longer needed, regardless of whether the transaction is completed or not. And at least the entire range of query coverage will be locked to prevent insertion that leads to phantom reading.

A U lock is compatible with other S locks, but not with other U locks. (check lock compatibility). Therefore, if locks are adopted at the row or page level, this will not block other reads unless they also use UPDLOCK prompts.

First, create a heap table and insert some test data:

CREATE FUNCTION dbo.RANDBETWEEN (@ minval TINYINT, @ maxval TINYINT, @ random NUMERIC (1810)) 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 (0Jack'WHEN dbo.RANDBETWEEN 9 (CHECKSUM (NEWID () = 0 THEN 'John'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 1 THEN' Jack'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 2 THEN 'Bill'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 3 THEN' Mary'WHEN dbo.RANDBETWEEN (0lad (NEWID () RAND (CHECKSUM (NEWID () = 4 THEN 'Kate'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 5 THEN' Matt'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 6 THEN 'Rachel'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 7 THEN' Tom'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 8 THEN 'Ann'WHEN dbo.RANDBETWEEN (09) RAND (CHECKSUM (NEWID () = 9 THEN 'Andrew'ELSE' Bob' END AS FirstName,CASEWHEN dbo.RANDBETWEEN (0NEWID (NEWID () = 0 THEN 'Smith'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 1 THEN' Morgan'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 2 THEN 'Simpson'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 3 THEN' Walker'WHEN dbo.RANDBETWEEN (09) RAND (CHECKSUM (NEWID () = 4 THEN 'Bauer'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 5 THEN' Taylor'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 6 THEN 'Morris'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 7 THEN' Elliot'WHEN dbo.RANDBETWEEN (CHECKSUM (NEWID () = 8 THEN 'White'WHEN dbo.RANDBETWEEN (09) RAND (CHECKSUM (NEWID () = 9 THEN 'Davis'ELSE' Brown' END AS LastName,dbo.RANDBETWEEN (CHECKSUM (NEWID () as CityIdFROM sys.all_objects aCROSS JOIN sys.all_objects bGOSELECT * FROM Person

Stack table

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

Nonclustered index table

Create a nonclustered index in the ID column of the heap table:

CREATE NONCLUSTERED INDEX IX_Person_ID ON dbo.Person (ID)

Scenario 1:

Use WITH (HOLDLOCK) without a WHERE clause to observe the lock escalation.

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

Scenario 2:

Use the WITH (HOLDLOCK) and WHERE clauses to look from the ID column index.

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

Scenario 3:

Use WITH (UPDLOCK, HOLDLOCK) and WHERE clauses to look from the ID column index.

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

Scenario 4:

Use WITH (INDEX (0), UPDLOCK, HOLDLOCK) to force a table scan.

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

Aggregate index table

Drop the nonclustered index and create a clustered index for the ID column:

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

Scenario 1:

Use WIH (HOLDLOCK) without a WHERE condition.

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

Scenario 2:

Use WITH (UPDLOCK, HOLDLOCK) without a WHERE condition.

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

Scenario 3:

Use the WITH (UPDLOCK, HOLDLOCK) and WHERE conditions to look up the clustered index in the ID column.

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

Next, create a nonclustered index on the CityId column:

CREATE INDEX IX_Person_CityId ON Person (CityId)

View the data distribution of CityId:

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

Scenario 4:

Query CityId is 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

Insert a more selective CityID value:

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

Scenario 5:

Query CityId is 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

Next, delete the CityId column index and create the column containing the index.

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

Scenario 6:

Similarly, the query CityID is 99, and the single output column is in the containing index, completely following the lookup of the nonclustered index. (primary key columns are included by default in nonclustered indexes)

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

Summary

For queries:

SELECT * FROM tblTest WITH (UPDLOCK, HOLDLOCK)

If the query plan shows a scan on a heap table, then you always get an X lock on an object. If it is an index scan, it depends on the lock granularity used. (lock escalation will be triggered if a single Transact-SQL statement acquires at least 5000 locks on a single unpartitioned table or index)

For nonclustered index tables, HOLDLOCK uses RangeS-S locks on (ffffffffffff), UPDLOCK, and HOLDLOCK uses RangeS-U locks. Both queries perform index lookups through the ID column. When I use WITH (INDEX (0), UPDLOCK, HOLDLOCK) to enforce a plan to perform a table scan, I see an X lock on the object. Key range locks are used if the index can be used to identify range queries in the execution plan.

For clustered index tables, when the WHERE condition goes to the clustered index lookup, UPDLOCK, HOLDLOCK uses the U lock on KEY. The Ranges-U lock on KEY is used only if it is purely a nonclustered index lookup.

Because you use HOLDLOCK, it prevents phantom reading. If your query reads the entire table, it prevents the phantom reading of the range, meaning that it does not allow any rows to be inserted. In order to get a key range lock your query needs the appropriate index and WHERE clause.

Referenc

Table hint

Https://msdn.microsoft.com/zh-cn/library/ms187373.aspx

Lock upgrade

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

Key range lock

Https://technet.microsoft.com/zh-cn/library/ms191272(en-us,SQL.110).aspx

SQL Server transaction and Lock (2)-Range Smurs Lock

Http://www.cnblogs.com/lxconan/archive/2011/10/21/sql_transaction_n_locks_2.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.