Locking When There’s Nothing To Lock

Demo Day

We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome.

Where things get tricky is with locking demos.

Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t sleeping.

Sometimes rollbacks can take a long time. After all, they’re single threaded, and you know how I feel about things that are single threaded.

The No-Row Update

To get around this, I decided to try some updates that didn’t actually update any rows. The WHERE clause excludes everything in the Users table.

I thought that SQL would outsmart me here, but it didn’t. It didn’t even try.

Here are two queries, neither of which will have any qualifying rows in my 2016/03 copy of the database. (If you’re using a more recent export, choose a more recent LastAccessDate.)

The only index on the Users table is a PK/CX on Id. That means it’s the one way in or out of the table for any queries, no matter what they’re trying to do.

So if I run an update that will also hit 0 rows, it has to use that.

No Holds Barred

While that update is running, I’ll try to run my query with the WHERE clause on LastAccessDate. It’ll get blocked — and we can confirm that by running sp_BlitzWho.

Low fiber diet

Rollback, Repeat

So look, clearly we need an index.

Maybe we need 75 indexes. If we do, we’ll run DTA and then set our pants on fire in defiance of all humanity.

But in the meantime, let’s try adding one.

This gives our select query a separate access path. Sort of. I know what you’re thinking — that SELECT * is a trick! He’s messing with us. The clustered index needs to get updated, so the key lookup will get blocked. Ha ha ha. Nice one, dummy. We’re smarter than you.

So I’ll do this instead.

This query only needs columns in our nonclustered index, so it won’t get blocked. Right?



The answer lies in sp_BlitzWho’s much cooler older brother, sp_WhoIsActive.

Our update query is locking the whole darn thing. Even though it changes 0 rows, and the query is done running (though it’s in a BEGIN TRAN), we’re still stuck with this lock.

I learned it from watching you!

So what to do?

Our clustered index doesn’t help, and our nonclustered index doesn’t either. Maybe our update needs an index?

It’s simple enough. We’re updating Reputation, and filtering on Reputation. Maybe we need an index on Reputation, too?

We have some options.

A separate index that leads with Reputation, and index that leads with LastAccessDate, and an index that leads with Reputation.

For the sake of blog post brevity: the index that leads with LastAccessDate doesn’t fix our blocking scenario.

So what’s better: Two separate indexes on (Reputation, Id) and (LastAccessDate, Id), or one index on (Reputation, LastAccessDate, Id)?

And then what if our UPDATE changes? What if we need to filter on LastAccessDate? Or something else? What if having LastAccessDate as the second key column makes our SELECT queries eat speed bumps?

You can’t index for everything, and as we sort-of-glossed-over, leading index key columns seem necessary to get us out of locking jams.

Sure, you could get around things with NOLOCK hints, but if that makes you queasy, it’s time to start looking into other Isolation Levels.

Indexes can be extra tricky. If you’re lucky enough to be going to PASS, be sure to hit up Kendra’s session on indexing.

I’ll probably be there, making sure you don’t start texting.

Previous Post
Why Missing Index Recommendations Aren’t Perfect
Next Post
Chaining Agent Tasks For Alerting The Right Way

7 Comments. Leave new

  • Hi Erick,
    [[You can’t index for everything]]
    True, but you can index for anything.
    I have had some success with “temporary indexes” (create it in the query, drop when query finishes) for situations where the overall squeeze of leaving a permanent index is not worth the juice 🙂
    Do you ever try them?

    • Erik Darling
      August 3, 2017 1:32 pm

      In short, yes, but there are a lot of caveats.

      Creating the index has to be faster and cheaper, and I’d tend not to do it on Standard Edition with the lack of online index creation, etc.

  • yep, needs to be faster and cheaper to be useful. And those qualities could be unpredictable over time as the underlying data changes, but the code does not 🙂
    But they have definitely helped me on a number of occasions to go from unacceptable, to acceptable, performance.
    And I have been lucky not to have to worry about standard edition since I was a student…

    • Erik Darling
      August 3, 2017 7:09 pm

      Ah, yeah, as data grows I usually start to think if a filtered index would be more appropriate. There’s usually at least one constant that would make a good candidate.

  • set read_committed_snapshot on?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.