11

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
  • what version of which db platform are you using? what is the default trx isolation (or concurrency) level? What indexes exist on the system_Queue table currently? – SQLRockstar Apr 4 '11 at 16:42
  • @SQLRockstar part of deadlock graph added, sql server 2008 – garik Apr 4 '11 at 16:46
  • @SQLRockstar IX_system_Queue_DirectionByStatus index by IsOutGoing and StatusID. – garik Apr 4 '11 at 16:50
13

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

  • Bingo! Thank you. It was really strange situation for deadlock I have ever seen. Thank you for answer. – garik Apr 4 '11 at 17:54
6

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://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.