Why does this query cause a deadlock?

Question

Why does this query cause a deadlock?

UPDATE TOP(1) system_Queue SET
  [StatusID] = 2,
  @ID = InternalID
WHERE InternalID IN (
    SELECT TOP 1 
      InternalID FROM system_Queue
    WHERE IsOutGoing = @IsOutGoing AND StatusID = 1
ORDER BY MessageID ASC, InternalID ASC)

Deadlock graph added:

<keylock hobtid="72057594236436480" dbid="9" objectname="Z.dbo.system_Queue" indexname="PK_system_Queue" id="lock5b25cc80" mode="X" associatedObjectId="72057594236436480">
    <owner-list>
     <owner id="processc6fe40" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc7b8e8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594405453824" dbid="9" objectname="Z.dbo.system_Queue" indexname="IX_system_Queue_DirectionByStatus" id="lock48cf3180" mode="S" associatedObjectId="72057594405453824">
    <owner-list>
     <owner id="processc7b8e8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc6fe40" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>

ADDED:

Thank you Sankar for article that has solutions how to avoid this type of deadlock:

• eliminate unnecessary columns from reader’s projection so he does not have to look up the clustered index
• add required columns as contained columns to the non-clustered index to make the index covering, again so that the reader does not have look up the clustered index
• avoid updates that have to maintain the non-clustered index

Answer 1

It looks to me as if you are trying to do an SELECT and an UPDATE in the same statement and onto the same table.

The SELECT is holding a shared lock on the values inside the IX_system_Queue_DirectionByStatus index, and the UPDATE needs for those locks to be released before it can get it's exclusive lock which will update the primary key (which I will guess is clustered and also part of the IX_system_Queue_DirectionByStatus key value).

Anyway, my guess is this query would only succeed on the rare chance that the index values it is selecting and updating are not conflicting. Is it deadlocking each time you execute (I assume that it would be).

Here is a link that explains deadlocks in more detail: http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx

Answer 2

I don't expect you to mark this post as an answer but sharing more information here by other SQL Server experts on this topic.

• http://web.archive.org/web/20111222024618/http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx

• http://rusanu.com/2009/05/16/readwrite-deadlock/