How does one SELECT block another?

Question

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.

Answer 1

I think its because the first select is performing row lock/table lock. While joining table you can provide NO LOCK Hint.

Answer 2

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: http://stackoverflow.com/questions/2945135/i-have-data-about-deadlocks-but-i-cant-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.