[FIXED] How does one SELECT block another?

Issue

I'm looking at output of SP_WhoIsActive on SQL Server 2005, and it's telling me one session is blocking another - fine. However they both are running a SELECT. How does one SELECT block another? Shouldn't they both be acquiring shared locks (which are compatible with one another)?

Some more details: Neither session has an open transaction count - so they are stand-alone.

The queries join a view with a table.

They are complex queries which join lots of tables and results in 10,000 or so reads.

Any insight much appreciated.

Solution

SELECT statements may block another SELECT statement. You're probably thinking that since both acquire only S locks, they should never block. But blocking occurs on various types of resources, not only locks. Typical example is memory constraints. I'll try to digg up a recent answer to a question here that had attached a deadlock graph that showed to SELECT statements, one waiting for the other for parallel exchange operator memory resources (buffers).

Updated Here is the link with deadlock info I talked about: I have data about deadlocks, but I can't understand why they occur If you study the deadlock graph, you'll notice the following resource in the wait list:

<exchangeEvent id="Pipe894b0680" WaitType="e_waitPipeGetRow" nodeId="0">
  <owner-list>
    <owner id="process824df048"/>
  </owner-list>
  <waiter-list>
    <waiter id="process86ce0988"/>
  </waiter-list>
</exchangeEvent>

This is not a lock, is a 'e_waitPipeGetRow' resource, is owned by a SELECT and another SELECT is waiting for it. Some discussion about 'intra-query parallel resources' can be found here: Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks". While most discussions are going to focus on deadlock issues, that doesn't mean that ordinary blocking cannot occur on these resources. sys.dm_exec_requests will have the proper info in wait_type and wait_resource.

Answered By - Remus Rusanu

Answer Checked By - Robin (PHPFixing Admin)