The Art Of The SQL Server Stored Procedure: Locking Hints

None Other

---

Locking hints are funny. Normally I see them in awful products like Dynamics, on select queries, and hilarious levels of blocking and deadlocking issues ensue.

Another place where they’re common is when developers think that adding hints like ROWLOCK means you’ll always only ever lock rows, and that you’ll never have lock escalation when you use it.

Both of those things are wrong. Here’s a video about why:

Where I most frequently do not see locking hints, or the use of an appropriate isolation level, is when developers still for some reason insist on using MERGE statements.

Perhaps I’m a bit salty because I spent a lot of today rewriting merge statements into separate actions. Perhaps I’m just salty generally.

When Do You Need Locking Hints?

---

Locking hints (aside from NOLOCK), have the most impact when you need to protect data beyond the scope of a single query in a transaction.

They have the second most impact when you’re doing strangeland things in the database, like processing queues. You can see my example of how to do that here: Building Reusable Queues In SQL Server Part 2

More often than not, though, you’ll want to use them if you do things like this:

BEGIN TRANSACTION
DECLARE
    @NextUser integer;

SELECT TOP (1)
    @NextUser = u.Id
FROM dbo.Users AS u WITH(UPDLOCK, SERIALIZABLE)
WHERE u.LastAccessDate >=
(
    SELECT
        MAX(u2.LastAccessDate)
    FROM dbo.Users AS u2 WITH(UPDLOCK, SERIALIZABLE)
)
ORDER BY
    u.LastAccessDate DESC; 

INSERT
    dbo.Badges
(
    Name,
    UserId,
    Date
)
VALUES
(
    N'Most Recent User Award',    
    @NextUser,      
    GETDATE()
);
COMMIT;

Why? What if someone else logs in after you assign this variable?

• What locking hints do you need each time you touch the Users table?
• Do you need a transaction to protect the entire thing?

Look at the join order

• What if someone logs in after the first scan of the Users table?

Is this great for concurrency? Well, that depends on how fast the queries are. With some Good Enough™️ indexes, this’ll be just fine.

Without them, you’ll be in a HEAP of trouble. Ahem. This is the time for laughter.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.