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 ;
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]
col2 VARCHAR(20)
) ;
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' ) ;

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] ;
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?

  • Can you add the query plans? Not everyone has AW on their servers to test with. pastetheplan.com – Erik Darling Jul 21 '18 at 11:50
  • Please check i have edited question – Alexandr Jul 24 '18 at 23:41

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.

| improve this answer | |
  • Non-clustered index is defined on col2 only , as i know non-clustered index contains duplicate of each value of indexed column in appropriate order and pointner to actual row in clustered index , so why it does not acquire S lock on the clustered index key ? Query needs to get data from col1 where clustered index is defined. – Alexandr Jul 25 '18 at 0:44
  • The "pointer to actual row in clustered index" is the clustered index key. There's no other way to point to a row in a clustered index. – David Browne - Microsoft Jul 25 '18 at 2:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.