Home Forums SQL Server 2008 SQL Server 2008 - General Deadlock on update command RE: Deadlock on update command

  • March 25, 2013 at 4:30 am

    #1600219

    bugg (3/25/2013)

    ChrisM@Work (3/25/2013)

    bugg (3/25/2013)

    After applying that index update. I am now receiving another deadlock on that same table:

    Node:1

    KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1

    Grant List 2:

    Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50

    SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1

    Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1

    Requested by:

    ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy:(0x00000002E58F1A60) Value:0xff19db40 Cost:(0/0)

    Node:2

    PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3

    Grant List 1:

    Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680

    SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1

    Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)

    Requested by:

    ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy:(0x00000001C09FA538) Value:0x94aaf3c0 Cost:(0/220)

    Node:3

    Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0

    ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy:(0x00000000B69AC510) Value:0xd9bdc8 Cost:(0/10000)

    Can you post the missing bits please?

    Hi ChrisM, do you need the table def with indexes?

    You've already posted them. No, it looks like a part of the deadlock info is missing. Can you post the actual plans for both of the queries please?

    You may well get some mileage by changing the update query as follows:

    UPDATE orderha

    SET [status] = 2

    WHERE sessionid = '7560129'

    AND orderha = 1

    AND [status] <> 2

    The actual plan for this query would be useful too.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden