Database Reference
In-Depth Information
■
Note
deadlocks are covered in more detail in Chapter 21.
To avoid this typical deadlock, the
UPDATE
statement uses a (U) lock instead of an (S) lock at its first intermediate
step. Unlike an (S) lock, a (U) lock doesn't allow another (U) lock on the same resource simultaneously. This forces the
second concurrent
UPDATE
statement to wait until the first
UPDATE
statement completes.
Exclusive (X) Mode
Exclusive mode provides an exclusive right on a database resource for modification by data manipulation queries
such as
INSERT
,
UPDATE
, and
DELETE
. It prevents other concurrent transactions from accessing the resource under
modification. Both the
INSERT
and
DELETE
statements acquire (X) locks at the very beginning of their execution. As
explained earlier, the
UPDATE
statement converts to the (X) lock after the data to be modified is read. The (X) locks
granted in a transaction are held until the end of the transaction.
The (X) lock serves two purposes.
•
It prevents other transactions from accessing the resource under modification so that they see
a value either before or after the modification, not a value undergoing modification.
•
It allows the transaction modifying the resource to safely roll back to the original value
before modification, if needed, since no other transaction is allowed to modify the resource
simultaneously.
Intent Shared (IS), Intent Exclusive (IX and Shared with Intent Exclusive
(SIX) Modes
Intent Shared, Intent Exclusive, and Shared with Intent Exclusive locks indicate that the query intends to grab a
corresponding (S) or (X) lock at a lower lock level. For example, consider the following transaction on the
Sales.Currency
table:
BEGIN TRAN
DELETE Sales.Currency
WHERE CurrencyCode = 'ALL';
SELECT tl.request_session_id,
tl.resource_database_id,
tl.resource_associated_entity_id,
tl.resource_type,
tl.resource_description,
tl.request_mode,
tl.request_status
FROM sys.dm_tran_locks tl;
ROLLBACK TRAN
Figure
20-5
shows the output from
sys.dm_tran_locks
.
