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.
Search WWH ::




Custom Search