Are key and RID level locks always acquired ?

Faced with the task of identifying the rows that are most frequently selected from a table, one possible solution was to use the lock_acquired extended event. Using predicates based on the event columns it would be possible to filter on the database id, the lock mode (3 = Share) and the lock resource type (7 = KEY). With the extended event in place it was a surprise to find that a simple select with a where clause specifying the clustered primary key value (an identity column) resulted in no events being captured. Removing the predicates for the lock mode and lock resource type resulted in the appearance of an intent share lock for the object and an intent share lock for the data page.

To check that this wasn’t an issue for extended events a sql trace on the lock event was set up and the same select scenario captured. Again, the intent share locks for the object and data page level showed up but no key level lock.

It was only until another concurrent process was run which updated other rows on the same data page did the share key lock appear for the process that was selecting from the table.

The conclusion is that for the default read committed transaction isolation level the finest grained locks at the data level are only requested if the same data page is at the same time being updated by another process. If a data page is not being updated it appears that the finest grained locks are not requested. This seems to make sense from a resource usage point of key (minimises the number of key level locks).

Here are the scripts demonstrating the observations:

SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.mydata') IS NOT NULL DROP TABLE dbo.mydata
GO
CREATE TABLE dbo.mydata
(
id int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
dataval CHAR(100) NOT NULL
)
GO
INSERT INTO dbo.mydata (dataval)
SELECT REPLICATE('This is a test',6)
GO 100

In one session run the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @val VARCHAR(50)

WHILE 1=1
BEGIN
SELECT @val = dataval FROM dbo.mydata WHERE id = 96
WAITFOR DELAY '00:00:02'
END

Now capture just this session’s lock events in a trace. Here is the trace event and relevant columns:

The lock escalation event has been included but no output for this event will show up.

The steady state results for the trace should be as follows:

The following was running to match up the object id, hobt id and page id from the trace output above:

SELECT * FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.mydata')

DBCC IND ('locktest', 'dbo.mydata', 1)

DBCC TRACEON(3604)
DBCC PAGE('locktest',1,94,3) WITH TABLERESULTS

The results showed the same ids as those in the trace:

On page 94 the minimum value for id is 72. If the following update is run concurrently with the select statement then the trace results are identical to those above (ie we see no share key lock):

SET NOCOUNT ON
DECLARE @i INT = 0
WHILE 1=1
BEGIN
SET @i = @i + 1
UPDATE dbo.mydata SET dataval = 'my text' + CONVERT(VARCHAR(1000),@i) WHERE id = 71
WAITFOR DELAY '00:00:02'
END

But if you change the 71 to 72 the share key locks start to appear:

You can see the share key lock for resource (6000e0d79de5). This matches the KeyHashValue for id 96 from the earlier DBCC PAGE output. Notice that even in this scenario because of the timing of events share key locks are not always acquired (see 10:47:36, 10:47:42, 10:47:48, 10:47:54)

The same scenarios were run for the primary key declared as nonclustered. For this case with just the select running for id 96 a share key lock was observed for index 2 but no share RID lock was observed. Just as for the clustered primary key case, the share RID lock appeared when the other concurrent process was updating another row on the same data page.

For completeness here is the extended event session for the lock_acquired event:

CREATE EVENT SESSION LockTestShareKeyLocks
ON SERVER
ADD EVENT sqlserver.lock_acquired
(
WHERE resource_type = 7
AND mode = 3
AND database_id = 19
)
ADD TARGET package0.ring_buffer

ALTER EVENT SESSION LockTestShareKeyLocks
ON SERVER
STATE=START
GO

The extended event target query was:

SELECT
n.value('(event/@name)[1]', 'varchar(50)') AS event_name,
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(event/data[@name="database_id"]/value)[1]', 'int') AS [database_id],
n.value('(event/data[@name="resource_type"]/value)[1]', 'int') AS [resource_type],
n.value('(event/data[@name="mode"]/value)[1]', 'int') AS [mode],
n.value('(event/data[@name="resource_0"]/value)[1]', 'bigint') AS [resource_0],
n.value('(event/data[@name="resource_1"]/value)[1]', 'bigint') AS [resource_1],
n.value('(event/data[@name="resource_2"]/value)[1]', 'bigint') AS [resource_2]
FROM
(
SELECT td.query('.') as n
FROM
(
SELECT CAST(target_data AS XML) as target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON s.address = t.event_session_address
WHERE s.name = 'LockTestShareKeyLocks'
AND t.target_name = 'ring_buffer'
) AS sub
CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as tab
ORDER BY
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
n.value('(event/@timestamp)[1]', 'datetime2'))
GO

When the concurrent update was run the output was as follows:

Courtesy of John Huang (http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588), the resource_0, resource_1, resource_2 can be converted to the lock resource:

SELECT dbo.[ConvertedLockResource]('KEY',25,6291712,3852326880)

The result, (6000e0d79de5)/72057594039566336, matches the SQL trace details.