Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 400

To understand the locking behavior of the intermediate steps of the UPDATE statement, you need to obtain
data from sys.dm_tran_locks at the end of each step. You can obtain the lock status after each step of the UPDATE
statement by following the steps outlined next. You're going have three connections open that I'll refer to as
Connection 1, Connection 2, and Connection 3. This will require three different query windows in Management
Studio. You'll run the queries in the connections I list in the order that I specify to arrive at a blocking situation; the
point of this is to observe those blocks as they occur. The initial query, which was listed previously, is in Connection 1:

3. Block the second step of the UPDATE statement by first executing a transaction from a

second connection, Connection 2.

--Execute from a second connection

BEGIN TRANSACTION LockTran2
--Retain an (S) lock on the resource
SELECT *
FROM Sales.Currency AS c WITH (REPEATABLEREAD)
WHERE c.CurrencyCode = 'EUR' ;
--Allow DMVs to be executed before second step of
-- UPDATE statement is executed by transaction LockTran1
WAITFOR DELAY '00:00:10';
COMMIT
The REPEATABLEREAD locking hint, running in Connection 2, allows the SELECT statement to retain the (S) lock on
the resource.

While the transaction LockTran2 is executing, execute the UPDATE transaction, updatelock ,
from the first connection (repeated here for clarity), Connection 1.

4.

BEGIN TRANSACTION LockTran1
UPDATE Sales.Currency
SET Name = 'Euro'
WHERE CurrencyCode = 'EUR';
-- NOTE: We're not committing yet
--COMMIT

While the UPDATE statement is blocked, query the sys.dm_tran_locks DMV from a third
connection, Connection 3, as follows:

5.

SELECT dtl.request_session_id,
dtl.resource_database_id,
dtl.resource_associated_entity_id,
dtl.resource_type,
dtl.resource_description,
dtl.request_mode,
dtl.request_status
FROM sys.dm_tran_locks AS dtl
ORDER BY dtl.request_session_id;