Solved

Reading SQL Trace

Posted on 2004-10-12
16
584 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

708 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