Why Does Using The Rowlock Hint In SQL Server Create More Locks?

Posted on by Erik Darling

Taking A Hint

There are all sorts of fun hints you can staple onto your queries that will make them behave differently.

Probably the most common one I see is the NOLOCK hint.

Don’t worry, this post has nothing to do with that.

You’ve been finger wagged to death about it already.

Other Lock Hints

Sometimes you want to try to control lock granularity. A noble effort, indeed.

To do that, you can use hints like ROWLOCK or PAGLOCK. They’ll let you tell the weevils inside your server to prefer those types of locks.

The thing is, both are still subject to lock escalation (unless you turn that off, too).

If we take an update query like this, and use sp_WhoIsActive to look at the locks:

    BEGIN TRAN;
    WITH x AS 
    (
        SELECT TOP (5000)
                h.Score
        FROM dbo.HighQuestionScores AS h
        ORDER BY h.Id
    )
    UPDATE x
    SET  x.Score = 2147483647
    FROM x WITH (ROWLOCK);

    ROLLBACK
EXEC dbo.sp_WhoIsActive @get_locks = 1;

The lock info that comes back looks like this:

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

The only lock type that has an X type is the Key lock. The page and object lock types are only intent exclusive. You can read more about intent locks here.

Now, if we change the 5000 to 7000, we get much different results from sp_WhoIsActive.

<Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="1" />

We’ve hit lock escalation, and our precious ROWLOCK hint has been disregarded.

Funny, huh?

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.

Related Posts