Category: Locking

Working On It

I’ve been re-working a lot of the demos in a presentation that I’m working on called Index Internals That Matter For Performance, because after a first walk through them, I had a lot of realizations about not only what I was presenting, but the way it was presented, and the order I was presenting it in.

This isn’t abnormal, and it’s hardly my finest moment in video history. But I wanted to stream it because a lot of people are out there who might be thinking about getting into blogging or presenting, and I wanted to show that it’s a process, not something you just walk out and nail like a half-court-no-look-over-the-shoulder-nothing-but-net-shot.

Anyway, I came across a weird thing, and had to make some changes to my helper script WhatsUpLocks to get some more information.

And not get blocked.

Read Committed is a trash isolation level. Don’t @ me.

The Not Weirdness

This is the simplest I could work things out to. I don’t have anything very practical to say about it right now.

Here’s an update:

BEGIN TRAN

UPDATE p
    SET p.Score += 1
FROM dbo.Posts AS p
WHERE p.Id = 999;

ROLLBACK

If we run it up to the rollback, it finishes pretty quickly. We are, after all, just updating a single row that we locate via the primary key.

My original idea for the demo was to show some of the odder things you can run into with blocking, so I wrote this query to return a bunch of rows, but get blocked at the very end.

SELECT TOP (100) p.Id, p.Body
FROM dbo.Posts AS p
WHERE p.Id > 900;

Which is exactly what happens. We get to Id 997 and crap out.

Now if we check on those sessions with WhatsUpLocks, we can see what happened.

SELECT *
FROM dbo.WhatsUpLocks(58) AS wul; --Writer SPID

SELECT *
FROM dbo.WhatsUpLocks(57) AS wul; --Reader SPID

Why is this not weird? Well, comparatively, we take a normal number of overall locks and get blocked in a fairly predictable spot. We get blocked waiting on one of the keys that we need to keep going.

The Weirdness

To backtrack a little bit, part of what I wanted to show was that using order by can sometimes result in “more” blocking. I don’t mean more locks; what I mean is that when we need to order by Score, but we don’t have Score indexed in a useful way, the query will get hung up without showing any rows whatsoever.

SELECT TOP (100) p.Id, p.Body
FROM dbo.Posts AS p
WHERE p.Id > 900
ORDER BY p.Score;

Originally this was a SELECT * query, but I want to show you that it’s specific to the Body column because it’s an NVARCHAR(MAX).

Here’s what comes back from looking at the locks now:

LOOK HOW MANY LOCKS WE TAKE ON PAGES. That’s bananas.

Watch my video on readers blocking writers for a little background on why this could be troublesome.

If I change my query to not have the Body column in the select list, the locks go back to normal.

SELECT TOP (100) p.Id, p.Score
FROM dbo.Posts AS p
WHERE p.Id > 900
ORDER BY p.Score;

Of course, sticking Body in the WHERE clause results in an uptick in shared locks taken:

SELECT TOP (100) p.Id, p.Score
FROM dbo.Posts AS p
WHERE p.Id > 900
AND p.Body LIKE N'_%'
ORDER BY p.Score;

But Of Course…

This kind of thing is maybe not the most likely thing you’ll see happening IRL, because you probably have other indexes that queries can use to access data in different ways. For instance, if I have this index on the Posts table, the first query will still get blocked, but all of the other queries will finish instantly.

CREATE INDEX surprise ON dbo.Posts(Score, Id);

Is this another reason to avoid MAX datatypes? Maybe probably.

Don’t forget that they also prevent predicate pushdown, and they can really mess with memory grants.

But hey, I’m sure you have more than enough indexes to fix everything.

Thanks for reading!

Try And Retry

I’ll sometimes see people implement retry logic to catch deadlocks, which isn’t a terrible idea by itself. The problem that may arise is when the deadlock monitor takes a full 5 seconds to catch a query, which can block other queries, and may generally make things feel slower.

Different Locks

An alternative is to set a lock timeout that’s shorter than five seconds.

DECLARE @lock_try INT = 0

WHILE @lock_try < 5 
BEGIN
    BEGIN TRY

        SET LOCK_TIMEOUT 5; /*five milliseconds*/

        SELECT COUNT(*) AS records FROM dbo.Users AS u;

    END TRY
    BEGIN CATCH

        IF ERROR_NUMBER() <> 1222 /*Lock request time out period exceeded.*/
		RETURN;

    END CATCH;

SET @lock_try += 1;

WAITFOR DELAY '00:00:01.000' /*Wait a second and try again*/

END;

While 5 milliseconds is maybe an unreasonably short time to wait for a lock, I’d rather you start low and go high if you’re trying this at home. The catch block is set up to break if we hit an error other than 1222, which is what gets thrown when a lock request times out.

This is a better pattern than just hitting a deadlock, or just waiting for a deadlock to retry. Normally when a deadlock occurs, one query throws an error, and there’s no attempt to try it again (unless a user is sitting there hitting submit until something works). Waiting ~5 seconds (I know I’m simplifying here, and the deadlock monitor will wake up more frequently after it detects one)

The big question is: are you better off doing this in T-SQL than in your application?

Thanks for reading!