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.
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.
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!
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.