Database Reference
In-Depth Information
On running the
LockBehavior
transaction again and querying
sys.dm_ tran_locks
from a separate connection,
you get the result shown in Figure
20-10
.
Figure 20-10.
Output from sys.dm_tran_locks showing the effect of a nonclustered index on locking behavior
The following locks are acquired by the transaction:
•
An (IU) lock on the page containing the nonclustered index row
•
A (U) lock on the nonclustered index row within the index page
•
An (IX) lock on the table
•
An (IX) lock on the page containing the data row
•
An (X) lock on the data row within the data page
Note that only the row-level and page-level locks are directly associated with the nonclustered index. The next
higher level of lock granularity for the nonclustered index is the table-level lock on the corresponding table.
Thus, nonclustered indexes introduce an additional locking overhead on the table. You can avoid the locking
overhead on the index by using the
ALLOW_ROW_LOCKS
and
ALLOW_PAGE_LOCKS
options in
ALTER INDEX
. Understand,
though, that this is a trade-off that could involve a loss of performance, and it requires careful testing to ensure it
doesn't negatively impact your system.
ALTER INDEX iTest ON dbo.Test1
SET (ALLOW_ROW_LOCKS = OFF ,ALLOW_PAGE_LOCKS= OFF);
BEGIN TRAN LockBehavior
UPDATE dbo.Test1 WITH (REPEATABLEREAD) --Hold all acquired locks
SET C2 = GETDATE()
WHERE C1 = 1;
--Observe lock behavior using sys.dm_tran_locks
--from another connection
WAITFOR DELAY '00:00:10';
COMMIT
ALTER INDEX iTest ON dbo.Test1
SET (ALLOW_ROW_LOCKS = ON ,ALLOW_PAGE_LOCKS= ON);
You can use these options when working with an index to enable/disable the
KEY
locks and
PAG
locks on the
index. Disabling just the
KEY
lock causes the lowest lock granularity on the index to be the
PAG
lock. Configuring lock
granularity on the index remains effective until it is reconfigured.