Link to home
Start Free TrialLog in
Avatar of Gbemisola4
Gbemisola4

asked on

Reading SQL Trace

I have got this output result ,could you please explain what this message is for me
Details includes how to know which tables is causing deadlocking and what can be down to reslove the problem.
Could you please write the scritpt that will tell me which tables is causing the lock. could you please also break all down

2004-10-11 15:32:12.85 spid4     Node:1
2004-10-11 15:32:12.85 spid4     PAG: 6:65:1782207              CleanCnt:1 Mode: S Flags: 0x2
2004-10-11 15:32:12.85 spid4      Grant List 1::
2004-10-11 15:32:12.85 spid4      Grant List 2::
2004-10-11 15:32:12.85 spid4        Owner:0x5a2e17c0 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:60 ECID:0
2004-10-11 15:32:12.85 spid4        SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:12.85 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:12.85 spid4      Requested By:
2004-10-11 15:32:12.85 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x47CBF520) Value:0x5ac7ec80 Cost:(0/5C)
2004-10-11 15:32:12.85 spid4    
2004-10-11 15:32:12.85 spid4     Node:2
2004-10-11 15:32:12.85 spid4     PAG: 6:65:1790483              CleanCnt:1 Mode: S Flags: 0x2
2004-10-11 15:32:12.85 spid4      Grant List 1::
2004-10-11 15:32:12.85 spid4        Owner:0x5aa12e20 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:58 ECID:0
2004-10-11 15:32:12.85 spid4        SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:12.85 spid4        Input Buf: RPC Event: sp_execute;1
2004-10-11 15:32:12.85 spid4      Grant List 2::
2004-10-11 15:32:12.85 spid4      Requested By:
2004-10-11 15:32:12.85 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:60 ECID:0 Ec:(0x47B99520) Value:0x5ac71980 Cost:(0/5C)
2004-10-11 15:32:12.85 spid4     Victim Resource Owner:
2004-10-11 15:32:12.85 spid4      ResType:LockOwner Stype:'OR' Mode: IX SPID:60 ECID:0 Ec:(0x47B99520) Value:0x5ac71980 Cost:(0/5C)


