Database Reference
In-Depth Information
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;