Category: Locking

Different Engines

I seem to have gotten quite a few questions about this lately, so I’m going to write down some thoughts here.

It’s probably crappy form for a blog post, but what the heck.

Queries Decide The Isolation Level

They can choose anything from read uncommitted to serializable. Read uncommitted is the more honest term for what’s going on. When people see the “nolock” hint, they often assume that means their query won’t take any locks. What it really means is that it will ignore locks taken by other queries. The more accurate term would be “norespect”. This is probably what leads to confusion: reading uncommitted data sounds different than not locking data. But they’re both the same.

The Storage Engine Decides Which Locks Are Used

And if lock escalation is attempted. The storage engine will respect the query’s isolation level, and any table-level settings related to lock granularity, like not allowing row or page locks. It may not fully respect any query level hints regarding lock granularity.

One thing that helps reduce the chance of lock escalation is having a good index to help your modification query find rows. Though if you need to find a million rows, don’t expect SQL Server to happily take a million row locks, just because of an index.

Batching modifications is one way to avoid lock escalation when you need to modify a lot of rows, though it isn’t always possible to do this. If for some reason you need to roll the entire change back, you’d have to keep track of all the committed batches somewhere, or wrap the entire thing in a transaction (which would defeat the purpose, largely).

One thing that increases the chance of lock escalation is having many indexes present on a table. For inserts and deletes, all of those indexes will get touched (unless they’re filtered around the rows to be inserted or deleted. For updates, any indexes containing the column(s) to be modified will need to be touched (again, barring filtering around the updated portion). Lock counts are cumulative across objects.

Not All “Blocking” Is “Locking”

In other words, when you see queries being blocked, there may not be an LCK wait involved. Some “blocking” can happen with resource contention, whether it’s physical (CPU, memory, disk), logical (like if there’s latch or spinlock contention), or even programmatic (if you’re lucky enough to see the source code).

This can happen in tempdb if you’re creating a lot of objects rapid-fire, even if you’re using table variables. Table variables can avoid some of the overhead that temp tables incur under high frequency execution, but not all of it.

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.

Gritty

You may occasionally see in your favorite monitoring tool, or Most Fantastic And Glorious Script For Determining Whom Activity Belongs To™ that a read query, perhaps even a long running one, is blocking modification queries.

Under most circumstances, this won’t happen. Most.

Shared locks taken by read queries will let go pretty quickly.

Under most circumstances.

Out, Liars

To hold onto Shared locks, you’d need to use an isolation level escalation hint, like REPEATABLE READ.

I could do that here if I were a lazy cheater.

Instead, I’m going to show you a more common and interesting scenario.

You see, like a lot of important specks of knowledge, this one comes from Craig Freedman:

Note the “WITH UNORDERED PREFETCH” keywords on the nested loops join.

I am not going to demontrate it , but when SQL Server executes this query, it holds S locks on the rows returned by the index seek until the query finishes executing.

And to generalize a bit from the source of the rest of the important knowledge in the world: These types of key lookups are common in plans with Star Join optimizations.

On To The Text

I’m going to be a little bit of a lazy cheater here, and rather than show you where this can happen with parameter sniffing or some other weird optimizer choice, I’m going to use an index hint to use this index:

CREATE INDEX whatever ON dbo.Votes(CreationDate, VoteTypeId);

Then I’m going to run this query, which’ll take about 10 seconds:

DECLARE @i INT
SELECT   @i = v.PostId
FROM     dbo.Votes AS v WITH (INDEX = whatever)
WHERE    v.CreationDate >= '20080101'
AND      v.VoteTypeId > 5
GROUP BY v.PostId
ORDER BY v.PostId;

Here’s what the query plan looks like:

And when we get the properties of the Nested Loops Join, we’ll see the Unordered Prefetch property set to true.

If I kick that query off and look at the results of sp_WhoIsActive @get_locks = 1, I’ll see this:

    <Object name="Votes" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="S" request_status="GRANT" request_count="4" />
      </Locks>

Which is exactly what I want — a Shared object lock on Votes that has been GRANTed. That’ll get held onto for the duration of the query.

Now when I try to run this update, it’ll get blocked:

BEGIN TRAN
UPDATE dbo.Votes SET UserId = 2147483647 
ROLLBACK

Note that I’m only wrapping it in a transaction here so it’ll roll back. It will still get blocked without that, but then I’d have to reverse the update on my own.

See, everyone’s kind of a lazy cheater.

The locks that the update wants look like this:

    <Object name="Votes" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="WAIT" request_count="1" />
      </Locks>

We see the IX lock has a request status of WAIT.

And Who Is Active is showing us that the read query has been blocking the write query for around 4 seconds.

Capper

If you’re out there in the world and you see a read query that blocked a write query, take a close look at the query plan for a Nested Loops Join with the Unordered Prefetch property set to true.

I bet you’ll find one. And I’ll bet your query wasn’t fast.

Fixing the Key Lookup may not make your query faster, but it should alleviate the blocking because of long-held shared locks.

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.