Database Reference
In-Depth Information
The output from
sys.dm_tran_locks
in Connection 3 will provide the lock status after the first step of the
UPDATE
statement since the lock conversion to an exclusive (X) lock by the
UPDATE
statement is blocked by the
SELECT
statement.
The lock status after the second step of the
UPDATE
statement will be provided by rerunning
the query against
sys.dm_tran_locks
in Connection 3.
6.
Next, let's look at the lock status provided by
sys.dm_tran_locks
as you go through the individual steps of the
UPDATE
statement.
20-3
shows the lock status after step 1 of the
UPDATE
statement (obtained from the
output from
sys.dm_tran_locks
executed on the third connection, Connection 3, as
explained previously).
•
Figure
Figure 20-3.
Output from sys.dm_tran_locks showing the lock conversion state of an UPDATE statement
■
the order of these rows is not that important. i've ordered by
session_id
in order to group the locks
from each query.
Note
20-4
shows the lock status after step 2 of the
UPDATE
statement.
•
Figure
Figure 20-4.
Output from sys.dm_tran_locks showing the final lock status held by the UPDATE statement