2004-10-11 15:32:17.87 spid4     Wait-for graph
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:1
2004-10-11 15:32:17.87 spid4     PAG: 6:65:1789710              CleanCnt:1 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Grant List 0::
2004-10-11 15:32:17.87 spid4        Owner:0x5ab41380 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:56 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4      Grant List 1::
2004-10-11 15:32:17.87 spid4      Grant List 3::
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:73 ECID:0 Ec:(0x49031520) Value:0x5b0aae40 Cost:(0/5C)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:2
2004-10-11 15:32:17.87 spid4     Port: 0x42c00200  Xid Slot: 0, EC: 0x47e51520, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-10-11 15:32:17.87 spid4     Coordinator: EC = 0x47e51520, SPID: 56, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Consumer List::
2004-10-11 15:32:17.87 spid4     Consumer: Xid Slot: 0, EC = 0x47e51520, SPID: 56, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Producer List::
2004-10-11 15:32:17.87 spid4     Producer: Xid Slot: 2, EC = 0x59744098, SPID: 56, ECID: 4, Blocking
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:3
2004-10-11 15:32:17.87 spid4     PAG: 6:65:71632                CleanCnt:2 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Wait List:
2004-10-11 15:32:17.87 spid4        Owner:0x5acfaa60 Mode: IX       Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 58 ECID: 0 Statement Type: INSERT Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_execute;1
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:56 ECID:4 Ec:(0x59744098) Value:0x5ad37e60 Cost:(0/0)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:4
2004-10-11 15:32:17.87 spid4     PAG: 6:65:71632                CleanCnt:2 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Grant List 1::
2004-10-11 15:32:17.87 spid4        Owner:0x5ab79200 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:73 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x47CBF520) Value:0x5acfaa60 Cost:(0/260)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     -- next branch --
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:2
2004-10-11 15:32:17.87 spid4     Port: 0x42c00200  Xid Slot: 0, EC: 0x47e51520, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-10-11 15:32:17.87 spid4     Coordinator: EC = 0x47e51520, SPID: 56, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Consumer List::
2004-10-11 15:32:17.87 spid4     Consumer: Xid Slot: 0, EC = 0x47e51520, SPID: 56, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Producer List::
2004-10-11 15:32:17.87 spid4     Producer: Xid Slot: 2, EC = 0x59744098, SPID: 56, ECID: 4, Blocking
2004-10-11 15:32:17.87 spid4     Victim Resource Owner:
2004-10-11 15:32:17.87 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:56 ECID:4 Ec:(0x59744098) Value:0x5ad37e60 Cost:(0/0)
2004-10-11 15:32:17.87 spid4    
Deadlock encountered .... Printing deadlock information
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Wait-for graph
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:1
2004-10-11 15:32:17.87 spid4     PAG: 6:65:71632                CleanCnt:2 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Grant List 1::
2004-10-11 15:32:17.87 spid4        Owner:0x5ab79200 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:73 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x47CBF520) Value:0x5acfaa60 Cost:(0/260)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:2
2004-10-11 15:32:17.87 spid4     PAG: 6:65:1789710              CleanCnt:1 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Grant List 0::
2004-10-11 15:32:17.87 spid4        Owner:0x5ab41380 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:56 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 56 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4      Grant List 1::
2004-10-11 15:32:17.87 spid4      Grant List 3::
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:73 ECID:0 Ec:(0x49031520) Value:0x5b0aae40 Cost:(0/5C)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:6
2004-10-11 15:32:17.87 spid4     Port: 0x42c00280  Xid Slot: 0, EC: 0x49091520, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-10-11 15:32:17.87 spid4        SPID: 66 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4     Coordinator: EC = 0x49091520, SPID: 66, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Consumer List::
2004-10-11 15:32:17.87 spid4     Consumer: Xid Slot: 0, EC = 0x49091520, SPID: 66, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Producer List::
2004-10-11 15:32:17.87 spid4     Producer: Xid Slot: 5, EC = 0x5c864098, SPID: 66, ECID: 6, Blocking
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:7
2004-10-11 15:32:17.87 spid4     PAG: 6:65:71632                CleanCnt:2 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Wait List:
2004-10-11 15:32:17.87 spid4        Owner:0x5acfaa60 Mode: IX       Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 58 ECID: 0 Statement Type: INSERT Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_execute;1
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:66 ECID:6 Ec:(0x5C864098) Value:0x5ad37400 Cost:(0/0)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     -- next branch --
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:6
2004-10-11 15:32:17.87 spid4     Port: 0x42c00280  Xid Slot: 0, EC: 0x49091520, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-10-11 15:32:17.87 spid4     Coordinator: EC = 0x49091520, SPID: 66, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Consumer List::
2004-10-11 15:32:17.87 spid4     Consumer: Xid Slot: 0, EC = 0x49091520, SPID: 66, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Producer List::
2004-10-11 15:32:17.87 spid4     Producer: Xid Slot: 5, EC = 0x5c864098, SPID: 66, ECID: 6, Blocking
2004-10-11 15:32:17.87 spid4     Victim Resource Owner:
2004-10-11 15:32:17.87 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:66 ECID:6 Ec:(0x5C864098) Value:0x5ad37400 Cost:(0/0)
2004-10-11 15:32:17.87 spid4    
Deadlock encountered .... Printing deadlock information
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Wait-for graph
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:1
2004-10-11 15:32:17.87 spid4     Port: 0x42c00100  Xid Slot: 0, EC: 0x47a7f520, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-10-11 15:32:17.87 spid4        SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4     Coordinator: EC = 0x47a7f520, SPID: 61, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Consumer List::
2004-10-11 15:32:17.87 spid4     Consumer: Xid Slot: 0, EC = 0x47a7f520, SPID: 61, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Producer List::
2004-10-11 15:32:17.87 spid4     Producer: Xid Slot: 6, EC = 0x5c2bc098, SPID: 61, ECID: 6, Blocking
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:2
2004-10-11 15:32:17.87 spid4     PAG: 6:65:71632                CleanCnt:2 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Wait List:
2004-10-11 15:32:17.87 spid4        Owner:0x5acfaa60 Mode: IX       Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 58 ECID: 0 Statement Type: INSERT Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_execute;1
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:6 Ec:(0x5C2BC098) Value:0x5ad36f80 Cost:(0/0)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:3
2004-10-11 15:32:17.87 spid4     PAG: 6:65:71632                CleanCnt:2 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Grant List 1::
2004-10-11 15:32:17.87 spid4        Owner:0x5ab79200 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:73 ECID:0
2004-10-11 15:32:17.87 spid4        SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
2004-10-11 15:32:17.87 spid4        Input Buf: RPC Event: sp_prepexec;1
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:58 ECID:0 Ec:(0x47CBF520) Value:0x5acfaa60 Cost:(0/260)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:4
2004-10-11 15:32:17.87 spid4     PAG: 6:65:1789710              CleanCnt:1 Mode: S Flags: 0x2
2004-10-11 15:32:17.87 spid4      Grant List 0::
2004-10-11 15:32:17.87 spid4        Owner:0x5ab41380 Mode: S        Flg:0x0 Ref:0 Life:00000001 SPID:56 ECID:0
2004-10-11 15:32:17.87 spid4      Grant List 1::
2004-10-11 15:32:17.87 spid4      Grant List 3::
2004-10-11 15:32:17.87 spid4      Requested By:
2004-10-11 15:32:17.87 spid4        ResType:LockOwner Stype:'OR' Mode: IX SPID:73 ECID:0 Ec:(0x49031520) Value:0x5b0aae40 Cost:(0/5C)
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     -- next branch --
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4    
2004-10-11 15:32:17.87 spid4     Node:1
2004-10-11 15:32:17.87 spid4     Port: 0x42c00100  Xid Slot: 0, EC: 0x47a7f520, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2004-10-11 15:32:17.87 spid4     Coordinator: EC = 0x47a7f520, SPID: 61, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Consumer List::
2004-10-11 15:32:17.87 spid4     Consumer: Xid Slot: 0, EC = 0x47a7f520, SPID: 61, ECID: 0, Not Blocking
2004-10-11 15:32:17.87 spid4     Producer List::
2004-10-11 15:32:17.87 spid4     Producer: Xid Slot: 6, EC = 0x5c2bc098, SPID: 61, ECID: 6, Blocking
2004-10-11 15:32:17.87 spid4     Victim Resource Owner:
2004-10-11 15:32:17.87 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:61 ECID:6 Ec:(0x5C2BC098) Value:0x5ad36f80 Cost:(0/0)
Avatar of praveen_ms
praveen_ms
Best way to check when you have deadlock is going to SQL Enterprise manager and looking into Management Current Actvity and and Locks/Process Info You will see there the blocking process and blovcked process. You can also see the properties on process ID to see what sql statement is being executed.

You can then kills the blocking process.

You can also check in SQL Server Logs under the same section to see if see any deadlock messages.

Cheers
Praveen
Avatar of Gbemisola4
Gbemisola4

ASKER

I have kill the process but am still getting deadlocking, the error email above are what is in the SQL Error log.
Could you please explain further the  error log set to you .
Because from the error i can see that the problem is comming from update statement but to trace the log further is my problem

Please explain further ther error above
Thanks
Avatar of arbert
arbert
"You can then kills the blocking process. "

Killing the process doesn't correct the problem.

Have you looked at the SPID id at the time to see what application is running and what the statement is that's running?
Avatar of Gbemisola4
Gbemisola4

ASKER

Pleasre help me
How do i link a page number  to a particular table.
Could you pleaase give me the script to do that

Assumming page number is 23456

do you link it up in sysobjects  
could you please write a script to do it
Avatar of Gbemisola4
Gbemisola4

ASKER

please help
on the below table am having deadlock on OIDEBTL3_IX which its noncluster index
the reference objid is reference to OIDEBTL3_IX
how do i resolve this problem ,is it to do with the table created and the way the index is created .
please help and tellme the way round it , i dont want to drop the index

what can be done to afford deadlock on this table and why
this is the result from sp_lock

spid   dbid   ObjId       IndId  Type Resource         Mode     Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51     6      0           0      DB                    S        GRANT
51     6      2087678485  2      KEY  (8c015900eacb)   U        WAIT
51     6      2087678485  2      PAG  65:65992         IU       GRANT
51     6      2087678485  0      TAB                   IX       GRANT
52     6      0           0      DB                    S        GRANT
52     6      2087678485  2      KEY  (8c015900eacb)   U        WAIT
52     6      2087678485  2      PAG  65:65992         IU       GRANT
52     6      2087678485  0      TAB                   IX       GRANT
53     6      0           0      DB                    S        GRANT
54     6      2087678485  2      KEY  (8c015900eacb)   U        WAIT
54     6      0           0      DB                    S        GRANT
54     6      2087678485  2      PAG  65:65992         IU       GRANT
54     6      2087678485  0      TAB                   IX       GRANT
55     6      2087678485  2      KEY  (8c015900eacb)   U        WAIT
55     6      0           0      DB                    S        GRANT
55     6      2087678485  2      PAG  65:65992         IU       GRANT
55     6      2087678485  0      TAB                   IX       GRANT
56     6      0           0      DB                    S        GRANT
56     6      2087678485  2      KEY  (8c015900eacb)   U        WAIT
56     6      2087678485  2      PAG  65:65992         IU       GRANT
56     6      2087678485  0      TAB                   IX       GRANT
58     6      2087678485  2      KEY  (8c015900eacb)   U        WAIT
58     6      0           0      DB                    S        GRANT


CREATE TABLE OIDEBTP (
  OIXRCE  CHAR     (6)      NOT NULL,
  OICVCD  CHAR     (10)     NOT NULL,
  OIBRNC  NUMERIC  (7)      NOT NULL,
  OIE5CD  NUMERIC  (9)      NOT NULL,
  OIDHNB  NUMERIC  (7)      NOT NULL,
  OIB9NE  NUMERIC  (3)      NOT NULL,
  OIKCSV  CHAR     (1)      NOT NULL,
  OIX8CE  CHAR     (6)      NOT NULL,
  OIX9CE  CHAR     (10)     NOT NULL,
  OIBXNC  NUMERIC  (7)      NOT NULL,
  OIALCU  NUMERIC  (8)      NOT NULL,
  OIAMCU  NUMERIC  (8)      NOT NULL,
  OIGUCU  NUMERIC  (8)      NOT NULL,
  OIB8ND  NUMERIC  (4)      NOT NULL,
  OIB3SS  CHAR     (1)      NOT NULL,
  OIJHNC  NUMERIC  (2)      NOT NULL,
  OIXBCF  CHAR     (2)      NOT NULL,
  OIDHCE  CHAR     (3)      NOT NULL,
  OIXCCF  CHAR     (3)      NOT NULL,
  OIP4ND  NUMERIC  (6,1)    NOT NULL,
  OIAJTY  CHAR     (30)     NOT NULL,
  OIJLCF  CHAR     (6)      NOT NULL,
  OIS3TY  CHAR     (30)     NOT NULL,
  OID4SV  CHAR     (1)      NOT NULL,
  OIAJVC  NUMERIC  (11,2)   NOT NULL,
  OIX6TZ  CHAR     (20)     NOT NULL,
  OIAKVC  NUMERIC  (11,2)   NOT NULL,
  OIX7TZ  CHAR     (20)     NOT NULL,
  OIALVC  NUMERIC  (11,2)   NOT NULL,
  OIX8TZ  CHAR     (20)     NOT NULL,
  OIAMVC  NUMERIC  (11,2)   NOT NULL,
  OIX9TZ  CHAR     (20)     NOT NULL,
  OIANVC  NUMERIC  (11,2)   NOT NULL,
  OIYATZ  CHAR     (20)     NOT NULL,
  OIJMVC  NUMERIC  (11,2)   NOT NULL,
  OIW8CF  CHAR     (20)     NOT NULL,
  OIA2CG  CHAR     (3)      NOT NULL,
  OIA3CG  CHAR     (3)      NOT NULL,
  OIYMVN  CHAR     (10)     NOT NULL,
  OID5SV  CHAR     (1)      NOT NULL,
  OIKDSV  CHAR     (1)      NOT NULL,
  OIB8SW  CHAR     (1)      NOT NULL,
  OIB9SW  CHAR     (1)      NOT NULL,
  OICASW  CHAR     (1)      NOT NULL,
  OICBSW  CHAR     (1)      NOT NULL,
  OICCSW  CHAR     (1)      NOT NULL,
  OIJNVC  NUMERIC  (11,2)   NOT NULL,
  OIJOVC  NUMERIC  (11,2)   NOT NULL,
  OIJPVC  NUMERIC  (11,2)   NOT NULL,
  OIUTCU  NUMERIC  (8)      NOT NULL,
  OIUUCU  NUMERIC  (8)      NOT NULL,
  OIUVCU  NUMERIC  (8)      NOT NULL,
  OIAAVN  CHAR     (10)     NOT NULL,
  OIUEDT  NUMERIC  (7)      NOT NULL,
  OIACTM  NUMERIC  (6)      NOT NULL,
  OIUDCT  CHAR     (10)     NOT NULL,
  OIUECT  NUMERIC  (7)      NOT NULL,
  OIUATM  NUMERIC  (6)      NOT NULL ) ON [Data]  

ALTER TABLE OIDEBTP ADD CONSTRAINT OIDEBTP_PK PRIMARY KEY (
  OIXRCE,
  OICVCD,
  OIBRNC)

CREATE INDEX OIDEBTL3_IX ON OIDEBTP (
  OIB3SS,
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]
 
CREATE INDEX OIDEBTL4_IX ON OIDEBTP (
  OIE5CD,
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]
 
CREATE INDEX OIDEBTL6_IX ON OIDEBTP (
  OIXRCE,
  OIJHNC,
  OIB3SS) ON [Index]
 
CREATE INDEX OIDEBTV0_IX ON OIDEBTP (
  OICVCD,
  OIJHNC,
  OIAJVC,
  OIX6TZ,
  OIAKVC,
  OIX7TZ,
  OIALVC,
  OIX8TZ,
  OIAMVC,
  OIX9TZ,
  OIANVC,
  OIYATZ) ON [Index]
 
CREATE INDEX OIDEBTV5_IX ON OIDEBTP (
  OICVCD,
  OIXRCE,
  OIJHNC,
  OIALCU,
  OIBRNC) ON [Index]
Avatar of arbert
arbert
I'll ask my question again, have you identified the process and the application that's causing the problem???
Avatar of Gbemisola4
Gbemisola4

ASKER

the application is a weblogic , the problem arises when update was been commited at the same time by muiltiple users
Avatar of arbert
arbert
Post the update code...Is there a clustered index on the primary key?
Avatar of Gbemisola4
Gbemisola4

ASKER

i have managed to narrow the problem down to the table and indexes causing the problem.the below indexes is causing the problem when the OIB3SS columns is on i get deadlock
CREATE INDEX OIDEBTL3_IX ON OIDEBTP (
  OIB3SS,
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]

As soon as i created the below indexes without OIB3SS

CREATE INDEX OIDEBTMHG_IX ON OIDEBTP (
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]

that solve the problem of deadlocking .

but if  recreated the below indexes as it is without columns OIB3SS

CREATE INDEX OIDEBTL3_IX ON OIDEBTP (
  OIB3SS,
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]
thee deadlock occurs again even though the below i trace is showing that  the OIB3SS is not in use

UPDATE oidebtp SET oie5cd = 8788 , oidhnb = 21 , oib9ne = 1 , oikcsv = 'K' , oix8ce = ' ' , oix9ce = ' ' ,
                   oibxnc = 0 , oialcu = 20160101 , oiamcu = 0 , oigucu = 0 , oib8nd = 0 , oib3ss = 'U' ,
                   oijhnc = 99 , oixbcf = ' ' , oidhce = 'RGN', oixccf = 'LB0' , oip4nd = 310.0 , oiajty = 'AK1' ,
                   oijlcf = ' ' , ois3ty = ' ' , oid4sv = ' ' , oiajvc = 0.00 , oix6tz = ' ' , oiakvc = 0.00 ,
                   oix7tz = ' ' , oialvc = 0.00 , oix8tz = ' ' , oiamvc = 0.00 , oix9tz = ' ' , oianvc = 0.00 ,
                   oiyatz = ' ' , oijmvc = '100.00' , oiw8cf = ' ' , oia2cg = ' ' , oia3cg = 'GBP' , oiymvn = ' ' ,
                   oid5sv = ' ' , oikdsv = ' ' , oib8sw = ' ' , oib9sw = ' ' , oicasw = ' ' , oicbsw = ' ' ,
                   oiccsw = ' ' , oijnvc = 0.00 , oijovc = 0.00                                                                                                                                                                                                                                                                 , oijpvc = 0.00 , oiutcu = 0 , oiuucu = 0 , oiuvcu = 0 , oiaavn = 'T8BCH05R' , oiuedt = 1040925 , oiactm = 111034 , oiudct = 'T8BCH05R' , oiuect = 1040925 , oiuatm = 111034
WHERE oixrce = 'GROUP2'  AND oicvcd = 'GROUP2'  AND oibrnc = 2787000

Execution Tree
--------------
Clustered Index Update(OBJECT:([wescotramp].[dbo].[OIDEBTP].[OIDEBTP_PK]), SET:([OIDEBTP].[OIUATM]=RaiseIfNull(Convert([@P55])), [OIDEBTP].[OIUECT]=RaiseIfNull(Convert([@P54])), [OIDEBTP].[OIUDCT]=RaiseIfNull(Convert([@P53])), [OIDEBTP].[OIACTM]=RaiseIfNull(Convert([@P52])), [OIDEBTP].[OIUEDT]=RaiseIfNull(Convert([@P51])), [OIDEBTP].[OIAAVN]=RaiseIfNull(Convert([@P50])), [OIDEBTP].[OIUVCU]=RaiseIfNull(Convert([@P49])), [OIDEBTP].[OIUUCU]=RaiseIfNull(Convert([@P48])), [OIDEBTP].[OIUTCU]=RaiseIfNull(Convert([@P47])), [OIDEBTP].[OIJPVC]=RaiseIfNull(Convert([@P46])), [OIDEBTP].[OIJOVC]=RaiseIfNull(Convert([@P45])), [OIDEBTP].[OIJNVC]=RaiseIfNull(Convert([@P44])), [OIDEBTP].[OICCSW]=RaiseIfNull(Convert([@P43])), [OIDEBTP].[OICBSW]=RaiseIfNull(Convert([@P42])), [OIDEBTP].[OICASW]=RaiseIfNull(Convert([@P41])), [OIDEBTP].[OIB9SW]=RaiseIfNull(Convert([@P40])), [OIDEBTP].[OIB8SW]=RaiseIfNull(Convert([@P39])), [OIDEBTP].[OIKDSV]=RaiseIfNull(Convert([@P38])), [OIDEBTP].[OID5SV]=RaiseIfNull(Convert([@P37])), [OIDEBTP].[OIYMVN]=RaiseIfNull(Convert([@P36])), [OIDEBTP].[OIA3CG]=RaiseIfNull(Convert([@P35])), [OIDEBTP].[OIA2CG]=RaiseIfNull(Convert([@P34])), [OIDEBTP].[OIW8CF]=RaiseIfNull(Convert([@P33])), [OIDEBTP].[OIJMVC]=RaiseIfNull(Convert([@P32])), [OIDEBTP].[OIYATZ]=RaiseIfNull(Convert([@P31])), [OIDEBTP].[OIANVC]=RaiseIfNull(Convert([@P30])), [OIDEBTP].[OIX9TZ]=RaiseIfNull(Convert([@P29])), [OIDEBTP].[OIAMVC]=RaiseIfNull(Convert([@P28])), [OIDEBTP].[OIX8TZ]=RaiseIfNull(Convert([@P27])), [OIDEBTP].[OIALVC]=RaiseIfNull(Convert([@P26])), [OIDEBTP].[OIX7TZ]=RaiseIfNull(Convert([@P25])), [OIDEBTP].[OIAKVC]=RaiseIfNull(Convert([@P24])), [OIDEBTP].[OIX6TZ]=RaiseIfNull(Convert([@P23])), [OIDEBTP].[OIAJVC]=RaiseIfNull(Convert([@P22])), [OIDEBTP].[OID4SV]=RaiseIfNull(Convert([@P21])), [OIDEBTP].[OIS3TY]=RaiseIfNull(Convert([@P20])), [OIDEBTP].[OIJLCF]=RaiseIfNull(Convert([@P19])), [OIDEBTP].[OIAJTY]=RaiseIfNull(Convert([@P18])), [OIDEBTP].[OIP4ND]=RaiseIfNull(Convert([@P17])), [OIDEBTP].[OIXCCF]=RaiseIfNull(Convert([@P16])), [OIDEBTP].[OIDHCE]=RaiseIfNull(Convert([@P15])), [OIDEBTP].[OIXBCF]=RaiseIfNull(Convert([@P14])), [OIDEBTP].[OIJHNC]=RaiseIfNull(Convert([@P13])), [OIDEBTP].[OIB3SS]=RaiseIfNull(Convert([@P12])), [OIDEBTP].[OIB8ND]=RaiseIfNull(Convert([@P11])), [OIDEBTP].[OIGUCU]=RaiseIfNull(Convert([@P10])), [OIDEBTP].[OIAMCU]=RaiseIfNull(Convert([@P9])), [OIDEBTP].[OIALCU]=RaiseIfNull(Convert([@P8])), [OIDEBTP].[OIBXNC]=RaiseIfNull(Convert([@P7])), [OIDEBTP].[OIX9CE]=RaiseIfNull(Convert([@P6])), [OIDEBTP].[OIX8CE]=RaiseIfNull(Convert([@P5])), [OIDEBTP].[OIKCSV]=RaiseIfNull(Convert([@P4])), [OIDEBTP].[OIB9NE]=RaiseIfNull(Convert([@P3])), [OIDEBTP].[OIDHNB]=RaiseIfNull(Convert([@P2])), [OIDEBTP].[OIE5CD]=RaiseIfNull(Convert([@P1]))))
  |--Compute Scalar(DEFINE:([ConstExpr1060]=Convert([@P1]), [ConstExpr1061]=Convert([@P2]), [ConstExpr1062]=Convert([@P3]), [ConstExpr1063]=Convert([@P4]), [ConstExpr1064]=Convert([@P5]), [ConstExpr1065]=Convert([@P6]), [ConstExpr1066]=Convert([@P7]), [ConstExpr1067]=Convert([@P8]), [ConstExpr1068]=Convert([@P9]), [ConstExpr1069]=Convert([@P10]), [ConstExpr1070]=Convert([@P11]), [ConstExpr1071]=Convert([@P12]), [ConstExpr1072]=Convert([@P13]), [ConstExpr1073]=Convert([@P14]), [ConstExpr1074]=Convert([@P15]), [ConstExpr1075]=Convert([@P16]), [ConstExpr1076]=Convert([@P17]), [ConstExpr1077]=Convert([@P18]), [ConstExpr1078]=Convert([@P19]), [ConstExpr1079]=Convert([@P20]), [ConstExpr1080]=Convert([@P21]), [ConstExpr1081]=Convert([@P22]), [ConstExpr1082]=Convert([@P23]), [ConstExpr1083]=Convert([@P24]), [ConstExpr1084]=Convert([@P25]), [ConstExpr1085]=Convert([@P26]), [ConstExpr1086]=Convert([@P27]), [ConstExpr1087]=Convert([@P28]), [ConstExpr1088]=Convert([@P29]), [ConstExpr1089]=Convert([@P30]), [ConstExpr1090]=Convert([@P31]), [ConstExpr1091]=Convert([@P32]), [ConstExpr1092]=Convert([@P33]), [ConstExpr1093]=Convert([@P34]), [ConstExpr1094]=Convert([@P35]), [ConstExpr1095]=Convert([@P36]), [ConstExpr1096]=Convert([@P37]), [ConstExpr1097]=Convert([@P38]), [ConstExpr1098]=Convert([@P39]), [ConstExpr1099]=Convert([@P40]), [ConstExpr1100]=Convert([@P41]), [ConstExpr1101]=Convert([@P42]), [ConstExpr1102]=Convert([@P43]), [ConstExpr1103]=Convert([@P44]), [ConstExpr1104]=Convert([@P45]), [ConstExpr1105]=Convert([@P46]), [ConstExpr1106]=Convert([@P47]), [ConstExpr1107]=Convert([@P48]), [ConstExpr1108]=Convert([@P49]), [ConstExpr1109]=Convert([@P50]), [ConstExpr1110]=Convert([@P51]), [ConstExpr1111]=Convert([@P52]), [ConstExpr1112]=Convert([@P53]), [ConstExpr1113]=Convert([@P54]), [ConstExpr1114]=Convert([@P55])))
       |--Table Spool
            |--Top(ROWCOUNT est 0)
                 |--Parallelism(Gather Streams)
                      |--Filter(WHERE:(Convert([OIDEBTP].[OIXRCE])=[@P56] AND Convert([OIDEBTP].[OICVCD])=[@P57]))
                           |--Index Scan(OBJECT:([wescotramp].[dbo].[OIDEBTP].[OIDEBTL3_IX]),  WHERE:([OIDEBTP].[OIBRNC]=Convert([@P58]))


please help
Avatar of Gbemisola4
Gbemisola4

ASKER

that might help the below script is the table created.

CREATE TABLE OIDEBTP (
  OIXRCE  CHAR     (6)      NOT NULL,
  OICVCD  CHAR     (10)     NOT NULL,
  OIBRNC  NUMERIC  (7)      NOT NULL,
  OIE5CD  NUMERIC  (9)      NOT NULL,
  OIDHNB  NUMERIC  (7)      NOT NULL,
  OIB9NE  NUMERIC  (3)      NOT NULL,
  OIKCSV  CHAR     (1)      NOT NULL,
  OIX8CE  CHAR     (6)      NOT NULL,
  OIX9CE  CHAR     (10)     NOT NULL,
  OIBXNC  NUMERIC  (7)      NOT NULL,
  OIALCU  NUMERIC  (8)      NOT NULL,
  OIAMCU  NUMERIC  (8)      NOT NULL,
  OIGUCU  NUMERIC  (8)      NOT NULL,
  OIB8ND  NUMERIC  (4)      NOT NULL,
  OIB3SS  CHAR     (1)      NOT NULL,
  OIJHNC  NUMERIC  (2)      NOT NULL,
  OIXBCF  CHAR     (2)      NOT NULL,
  OIDHCE  CHAR     (3)      NOT NULL,
  OIXCCF  CHAR     (3)      NOT NULL,
  OIP4ND  NUMERIC  (6,1)    NOT NULL,
  OIAJTY  CHAR     (30)     NOT NULL,
  OIJLCF  CHAR     (6)      NOT NULL,
  OIS3TY  CHAR     (30)     NOT NULL,
  OID4SV  CHAR     (1)      NOT NULL,
  OIAJVC  NUMERIC  (11,2)   NOT NULL,
  OIX6TZ  CHAR     (20)     NOT NULL,
  OIAKVC  NUMERIC  (11,2)   NOT NULL,
  OIX7TZ  CHAR     (20)     NOT NULL,
  OIALVC  NUMERIC  (11,2)   NOT NULL,
  OIX8TZ  CHAR     (20)     NOT NULL,
  OIAMVC  NUMERIC  (11,2)   NOT NULL,
  OIX9TZ  CHAR     (20)     NOT NULL,
  OIANVC  NUMERIC  (11,2)   NOT NULL,
  OIYATZ  CHAR     (20)     NOT NULL,
  OIJMVC  NUMERIC  (11,2)   NOT NULL,
  OIW8CF  CHAR     (20)     NOT NULL,
  OIA2CG  CHAR     (3)      NOT NULL,
  OIA3CG  CHAR     (3)      NOT NULL,
  OIYMVN  CHAR     (10)     NOT NULL,
  OID5SV  CHAR     (1)      NOT NULL,
  OIKDSV  CHAR     (1)      NOT NULL,
  OIB8SW  CHAR     (1)      NOT NULL,
  OIB9SW  CHAR     (1)      NOT NULL,
  OICASW  CHAR     (1)      NOT NULL,
  OICBSW  CHAR     (1)      NOT NULL,
  OICCSW  CHAR     (1)      NOT NULL,
  OIJNVC  NUMERIC  (11,2)   NOT NULL,
  OIJOVC  NUMERIC  (11,2)   NOT NULL,
  OIJPVC  NUMERIC  (11,2)   NOT NULL,
  OIUTCU  NUMERIC  (8)      NOT NULL,
  OIUUCU  NUMERIC  (8)      NOT NULL,
  OIUVCU  NUMERIC  (8)      NOT NULL,
  OIAAVN  CHAR     (10)     NOT NULL,
  OIUEDT  NUMERIC  (7)      NOT NULL,
  OIACTM  NUMERIC  (6)      NOT NULL,
  OIUDCT  CHAR     (10)     NOT NULL,
  OIUECT  NUMERIC  (7)      NOT NULL,
  OIUATM  NUMERIC  (6)      NOT NULL ) ON [Data]  

ALTER TABLE OIDEBTP ADD CONSTRAINT OIDEBTP_PK PRIMARY KEY (
  OIXRCE,
  OICVCD,
  OIBRNC)

CREATE INDEX OIDEBTL3_IX ON OIDEBTP (
  OIB3SS,
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]
 
CREATE INDEX OIDEBTL4_IX ON OIDEBTP (
  OIE5CD,
  OIXRCE,
  OICVCD,
  OIBRNC) ON [Index]
 
CREATE INDEX OIDEBTL6_IX ON OIDEBTP (
  OIXRCE,
  OIJHNC,
  OIB3SS) ON [Index]
 
CREATE INDEX OIDEBTV0_IX ON OIDEBTP (
  OICVCD,
  OIJHNC,
  OIAJVC,
  OIX6TZ,
  OIAKVC,
  OIX7TZ,
  OIALVC,
  OIX8TZ,
  OIAMVC,
  OIX9TZ,
  OIANVC,
  OIYATZ) ON [Index]
 
CREATE INDEX OIDEBTV5_IX ON OIDEBTP (
  OICVCD,
  OIXRCE,
  OIJHNC,
  OIALCU,
  OIBRNC) ON [Index]
Avatar of arbert
arbert
How many rows does the update affect (approximately) and what is the other statement that's involved in the deadlock scenario?
Avatar of Gbemisola4
Gbemisola4

ASKER

 rows in OIDEBTP tables are 9008006

If you look on the SQL Error log , that will give you more info

Thanks
ASKER CERTIFIED SOLUTION
Avatar of arbert
arbert
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial