Why SQL Server’s READPAST Hint Doesn’t Always Work

Oh, Bother

---

Sometimes, you only wanna work on one thing. Other times, you only wanna work on something if it’s not locked by something else.

Locking hints can be really handy in these situations, especially the READPAST hint. The documentation for it says that it allows you to skip over row level locks (that means you can’t skip over page or object level locks).

What it leaves out is that your READPAST query may also need to try to take row level shared locks.

Here’s an example!

Sterling Reputation

---

If I run this query, it’ll take out locks we don’t want (without an index on Reputation).

BEGIN TRAN
UPDATE u
SET u.Reputation += 1
FROM dbo.Users AS u
WHERE u.Reputation = 1047863;

ROLLBACK

If we use sp_WhoIsActive @get_locks = 1; we’ll get this back:

<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Users_Id" request_mode="X" request_status="GRANT" request_count="1" />

We take a single exclusive page lock, which means our readpast query can’t skip it.

SELECT TOP (1)  
        u.Id,
        u.DisplayName, 
        u.Reputation
FROM dbo.Users AS u WITH(READPAST)
ORDER BY u.Reputation DESC;

We’ll get blocked. We’ll need to change our update query to do this:

BEGIN TRAN
UPDATE u
SET u.Reputation += 1
FROM dbo.Users AS u WITH(ROWLOCK)
WHERE u.Reputation = 1047863;

ROLLBACK

We’re now asking SQL Server to only lock rows if it can, for as long as it can.

Now our locks change to this:

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Users_Id" request_mode="IX" request_status="GRANT" request_count="1" />

Rather than exclusive page locks, we’re taking an exclusive key lock, and only intent exclusive locks elsewhere.

What’s amusing here is that our select query will… Still be blocked!

But why?

Going back to look at the locks it’s trying to take, we’ll see this:

<Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="5" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Users_Id" request_mode="S" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Users_Id" request_mode="S" request_status="WAIT" request_count="1" />

So uh. Our read query is shooting itself in the foot by trying to lock pages rather than rows.

That’s… brave.

We need to change the locking hints slightly to avoid that.

SELECT TOP (1)  
        u.Id,
        u.DisplayName, 
        u.Reputation
FROM dbo.Users AS u WITH(READPAST, ROWLOCK)
ORDER BY u.Reputation DESC;

With the rowlock hint here, our query finishes.

Index Alternative

---

If we start the process over, but add this index:

CREATE INDEX ix_dammit_please_just_work
    ON dbo.Users(Reputation);

Some things will be different:

• We don’t need a rowlock hint on the update
• We don’t need a rowlock hint on the select

This is true despite there being more overall locks taken for the update:

<Lock resource_type="KEY" index_name="ix_dammit_please_just_work" request_mode="X" request_status="GRANT" request_count="2" />
<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="ix_dammit_please_just_work" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" index_name="PK_Users_Id" request_mode="IX" request_status="GRANT" request_count="1" />

But the addition of the index makes row level locks a more obvious choice for both queries.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.