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)', 'varchar(50)') AS event_name, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), n.value('(event/@timestamp)', 'datetime2')) AS [timestamp], n.value('(event/data[@name="database_id"]/value)', 'int') AS [database_id], n.value('(event/data[@name="resource_type"]/value)', 'int') AS [resource_type], n.value('(event/data[@name="mode"]/value)', 'int') AS [mode], n.value('(event/data[@name="resource_0"]/value)', 'bigint') AS [resource_0], n.value('(event/data[@name="resource_1"]/value)', 'bigint') AS [resource_1], n.value('(event/data[@name="resource_2"]/value)', '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)', '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:
The result, (6000e0d79de5)/72057594039566336, matches the SQL trace details.