RE: determining bind values in deadlock situations

From: "Joel Wittenmyer" <joel.wittenmyer@xxxxxxxxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 05 Mar 2009 16:55:27 -0600
It was a stand-alone environment in each case, and of course I only had
the statements and variables for the process that received the ORA-60,
and the statement from the second process that caused the deadlock.  The
Process State dump in the trace file will show you all the statements
that the process has issued, ordered by 'savetime' (iirc).  (Note:
'savetime' values wrap.)

For a RAC environment, use the methods given previously to get to the
individual process files.  From there the methods are the same.

Anyway, setting out all the statements and variable values for one
process, in order, along with the locking statement from the other
process allowed the development team to determine how they got to where
they were, and fix the issue.



So, how did you get all the statements and bind variables for a
deadlock, in a RAC environment?
> 
> Regards,
>  
> Vlado Barun, M.Sc.
> Sr. Manager, Database Engineering and Operations
> Jewelry Television
> Mobile: 865 335 7652
> Email: vlado.barun@xxxxxxx
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joel Wittenmyer
> Sent: Thursday, March 05, 2009 2:58 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: determining bind values in deadlock situations
> 
> 'Application Error' is just one of many reasons you can get a deadlock.
>  The bind variables help you to understand exactly what the application
> was doing.  Even if it is in fact an application errors, the bind
> variable values in conjunction with the rest of the information in the
> Process State often helps the developers figure out exactly how they got
> themselves into that situation.  It can also tell you if the application
> caught the error and re-tried the statement (if a DML) or not.  Which is
> to say that you can tell if you have lost data.  By laying out the
> entire sequence of events that one process did, along with the bind
> variable values, for the VP in one company we resolved a deadlock that
> the company had been plagued by for years and had given up hope of
> resolving.  For another company, I showed them that they were losing
> about 100 records / day.
> 
> > 
> > On Fri, Feb 20, 2009 at 5:33 PM, Barun, Vlado <Vlado.Barun@xxxxxxx>
wrote:
> > 
> > >  Environment: 10.2.0.3 RAC/ASM on Solaris 10
> > >
> > > I'm trying to find an efficient way to get the bind values for
> statements
> > > that are involved in a deadlock situation.
> > >
> > >
> > Maybe I am just being particularly dense today, but I can't think of why
> > you would need bind variables to resolve a deadlock.
> > 
> > The ORA-60 deadlock is caused by an application error, typically two
> > different parts of an application causing a deadly embrace by trying
> > to lock the same tables, but in different orders.
> > 
> > The trace file generated by the ORA-60 contains the SQL and the
> > rowid's of the affected rows.
> > 
> > Is there something I am missing?
> > 
> > Jared Still
> > Certifiable Oracle DBA and Part Time Perl Evangelist
> > 
> > 
> 
> -- 
> 
> --
> //www.freelists.org/webpage/oracle-l
> 
> 
> 
> 

-- 

--
//www.freelists.org/webpage/oracle-l
RE: determining bind values in deadlock situations

Other related posts:
