SEO

I asked the DBA to start the Server with options -T1204 and -T3605 and
here's what I get. I need help deciphering this. This happens when we
have 5 usrs performing concurrent actions and for the life of me cannot
decipher this. WHat is a Node, what is the branch ?
WHat are the ECIDs. I cannot even easily find out what process blocks
what. Does anyone have any experience with these. ANy and all help will
be appreaciated. I have posted the output of one of the many deadlocks
encountered. Thanks

Deadlock encountered .... Printing deadlock information
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Wait-for graph
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:1
2005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:
0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2005-01-11 08:49:59.49 spid4 SPID: 61 ECID: 0 Statement Type:
SELECT Line #: 651
2005-01-11 08:49:59.49 spid4 Input Buf: RPC Event:
Procedure_GetLatestValues;1
2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:
61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Consumer List::
2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =
0x46d09590, SPID: 61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Producer List::
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =
0x4732c098, SPID: 61, ECID: 6, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =
0x46824098, SPID: 61, ECID: 5, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =
0x47296098, SPID: 61, ECID: 7, Blocking
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:2
2005-01-11 08:49:59.49 spid4 PAG: 7:1:405367
CleanCnt:3 Mode: IX Flags: 0x2
2005-01-11 08:49:59.49 spid4 Grant List 1::
2005-01-11 08:49:59.49 spid4 Grant List 3::
2005-01-11 08:49:59.49 spid4 Owner:0x4ed4b8e0 Mode: IX
Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
2005-01-11 08:49:59.49 spid4 SPID: 64 ECID: 0 Statement Type:
UPDATE Line #: 1
2005-01-11 08:49:59.49 spid4 Input Buf: RPC Event:
Standard_InsertNamePair;1
2005-01-11 08:49:59.49 spid4 Requested By:
2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:61 ECID:6 Ec0x4732C098) Value:0x4aafe660 Cost0/A6C)
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:3
2005-01-11 08:49:59.49 spid4 PAG: 7:1:405373
CleanCnt:1 Mode: IX Flags: 0x2
2005-01-11 08:49:59.49 spid4 Grant List 1::
2005-01-11 08:49:59.49 spid4 Owner:0x4ac00160 Mode: IX
Flg:0x0 Ref:1 Life:02000000 SPID:61 ECID:0
2005-01-11 08:49:59.49 spid4 Requested By:
2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:
U SPID:64 ECID:0 Ec0x472F3590) Value:0x46c24a40 Cost0/A38)
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 -- next branch --
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:1
2005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:
0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:
61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Consumer List::
2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =
0x46d09590, SPID: 61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Producer List::
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =
0x4732c098, SPID: 61, ECID: 6, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =
0x46824098, SPID: 61, ECID: 5, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =
0x47296098, SPID: 61, ECID: 7, Blocking
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:6
2005-01-11 08:49:59.49 spid4 PAG: 7:1:405367
CleanCnt:3 Mode: IX Flags: 0x2
2005-01-11 08:49:59.49 spid4 Wait List:
2005-01-11 08:49:59.49 spid4 Owner:0x4aafe660 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:6
2005-01-11 08:49:59.49 spid4 Requested By:
2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:61 ECID:5 Ec0x46824098) Value:0x4edc4d80 Cost0/A6C)
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 -- next branch --
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:1
2005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:
0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:
61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Consumer List::
2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =
0x46d09590, SPID: 61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Producer List::
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =
0x4732c098, SPID: 61, ECID: 6, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =
0x46824098, SPID: 61, ECID: 5, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =
0x47296098, SPID: 61, ECID: 7, Blocking
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:9
2005-01-11 08:49:59.49 spid4 PAG: 7:1:405367
CleanCnt:3 Mode: IX Flags: 0x2
2005-01-11 08:49:59.49 spid4 Wait List:
2005-01-11 08:49:59.49 spid4 Owner:0x4aafe660 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:6
2005-01-11 08:49:59.49 spid4 Requested By:
2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:61 ECID:7 Ec0x47296098) Value:0x42bef7c0 Cost0/A6C)
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 -- next branch --
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4
2005-01-11 08:49:59.49 spid4 Node:1
2005-01-11 08:49:59.49 spid4 Port: 0x42bf0280 Xid Slot: 0, EC:
0x46d09590, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2005-01-11 08:49:59.49 spid4 Coordinator: EC = 0x46d09590, SPID:
61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Consumer List::
2005-01-11 08:49:59.49 spid4 Consumer: Xid Slot: 0, EC =
0x46d09590, SPID: 61, ECID: 0, Not Blocking
2005-01-11 08:49:59.49 spid4 Producer List::
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 1, EC =
0x4732c098, SPID: 61, ECID: 6, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 2, EC =
0x46824098, SPID: 61, ECID: 5, Blocking
2005-01-11 08:49:59.49 spid4 Producer: Xid Slot: 3, EC =
0x47296098, SPID: 61, ECID: 7, Blocking
2005-01-11 08:49:59.49 spid4 Victim Resource Owner:
2005-01-11 08:49:59.49 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:64 ECID:0 Ec0x472F3590) Value:0x46c24a40 Cost0/A38)
2005-01-11 08:50:15.11 spid4

