Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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
add comment

2 Answers 2

up vote 10 down vote accepted

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

share|improve this answer
    
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
add comment

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/

share|improve this answer
    
+1 thank you for list of solutions. –  garik Apr 4 '11 at 19:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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