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.

Advertisements
  1. #1 by Hugo Kornelis on September 17, 2012 - 5:52 pm

    You write: “This seems to make sense from a resource usage point of key (minimises the number of key level locks)” – but to me, it doesn’t. Yes, this saves one lock taken when a row is read. But at a terrible expense – because now, when an update runs, SQL Server has to check all other running connections to see if they would like to take an “S” key lock on a row that they already read. That can take a lot of time, and it might introduce problems if that lock cannot be granted at this time. Also, when the reading transaction does not acquire an S key lock, how do you prevent it from reading and returning a row that is exclusively locked by another connection?

    Fortunately, your conclusions are incorrect. SQL Server will take an S key lock when reading a row through the clustered primary key. I checked this using a different way to test. I took your table, but modified the “select” code – I wanted to use repeatable read, so that S locks are held longer, and I didn’t use the loop. In the first window, I executed:

    SELECT @@SPID;
    go
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    BEGIN TRAN;
    DECLARE @val VARCHAR(50);
    SELECT @val = dataval FROM dbo.mydata WHERE id = 96;
    — COMMIT TRAN;
    go

    The first select (for @@spid) returned 57 on my system, so in my second window I executed this:

    SELECT resource_type, resource_description, request_mode, request_type
    FROM sys.dm_tran_locks
    WHERE request_session_id = 57;

    The results showed, as expected, that there were IS locks on the object and page level, and an S lock at the key level.

    But now I wanted to verify that the same three locks are taken when using the same transaction isolation level you use in your post, read committed. So I rolled back the transaction in my first window, then went back to your original code (reading in a loop), but ripped out the WAITFOR – because locks are held so brieftly, the only way to have a chance to see them is to make sure they are taken and released in a very tight loop, with as many executions per second as possible. So this is the code I now used:

    SELECT @@SPID;
    go
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    DECLARE @val VARCHAR(50);

    WHILE 1=1
    BEGIN;
    SELECT @val = dataval FROM dbo.mydata WHERE id = 96;
    END;
    go

    I went back to my second window and hit execute a couple of dozen times, each time checking the results. Most of the time I had no results; the second most frequent result was only the object level lock. However, when my timing was lucky I also sometimes got to see the page lock, and a very precious few times, I did see the S lock on key level. (And then I also sometimes saw two of them simultaneously).
    This clearly shows that the key lock *is* taken. I guess this also shows that this lock lives so short that, even in this setting, it’s very hard to catch. But it is taken.

    Which of course leads to a completely different question – how is it possible that both the extended events architecture and the trace archtecture do not pick up on these locks? I have no idea how to answer that question!

    • #2 by sqlscope on September 17, 2012 - 7:29 pm

      Thanks, Hugo, for your very detailed reply. In my first run I found the same results as you with REPEATABLE READ. That is, S key locks are acquired.

      For the READ COMMITTED case the WAITFOR DELAY was present to avoid flooding my trace with lock events. I have re-run the READ COMMITTED test, this time removing the WAITFOR DELAY to make my WHILE loop identical to yours. In another connection rather than running the select from sys.dm_tran_locks manually I have run this loop:

      SET NOCOUNT ON

      DECLARE @resource_type NVARCHAR(60)
      DECLARE @request_mode NVARCHAR(60)
      DECLARE @resource_description NVARCHAR(256)
      DECLARE @request_type NVARCHAR(60)

      WHILE 1=1
      BEGIN

      SELECT
      @resource_type = resource_type,
      @resource_description = resource_description,
      @request_mode = request_mode,
      @request_type = request_type
      FROM sys.dm_tran_locks (NOLOCK)
      WHERE request_session_id = 52
      AND resource_type = N’KEY’

      IF @resource_type = N’KEY’ PRINT @resource_description

      END

      In my case the SPID I was monitoring was 52. I left the both connections running for over five minutes. For the sys.dm_tran_locks connection the PRINT statement was never executed ie no S key locks, in fact, no key locks at all for the connection that was running the select from dbo.mydata.

      I changed the isolation level to REPEATABLE READ and re-ran the test and immediately saw resource descriptions being printed from the monitoring connection. In addition, I was running performance monitor capturing lock requests/sec for KEYs. For the REPEATABLE READ case I immediately saw the metric go through the roof. For the READ COMMITTED case I saw no change.

      • #3 by Hugo Kornelis on September 17, 2012 - 9:09 pm

        That is very strange. I copied and pasted the code from your comment and did the same test (using the code from your blog post in one window, and the code from your comment in a second). At first, I got no results. But after changing 52 to 53 in the dm_tran_locks query, I did get results – more than a hundred lines in about 5 seconds (and then I cancelled the transactions).

        I am running on SQL Server 2012 Developer Edition, build 11.0.2100.60 (X64 version). What version of SQL Server are you testing this on?

      • #4 by sqlscope on September 18, 2012 - 4:54 pm

        Hugo, I’m running my tests on Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (X64). After your

        INSERT INTO dbo.mydata (dataval)
        SELECT REPLICATE(‘This is a test’,6)
        GO 100

        can you run CHECKPOINT and then run the test. I have been able to reproduce your output when I run the select statement in the same connection window immediately after the population of the dbo.mydata table. If I execute the select in a different connection or in the same connection but with a checkpoint before the select then I see the effect described in the blog. The comments in Paul’s blog make reference to this as well.

      • #5 by Hugo Kornelis on September 19, 2012 - 9:15 am

        Thanks for checking that. After reading Paul’s blog post, I wanted to go back to my own test and try if I can replicate your original observations after adding an explicit CHECKPOINT – but I simply lacked (and still lack) the time. So I am glad you did that test for me; that is the last bit of information I needed to be able to explain all the behaviour I have seen.

  2. #6 by Paul White on September 18, 2012 - 1:59 am

    I haven’t read the whole post in detail, but it seems to be the same optimization I wrote about here: http://bit.ly/MissingSharedLocks

    • #7 by sqlscope on September 18, 2012 - 6:45 am

      Thanks, Paul, it’s the exact same optimisation. Great detail in your blog post. I should have known better and checked anything posted by you for an explanation for my observations !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: