When Read Queries Block Write queries

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:

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

Here’s what the query plan looks like:

Your My Best Wishes

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

Steve Prefetchtaine

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

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:

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:

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

They make me suspicious

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!

Leave a Reply

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