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%' ;
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:
Question is why there is no key lock on clustred index in second query ? (Select from [IsolationTest2Test] )
Query plan of first statement :
Query plan of second query :
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?