Sql-server – Data locking question

blockinglockingsql servertransaction

I'm learning locking basics and I have some doubts about how it works.
I'm using following query to examine transaction locks :

SELECT request_session_id AS spid ,
DB_NAME(resource_database_id) AS dbname ,
CASE WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE OBJECT_NAME(p.object_id)
END AS entity_name ,
index_id ,
resource_type AS resource ,
resource_description AS description ,
request_mode AS mode ,
request_status AS status
FROM sys.dm_tran_locks t
LEFT JOIN sys.partitions p
ON p.partition_id = t.resource_associated_entity_id
WHERE resource_database_id = DB_ID()
AND resource_type <> 'DATABASE' ;

Here i'm opening transaction and running some basic select query :

USE AdventureWorks ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRAN
SELECT *
FROM Production.Product
WHERE Name LIKE 'Racing Socks%' ;

I can see following output :enter image description here

As you can see there exists key locks on clustered and non-clustered indexes

Now i'm trying to create another handmade table 

CREATE TABLE [IsolationTest2Test]
(
col1 INT PRIMARY KEY ,
col2 VARCHAR(20)
) ;
GO
INSERT INTO IsolationTest
VALUES ( 10, 'The first row' ) ;
INSERT INTO IsolationTest
VALUES ( 20, 'The second row' ) ;
INSERT INTO IsolationTest
VALUES ( 30, 'The third row' ) ;
INSERT INTO IsolationTest
VALUES ( 40, 'The fourth row' ) ;
INSERT INTO IsolationTest
VALUES ( 50, 'The fifth row' ) ;
GO

So now there exists clustered index with primary key created and additioanly i added non-clustered index on column2

After all of it i'm running following query :

USE [IsolationDB] ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN TRAN
SELECT *
FROM [dbo].[IsolationTest2Test]
WHERE col2 = 'The third row' ;

Then I examine locking and seeing following:

enter image description here

Question is why there is no key lock on clustred index in second query ? (Select from [IsolationTest2Test] )

Query plan of first statement :

enter image description here

Query plan of second query :

enter image description here

Now i can inderstand that first query that select all columns from the table acquires S lock on non-clustered index key value and also it acqiures S lock on clustered index key that has data on the leaf level.

Why second query do not use clustered index key col 1 to perform key lookup? Why it only use non-clustered index seek? Where it takes value for col1?

Best Answer

Question is why there is no key lock on clustered index in second query ?

Because the only other column in that table is the clustered index key. And a non-clustered index always contains the clustered index key columns.

So your non-clustered index on col2 is a covering index for the SELECT * query.

Related Question