THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Paul White: Page Free Space

A technical SQL Server blog from New Zealand. See also my articles on SQLperformance.com

The Case of the Missing Shared Locks

In this post I cover a little-known locking optimization that provides a surprising answer to the question:

“If I hold an exclusive lock on a row, can another transaction running at the default read committed isolation level read it?”

Most people would answer ‘no’, on the basis that the read would block when it tried to acquire a shared lock.  Others might respond that it depends on whether the READ_COMMITTED_SNAPSHOT database option was in effect, but let’s assume that is not the case, and we are dealing simply with the default (locking) read committed isolation level.

A Surprising Answer

To investigate, let’s create a test table, and add a single row:

CREATE TABLE
        dbo.Demo
        (
        some_key    INTEGER PRIMARY KEY,
        some_value  INTEGER NOT NULL,
        );
GO
INSERT  INTO
        dbo.Demo (some_key, some_value)
VALUES  (1, 100);
GO
IF      DB_NAME() = N'tempdb'
        CHECKPOINT;

Now start a transaction, acquire an exclusive lock, and show the locks held by our transaction:

BEGIN   TRANSACTION;
 
SELECT  D.some_key,
        D.some_value
FROM    dbo.Demo D WITH (XLOCK);
 
SELECT  L.resource_type,
        L.request_mode,
        L.request_status,
        L.resource_description,
        L.resource_associated_entity_id
FROM    sys.dm_tran_current_transaction T
JOIN    sys.dm_tran_locks L
        ON  L.request_owner_id = T.transaction_id;

As expected, that shows an exclusive (X) key lock on the row, and intent-exclusive (IX) locks at the table and page levels:

image

On a separate connection, let’s try to read the contents of the table:

SELECT  D.some_key,
        D.some_value 
FROM    dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);

Perhaps unexpectedly, this query does not block – the result is returned immediately, despite the exclusive lock.

(Side note: The READCOMMITTEDLOCK table hint is there to ensure that read operations comply with the rules for the READ COMMITTED isolation level by using locking, regardless of the state of the READ_COMMITTED_SNAPSHOT database option).

The Locking Optimization

SQL Server contains an optimization that allows it to avoid taking row-level shared (S) locks in some circumstances.  Specifically, it can skip shared locks if there is no risk of reading uncommitted data without them.

The SELECT query above did not block simply because no shared locks were issued.  We can verify that this is the case by checking the locks acquired using Profiler:

image

There are Intent-Shared (IS) locks at the table and page levels, which are compatible with the Intent-Exclusive locks held by our first query (see Lock Compatibility).  There are no row-level shared locks that would conflict with the existing exclusive lock, so no blocking occurs.

Row-Level Shared Locks Only

The optimization only applies to row-level shared locks, so if we specifically request a different granularity, the query blocks:

SELECT  D.some_key,
        D.some_value 
FROM    dbo.Demo D WITH (PAGLOCK, READCOMMITTEDLOCK);

Looking at the locks, we see that a table-level Intent-Shared (IS) lock was acquired successfully.  An attempt to obtain the requested page-level shared (S) lock blocked because it is incompatible with the existing page-level Intent-Exclusive lock.

image

No Uncommitted Changes

SQL Server cannot apply the locking optimization if there is an uncommitted change on the containing page.  This makes sense because with an uncommitted change on the page, a dirty read would occur if shared locks were not taken.  Dirty reads are not allowed at the read committed isolation level.

SQL Server applies the optimization on a page-by-page basis, so a read may acquire shared row-level locks on some pages (for example those with uncommitted changes) but not on others (where it is safe to skip the locks).

To illustrate, let’s add a second row to our table, using the connection with the still-open transaction:

INSERT  INTO
        dbo.Demo (some_key, some_value)
VALUES  (2, 200);

Now both rows are exclusively locked, and there is an uncommitted change on the page:

image

If we run our SELECT query again, we find that it blocks trying to acquire a shared lock, as expected.  If we add the READPAST hint (to skip locked rows) we find that the query completes immediately, and no rows are returned.

SELECT  D.some_key,
        D.some_value 
FROM    dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK, READPAST);

Both rows on the page are skipped; one because of the explicit XLOCK hint we took earlier, and the other due to the exclusive lock protecting the inserted (but not yet committed) row.  The Profiler trace shows both shared row-level locks timing out:

image

You might be surprised to discover how many of the exclusive locks taken in your databases are not associated with a change in data.  SQL Server takes exclusive locks when processing an UPDATE statement, regardless of whether the data is being changed or not.  For more details, see my previous post on The Impact of Updates that Don't Change Data.

Committed Changes Are Fine

If we commit our transaction, we find that row-level locks can again be skipped:

COMMIT  TRANSACTION;
 
BEGIN   TRANSACTION;
 
SELECT  D.some_key,
        D.some_value
FROM    dbo.Demo D WITH (XLOCK);

