Re: Need HELP with Deadlocks

In article <8ec1n6$n4h$1_at_nnrp1.deja.com>,   miaemp_at_my-deja.com wrote:
> Is there some tool out there that will parse this trace file, or how
 or
> what do I look for in the trace file to determine the deadlock. Here
> is part of the trace file.
> Deadlock graph:
> ---------Blocker(s)-------- ---------Waiter
 (s)--
> -------
> Resource Name process session holds waits process session
> holds waits
> TX-001e0013-0000063d 73 116 X 33
> 87 S
> TX-000b0076-0000b2d2 33 87 X 73
> 116 S
> Rows waited on:
> Session 87: no row
> Session 116: no row
>
> To me, it appears that a row is not being waited on, it might be some
> sort of resource. Anyone have any other ideas?
>
> TIA
> Eric Peterson
> Programmer/Analyst DBA
> Maurices Inc.
> eric_NOpeterson_at_mauricesSPAMMERS.inrg.com
>
> Note that the opinions I may have expressed here are solely my own and
> not that of my employer's. To email me directly, remove the NO and
 the
> SPAMMERS from the address.
>
> In article <8ea8th$q84$1_at_nnrp1.deja.com>,
> Mark D Powell <markp7832_at_my-deja.com> wrote:
> > In article <8ea3r2$k2q$1_at_nnrp1.deja.com>,
> > miaemp_at_my-deja.com wrote:
> > > I am on 7.3.4.0 with AIX 4.2. Currently, we have a third party
 batch
> > > job which is logically threaded by a location and we are running
> > > several threads at once. We have been running this job for
 approx.
 18
> > > months with no deadlocking problems. I have checked all of the
 tables
> > > it is accessing to make sure that the foreign keys it has are
 indexed,
> > > so, that is not the problem. What we are experiencing are
 deadlocks,
> > > we find that the thread run fine for a short time, then, the
 deadlocks
> > > start to occur. I looked at the SQL each thread is running, all
 are
> > > trying to insert into the same table except for one, the one is
 trying
> > > to select from a different table. I have checked DBA_WAITERS and
 have
> > > found all the threads waiting. I have checked DBA_blockers and
 there
> > > are no rows returned. The one thread that is doing the select
 seems
 to
> > > be blocking all the others, and all the others are blocking the
 one
> > > that is doing the select, I have not been able to figure out why.
 The
> > > tables that are being inserted and selected, has the freelists set
 to
> > > 10 and the initrans set to 10 as well, so, I don't think it's a
 problem
> > > of trying to insert into the same block, but, maybe it is.
> > >
> > > Anyone have any suggestions as to how to solve this problem? I
 would
> > > greatly appreciate the help.
> > >
> > > TIA
> > > Eric Peterson
> > > Programmer/Analyst DBA
> > > Maurices Inc.
> > > eric_NOpeterson_at_mauricesSPAMMERS.inrgALLOWED.com
> > >
> > > Note that the views I may have expressed here are solely my own
 and
 not
> > > that of my employer's. To email me directly, remove the NO
 SPAMMERS
> > > ALLOWED from the email address.
> > >
> > I believe that Oracle deadlocks cause a trace file to the user dump
> > destination and this trace file contains the rowid of the row the
> > deadlock was on. You may to look for to see if such trace files
 exist
> > and if they are of any use.
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >

I can not find a copy of the post where I read how to find the information but since the trace file for the deadlock does exist try using tkprof on it just like it was a session trace file and see what happens. Please post if this works or fails.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.