LOB Data And Weird Locks In SQL Server

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!

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.