Running the SELECT query (on our second connection) completes without taking any shared locks, and both rows are returned:

SELECT  D.some_key,
        D.some_value 
FROM    dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);

Rollbacks Are (Very!) Different

If we run a query that modifies the data on a page and then roll those changes back, SQL Server will not be able to apply the locking optimization on that page, until a later committed change is made to the same page, or the page is written back to permanent storage (for example, by a checkpoint).

Using the connection that still has a transaction open, let’s insert a third row and immediately roll the change back:

INSERT  INTO
        dbo.Demo (some_key, some_value)
VALUES  (3, 300);
 
ROLLBACK TRANSACTION;

We now find that a SELECT on the affected page takes shared locks, even though there are no exclusive locks on that page:

SELECT  D.some_key,
        D.some_value 
FROM    dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);

The Profiler trace shows the row-level shared locks:

image

Now add the third row again, but commit the change:

BEGIN   TRANSACTION;
 
INSERT  INTO
        dbo.Demo (some_key, some_value)
VALUES  (3, 300);
 
COMMIT  TRANSACTION;

Our SELECT query returns all three rows, and takes only Intent-Shared locks at the table and page level:

image

We could also have re-enabled the locking optimization by issuing a manual CHECKPOINT.

Final Thoughts

When reading data, SQL Server decides whether to start off taking locks at the row, page, or table level.  One interesting side-effect of the locking optimization described here is that a query that takes page locks may block where the same query issued with a ROWLOCK hint might not.  (I say ‘might not’ since ROWLOCK is genuinely a hint, rather than an instruction – the engine may or may not respect it.)

For example, a query that issues shared page locks will block if it encounters an Intent-Exclusive (IX) lock but the query that specifies ROWLOCK may only issue Intent-Shared locks at the page level (which are compatible with IX), and might not issue any row-level locks at all.  That’s not to say that you should immediately go out and add ROWLOCK hints to all your SELECT queries – that would probably be a very bad idea – but you should be aware that this optimization exists.

Read Committed (the isolation level) guarantees two things: you will not experience dirty writes or dirty reads.  It does not say anything about how those guarantees should be implemented.  SQL Server happens to use shared locks as part of its implementation of the default isolation level – but you cannot rely on those locks always being present.

Paul White
Email: SQLkiwi@gmail.com
Twitter: @SQL_Kiwi
Published Monday, November 01, 2010 6:19 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Paul

SQL Server 2005 (SP3)

>>>Perhaps unexpectedly, this query does not block – the result is >>>returned immediately, despite the exclusive lock.

Running the below in the second connection IS blocked

SELECT  D.some_key,    

       D.some_value FROM  

dbo.Demo D WITH (ROWLOCK, READCOMMITTEDLOCK);

What I am missing?

November 1, 2010 2:47 AM
 

Paul White said:

Hi Uri,

If you created the Demo table in tempdb, you need to run a manual CHECKPOINT after creating the table and inserting the test row.

I've updated the script to reflect that requirement - thanks for the feedback :)

Paul

November 1, 2010 4:06 AM
 

Uri Dimant said:

Hi Paul

Even running SELECT in the second connection without the hints does not block and we get result immediately

November 1, 2010 6:12 AM
 

Paul White said:

Uri,

Yes, that's right.  The hints aren't required for the optimization to kick in - I added them to make it clear that shared row locks are being taken.  Glad it's working for you now.

Paul

November 1, 2010 6:34 AM
 

Rishabh K said:

Hi Paul,

I tried the same thing on one of my database but as expected the second session was blocked. However it works in tempdb

Use MyDatabase;

GO

CREATE TABLE TEST(ID int PRIMARY KEY,Name varchar(50))

GO

INSERT INTO TEST values (1,'Rishabh')

GO

--Session 1

BEGIN TRAN

select * from test with (XLOCK)

--session 2

select * from test with(rowlock,readcommittedlock)

A little doubt..In connection 1, the data is still in uncommitted mode(not available to others as Read committed ensures).

What I think is even if we specify rowlock in session 2, sql server will anyhow go for the rowlock assuming that there is only a single row ,so it will try to get a shared lock on that row and due to the fact that the row is X locked by the session 1, it will be blocked.

I know that IS is compatible with IX but here there is only one key that really needs the access(one with X lock in session 1 and one with S lock in session 2)

Am I missing anything or tempdb works in a different way as compared to other user databases ??

Thanks for all the great post!!!

March 19, 2012 11:28 AM
 

Rishabh K said:

I think I missed the checkpoint run..Thanks again

March 19, 2012 11:31 AM
 

Alberto gastaldo said:

Hi paul

Great article.  I am a SQL SERVER mct and I was not aware of this behavior.

I suppose if the first query was an update instead of a Select with enforced XLOCK then the second would have blocked

Am I right?

April 10, 2014 3:26 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement