Hi, I would like to know how to interpret the wait for graph entries.. i am not sure of the hex values , which go along with the KEY field ... and granularity of the lock on the resource. Anything in simple terms , would be appreciated.
2007-10-16 15:51:15.79 spid4 Wait-for graph 0 2007-10-16 15:51:15.79 spid4 0 2007-10-16 15:51:15.79 spid4 Node:1 0 2007-10-16 15:51:15.79 spid4 KEY: 65:298692362:2 (c901bf7ffc9a) CleanCnt:3 Mode: X Flags: 0x0 0 2007-10-16 15:51:15.79 spid4 Wait List: 0 2007-10-16 15:51:15.79 spid4 Owner:0x2d2a1920 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:727 ECID:0 0 2007-10-16 15:51:15.79 spid4 SPID: 727 ECID: 0 Statement Type: UPDATE Line #: 1165 0 2007-10-16 15:51:15.79 spid4 Input Buf: RPC Event: Proc_SP1;1 0 2007-10-16 15:51:15.79 spid4 Requested By: 0 2007-10-16 15:51:15.79 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:722 ECID:0 Ec0x4907D5A0) Value:0x449acce0 Cost0/4DF8) 0 2007-10-16 15:51:15.79 spid4 0 2007-10-16 15:51:15.79 spid4 Node:2 0 2007-10-16 15:51:15.79 spid4 KEY: 65:298692362:2 (c901bf7ffc9a) CleanCnt:3 Mode: X Flags: 0x0 0 2007-10-16 15:51:15.79 spid4 Grant List 1:: 0 2007-10-16 15:51:15.79 spid4 Owner:0x7ac90c00 Mode: X Flg:0x0 Ref:0 Life:02000001 SPID:724 ECID:0 0 2007-10-16 15:51:15.79 spid4 SPID: 724 ECID: 0 Statement Type: UPDATE Line #: 1165 0 2007-10-16 15:51:15.79 spid4 Input Buf: RPC Event: Proc_SP1;1 0 2007-10-16 15:51:15.79 spid4 Requested By: 0 2007-10-16 15:51:15.79 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:727 ECID:0 Ec0x357035A0) Value:0x2d2a1920 Cost0/4F1C) 0 2007-10-16 15:51:15.79 spid4 0 2007-10-16 15:51:15.79 spid4 Node:3 0 2007-10-16 15:51:15.79 spid4 KEY: 65:298692362:2 (6f0241d133e8) CleanCnt:2 Mode: X Flags: 0x0 0 2007-10-16 15:51:15.79 spid4 Grant List 3:: 0 2007-10-16 15:51:15.79 spid4 Owner:0x708a3160 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:722 ECID:0 0 2007-10-16 15:51:15.79 spid4 SPID: 722 ECID: 0 Statement Type: UPDATE Line #: 1165 0 2007-10-16 15:51:15.79 spid4 Input Buf: RPC Event: proc_SP1;1 0 2007-10-16 15:51:15.79 spid4 Requested By: 0 2007-10-16 15:51:15.79 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:724 ECID:0 Ec0x3418D5A0) Value:0x34c5f120 Cost0/3D60) 0 2007-10-16 15:51:15.79 spid4 Victim Resource Owner: 0 2007-10-16 15:51:15.79 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:724 ECID:0 Ec0x3418D5A0) Value:0x34c5f120 Cost0/3D60) 0 2007-10-16 15:51:20.79 spid4
I would like to know the difference between the wait list and the requested-by.... How do we have two nodes for the same resource?
See this blog - http://decipherinfosys.wordpress.com/2007/01/21/interpreting-deadlock-trace-output-in-sql-server/ in interpreting the deadlock information.