Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 416

CREATE TABLE dbo.Test1 (C1 INT, C2 DATETIME);

INSERT INTO dbo.Test1
VALUES (1, GETDATE());

Next, observe the locking behavior on the table for the transaction ( --indexlock in the download).

BEGIN TRAN LockBehavior
UPDATE dbo.Test1 WITH (REPEATABLEREAD) --Hold all acquired locks
SET C2 = GETDATE()
WHERE C1 = 1 ;
--Observe lock behavior from another connection
WAITFOR DELAY '00:00:10' ;
COMMIT

Figure 20-9 shows the output of sys.dm_tran_locks applicable to the test table.

Figure 20-9. Output from sys.dm_tran_locks showing the locks granted on a table with no index

The following locks are acquired by the transaction:

• An (IX) lock on the table

• An (IX) lock on the page containing the data row

•
When the resource_type is an object, the resource_associated_entity_id column value in sys.dm_tran_locks
indicates the objectid of the object on which the lock is placed. You can obtain the specific object name on which the
lock is acquired from the sys.object system table, as follows:

An (X) lock on the data row within the table

SELECT OBJECT_NAME(<object_id>);

The effect of the index on the locking behavior of the table varies with the type of index on the WHERE clause
column. The difference arises from the fact that the leaf pages of the nonclustered and clustered indexes have
a different relationship with the data pages of the table. Let's look into the effect of these indexes on the locking
behavior of the table.

Effect of a Nonclustered Index
Because the leaf pages of the nonclustered index are separate from the data pages of the table, the resources
associated with the nonclustered index are also protected from corruption. SQL Server automatically ensures this.
To see this in action, create a nonclustered index on the test table.

CREATE NONCLUSTERED INDEX iTest ON dbo.Test1(C1);