3

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Krip
  • 854
  • 1
  • 7
  • 15

2 Answers2

6

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.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
-3

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

Chinjoo
  • 2,697
  • 6
  • 28
  • 45
  • In my experience today, the NO LOCK hint is not enough to stop a real intra-query parallel thread deadlock event. – Cody Konior Apr 19 '13 at 02:16
  • 1
    It's not advisable to use NO LOCK. It can lead to dirty reads (duplicated data, missing data). Locking is an intentional mechanism to preserve data consistency. Use NO LOCK at your peril. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/ – Davos Aug 04 '15 at 05:01