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 Ec:(0x3B241530) Value:0x77a971a0 Cost:(0/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 Ec:(0x6D8454E8) Value:0x77a97a00 Cost:(0/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 Ec:(0x6D8454E8) Value:0x77a97a00 Cost:(0/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.Si***@Aspentech.com

Inline ...

Show quoteHide quoteNode 1 was granted to Process 60 and requested by process 59
Node 2 was granted to Process 59 and requested by process 60
You have to turn on trace flag 3604 to get results from DBCC PAGE:

DBCC TRACEON (3604)
DBCC PAGE (12,14496,3)


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.

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.

Show quoteHide quote

Database Restore Error
Can not save relationship
Looking for Database comparison/synchronizing tool
SQL 2005 SMO Scripts
SQL Server 2000 - Setting EM to check status of servers