The Case of the Missing Shared Locks

This post covers 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 dbo.Demo
    (some_key, some_value)
VALUES
    (1, 100);
GO
IF DB_NAME() = N'tempdb'
    CHECKPOINT;

Now let’s 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 AS 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 AS T
JOIN sys.dm_tran_locks AS 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:

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

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

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 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 the right 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 because no shared locks were issued. We can verify that this is the case by checking the locks acquired using Profiler:

There are Intent-Shared (IS) locks at the table and page levels, which are compatible with the Intent-Exclusive (IX) locks held by our first query (see Lock Compatibility in the documentation).

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 AS 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 (IX) lock.

No Uncommitted Changes on the Page

SQL Server cannot apply the locking optimization if there is an uncommitted change on the same 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 per-page basis, so a read may acquire shared row-level locks on some pages (e.g. 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 first connection with the still-open transaction:

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

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

If we run our SELECT query again, we find that it blocks trying to acquire a shared lock, as expected.

If we add a READPAST table hint to skip the locked rows, we find that the query completes immediately, and no rows are returned:

SELECT
    D.some_key,
    D.some_value 
FROM dbo.Demo AS 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.

A Profiler trace shows both shared row-level locks timing out:

Many of the exclusive locks taken in 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, The Impact of Non-Updating Updates.

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 AS D
    WITH (XLOCK);

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

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

Rollbacks Are 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 dirty page is written 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 dbo.Demo
    (some_key, some_value)
VALUES
    (3, 300);

ROLLBACK TRANSACTION;

A SELECT on the affected page now takes shared locks — even though there are no exclusive locks on that page:

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

The Profiler trace shows the row-level shared locks:

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

BEGIN TRANSACTION;

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

COMMIT  TRANSACTION;

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

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

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 a directive — the storage engine may or may not respect it.)

For example, a query that issues shared (S) page locks will block if it encounters an Intent-Exclusive (IX) lock but the query that specifies ROWLOCK may only issue Intent-Shared (IS) 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 locking optimization exists.

The Read Committed isolation level guarantees a transaction 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 taken.

© Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi