Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 380

How to do it...

The following steps are required in designing sustainable locking methods to improve
concurrency in the data platform:

1. We will begin with the HOLDLOCK hint that maintains the shared lock for the duration
of the statement execution.
2. The HOLDLOCK hint is equivalent to the SERIABLABLE isolation level that will be
used within a transaction.

3. Using the Production.Product table, we will obtain the results based on the

condition between the ProductLine and DaysToManufacture columns.

4. Open a New Query window session on the SSMS tool and execute the following TSQL:

--Start a new query window session for this query

USE AdventureWorks2008R2;
GO
BEGIN TRAN
SELECT Name, ProductNumber, ListPrice AS Price,ProductLine,DaysToM
anufacture
FROM Production.Product
WITH (HOLDLOCK)
WHERE ProductLine = 'M'
AND DaysToManufacture between 2 AND 4
ORDER BY Name ASC;
GO

The preceding query returns around 38 rows that are matched as M for the
ProductLine column.

5. Now, let us display the locking information by using the following TSQL:

SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('AdventureWorks2008R2');

The locking information includes the shared range shared resource lock based on the
serializable range scan and GRANT request status.