Always useful to have a look at :
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Always useful to have a look at :
http://www.sommarskog.se/sqlutil/aba_lockinfo.html

drdeadpan (vkat01-nospam@yahoo.com) writes:
> I asked the DBA to start the Server with options -T1204 and -T3605 and
> here's what I get. I need help deciphering this. This happens when we
> have 5 usrs performing concurrent actions and for the life of me cannot
> decipher this. WHat is a Node, what is the branch ?
> WHat are the ECIDs. I cannot even easily find out what process blocks
> what. Does anyone have any experience with these. ANy and all help will
> be appreaciated. I have posted the output of one of the many deadlocks
> encountered. Thanks

Deadlock graphs are unfortunately very difficult to read.

But what I can tell from your dump is that your deadlock involves
parallelism. ECID is a thread-id within a spid, and it seems that
some threads may be blocking each other.

When it says "PAG 7:1:405367" that's the resource being fought about.
7 is the database id, and 1 is the file id. Since this is page, the
last number is a page number. By using DBCC PAGE you can find out
which table the page belongs to. (But DBCC PAGE is not documented in
Books Online, and I don't remember the parameters. But a Google search
might find something.)

Parallelism is often more to harm than benefit in my experience. If
you can track down the query that SPID 61 submitted, an OPTION (MAXDOP 1)
at the end of that query, prevents parallelism. (MAXDOP = MAX Degree of
Parallelism.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks as always. I did learn something from that.
Ok, here's an easier one Erland. What can I infer from this?

Deadlock encountered .... Printing deadlock information
2005-01-13 09:00:10.64 spid4
2005-01-13 09:00:10.64 spid4 Wait-for graph
2005-01-13 09:00:10.64 spid4
2005-01-13 09:00:10.64 spid4 Node:1
2005-01-13 09:00:10.64 spid4 KEY: 7:1038026504:2 (17018f579faa)
CleanCnt:1 Mode: X Flags: 0x0
2005-01-13 09:00:10.64 spid4 Grant List 0::
2005-01-13 09:00:10.64 spid4 Owner:0x6746f520 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2005-01-13 09:00:10.64 spid4 SPID: 62 ECID: 0 Statement Type:
DELETE Line #: 122
2005-01-13 09:00:10.64 spid4 Input Buf: RPC Event:
Requisition_Scansample;1
2005-01-13 09:00:10.64 spid4 Requested By:
2005-01-13 09:00:10.64 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:57 ECID:0 Ec0x70E27590) Value:0x7102e5c0 Cost0/A10)
2005-01-13 09:00:10.64 spid4
2005-01-13 09:00:10.64 spid4 Node:2
2005-01-13 09:00:10.64 spid4 KEY: 7:1811953977:6 (420154596011)
CleanCnt:1 Mode: X Flags: 0x0
2005-01-13 09:00:10.64 spid4 Grant List 3::
2005-01-13 09:00:10.64 spid4 Owner:0x550cdac0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:57 ECID:0
2005-01-13 09:00:10.64 spid4 SPID: 57 ECID: 0 Statement Type:
CONDITIONAL Line #: 59
2005-01-13 09:00:10.64 spid4 Input Buf: RPC Event:
Requisition_Scansample;1
2005-01-13 09:00:10.64 spid4 Requested By:
2005-01-13 09:00:10.64 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:62 ECID:0 Ec0x670F7590) Value:0x718e8680 Cost0/27E4)
2005-01-13 09:00:10.64 spid4 Victim Resource Owner:
2005-01-13 09:00:10.64 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:57 ECID:0 Ec0x70E27590) Value:0x7102e5c0 Cost0/A10)
2005-01-13 09:01:00.97 spid4

Thanks..

drdeadpan (vkat01-nospam@yahoo.com) writes:
> Thanks as always. I did learn something from that.
> Ok, here's an easier one Erland. What can I infer from this?

First of all, you do have this information:

> DELETE Line #: 122
> 2005-01-13 09:00:10.64 spid4 Input Buf: RPC Event:
> Requisition_Scansample;1
>...
> CONDITIONAL Line #: 59
> 2005-01-13 09:00:10.64 spid4 Input Buf: RPC Event:
> Requisition_Scansample;1

That is procedure name and line number. Although if Requisition_Scansample
calls other procedures, the line number may refer to any these instead.

We also have:

> 2005-01-13 09:00:10.64 spid4 KEY: 7:1038026504:2 (17018f579faa)

SELECT db_name(7) will give you the database, and if you then
go that database SELECT object_name(1038026504) will give you
the table. Finally 2 is index = 2 for that table, check sysindexes.

And again:

> 2005-01-13 09:00:10.64 spid4 KEY: 7:1811953977:6 (420154596011)

Now you know the involved objects and you have some hint where it's
happening. Then use your knowledge about what's happening in
Requisition_Scansample to understand what is happening.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp