Solved

Reading SQL Trace

Posted on 2004-10-12
16
590 Views
Last Modified: 2012-06-27
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)
0
Comment
Question by:Gbemisola4
  • 7
  • 5
16 Comments
 
LVL 2

Expert Comment

by:praveen_ms
ID: 12285352
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
0
 

Author Comment

by:Gbemisola4
ID: 12285468
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
0
 
LVL 34

Expert Comment

by:arbert
ID: 12294628
"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?
0
 

Author Comment

by:Gbemisola4
ID: 12296377
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
0
 

Author Comment

by:Gbemisola4
ID: 12298633
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]
0
 
LVL 34

Expert Comment

by:arbert
ID: 12298969
I'll ask my question again, have you identified the process and the application that's causing the problem???
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Gbemisola4
ID: 12299351
the application is a weblogic , the problem arises when update was been commited at the same time by muiltiple users
0
 
LVL 34

Expert Comment

by:arbert
ID: 12299428
Post the update code...Is there a clustered index on the primary key?
0
 

Author Comment

by:Gbemisola4
ID: 12306452
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
0
 

Author Comment

by:Gbemisola4
ID: 12306489
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]
0
 
LVL 34

Expert Comment

by:arbert
ID: 12307731
How many rows does the update affect (approximately) and what is the other statement that's involved in the deadlock scenario?
0
 

Author Comment

by:Gbemisola4
ID: 12308182
 rows in OIDEBTP tables are 9008006

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

Thanks
0
 
LVL 34

Accepted Solution

by:
arbert earned 500 total points
ID: 12314955
The error log does not give the action/statement the other transaction was performing...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now