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?
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