Deadlocks

Can anyone help me with my questions about the following deadlock.
Deadlock encountered ... Printing deadlock information
2006-08-14 15:45:55.04 spid4
2006-08-14 15:45:55.04 spid4 Wait-for graph
2006-08-14 15:45:55.04 spid4
2006-08-14 15:45:55.04 spid4 Node:1
2006-08-14 15:45:55.04 spid4 KEY: 9:363864363:1 (2002aa6e06a0)
CleanCnt:1 Mode: X Flags: 0x0
2006-08-14 15:45:55.04 spid4 Grant List 1::
2006-08-14 15:45:55.04 spid4 Owner:0x3f81b5c0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:60 ECID:0
2006-08-14 15:45:55.04 spid4 SPID: 60 ECID: 0 Statement Type: SELECT
Line #: 40
2006-08-14 15:45:55.04 spid4 Input Buf: RPC Event: spManagePVOrder;1
2006-08-14 15:45:55.04 spid4 Requested By:
2006-08-14 15:45:55.04 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:59 ECID:0 Ec0x3B241530) Value:0x77a971a0 Cost0/0)
2006-08-14 15:45:55.04 spid4
2006-08-14 15:45:55.04 spid4 Node:2
2006-08-14 15:45:55.04 spid4 PAG: 12:1:144496 CleanCnt:1
Mode: SIX Flags: 0x0
2006-08-14 15:45:55.04 spid4 Grant List 1::
2006-08-14 15:45:55.04 spid4 Owner:0x3f81bce0 Mode: SIX Flg:0x0
Ref:0 Life:02000000 SPID:59 ECID:0
2006-08-14 15:45:55.04 spid4 SPID: 59 ECID: 0 Statement Type: DELETE
Line #: 136
2006-08-14 15:45:55.04 spid4 Input Buf: RPC Event: sp_executesql;1
2006-08-14 15:45:55.04 spid4 Requested By:
2006-08-14 15:45:55.04 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec0x6D8454E8) Value:0x77a97a00 Cost0/0)
2006-08-14 15:45:55.04 spid4 Victim Resource Owner:
2006-08-14 15:45:55.04 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec0x6D8454E8) Value:0x77a97a00 Cost0/0)
I know what this means Node 1 was blocked by Process 60, and requested by
process 59, and Node 1 was blocked by Process 59, but requested by process
60 (which created the deadlock). The victim was process 60.
But I need some help for the following:
1.- How can I know what is Node 2 (PAG: 12:1:144496 ) I have tried DBCC PAGE
(12,14496,3) but all I got is "DBCC execution completed. If DBCC printed
error messages, contact your system administrator."
2.- How can I know what application called sp_executesql in process 60 ?..
3.- How can I fix the problem, since probably I only have control over
sp_managePVorders ?.
Regards,
Pablo.Silva@.Aspentech.com
Inline ...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Pablo Silva" <PabloSilva@.discussions.microsoft.com> wrote in message
news:F92D8349-F9CF-4A5F-AE4B-73BA48F2C881@.microsoft.com...
> Can anyone help me with my questions about the following deadlock.
>
> Deadlock encountered ... Printing deadlock information
> 2006-08-14 15:45:55.04 spid4
> 2006-08-14 15:45:55.04 spid4 Wait-for graph
> 2006-08-14 15:45:55.04 spid4
> 2006-08-14 15:45:55.04 spid4 Node:1
> 2006-08-14 15:45:55.04 spid4 KEY: 9:363864363:1 (2002aa6e06a0)
> CleanCnt:1 Mode: X Flags: 0x0
> 2006-08-14 15:45:55.04 spid4 Grant List 1::
> 2006-08-14 15:45:55.04 spid4 Owner:0x3f81b5c0 Mode: X
> Flg:0x0
> Ref:0 Life:02000000 SPID:60 ECID:0
> 2006-08-14 15:45:55.04 spid4 SPID: 60 ECID: 0 Statement Type:
> SELECT
> Line #: 40
> 2006-08-14 15:45:55.04 spid4 Input Buf: RPC Event:
> spManagePVOrder;1
> 2006-08-14 15:45:55.04 spid4 Requested By:
> 2006-08-14 15:45:55.04 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:59 ECID:0 Ec0x3B241530) Value:0x77a971a0 Cost0/0)
> 2006-08-14 15:45:55.04 spid4
> 2006-08-14 15:45:55.04 spid4 Node:2
> 2006-08-14 15:45:55.04 spid4 PAG: 12:1:144496 CleanCnt:1
> Mode: SIX Flags: 0x0
> 2006-08-14 15:45:55.04 spid4 Grant List 1::
> 2006-08-14 15:45:55.04 spid4 Owner:0x3f81bce0 Mode: SIX
> Flg:0x0
> Ref:0 Life:02000000 SPID:59 ECID:0
> 2006-08-14 15:45:55.04 spid4 SPID: 59 ECID: 0 Statement Type:
> DELETE
> Line #: 136
> 2006-08-14 15:45:55.04 spid4 Input Buf: RPC Event: sp_executesql;1
> 2006-08-14 15:45:55.04 spid4 Requested By:
> 2006-08-14 15:45:55.04 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec0x6D8454E8) Value:0x77a97a00 Cost0/0)
> 2006-08-14 15:45:55.04 spid4 Victim Resource Owner:
> 2006-08-14 15:45:55.04 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec0x6D8454E8) Value:0x77a97a00 Cost0/0)
> I know what this means Node 1 was blocked by Process 60, and requested by
> process 59, and Node 1 was blocked by Process 59, but requested by
> process
> 60 (which created the deadlock). The victim was process 60.
Node 1 was granted to Process 60 and requested by process 59
Node 2 was granted to Process 59 and requested by process 60
> But I need some help for the following:
> 1.- How can I know what is Node 2 (PAG: 12:1:144496 ) I have tried DBCC
> PAGE
> (12,14496,3) but all I got is "DBCC execution completed. If DBCC printed
> error messages, contact your system administrator."
You have to turn on trace flag 3604 to get results from DBCC PAGE:
DBCC TRACEON (3604)
DBCC PAGE (12,14496,3)

> 2.- How can I know what application called sp_executesql in process 60 ?..
The trace does not contain this information. If you are running a trace when
the deadlock occurs, that is the best way to get the info.

> 3.- How can I fix the problem, since probably I only have control over
> sp_managePVorders ?.
Did this just happen once? If so, it is not a problem as long as your
application detects it and resubmits the query. If it happens often, you can
start a trace to capture deadlock, deadlock chains, batches, stored
procedures statements, which will give you more details as to the exact
series of events that led to the deadlock.

> Regards,
> Pablo.Silva@.Aspentech.com
sql