Archive for category Locking
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.
Disregarding any of the higher isolation levels and using the out-of-box read committed isolation level you’d expect an exclusive lock to block a share lock. Here’s a case, admittedly not that common, where blocking does not occur.
-- Create a demo table CREATE TABLE dbo.t (c int NOT NULL); INSERT INTO dbo.t VALUES (1); -- In one session run this BEGIN TRAN; UPDATE dbo.t SET c = c; -- Examine the locks. You'll see the RID X lock SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID -- In another session run this SELECT c FROM dbo.t -- The SELECT is not blocked
This is a contrived example. The column value is being updated to itself but this may happen, for example, in an ETL load where your transaction begins, the daily update value doesn’t change, an update trigger on the table fires that updates other tables. In the transaction time frame you rely on SQL Server’s locking mechanisms to prevent other users reading data while transactions involving it are taking place. As the example shows, for this data scenario your users won’t be blocked.
The real world cases aside, there’s also a SQL Server inconsistency. If the table definition is changed so that a clustered primary key is placed on the column then blocking does occur. You’ll see a granted exclusive key lock from the updating spid and a waiting share key lock from the selecting spid. So the behaviour is dependent on whether the table is a heap or a btree.
Realistically speaking there’s little chance of this being addressed. Nevertheless, the following CONNECT item has been raised:
UPDATE: Martin Smith kindly replied to the CONNECT item. He cited a couple of posts by Paul White that explain the behaviour described in this post:
Using fn_dblog for the two scenarios mentioned here show no log entries for the heap case compared to a delete and an insert operation for the btree case. This explains the blocking for the btree case.