RE: determining bind values in deadlock situations
Date: Thu, 5 Mar 2009 17:14:49 -0500
So, how did you get all the statements and bind variables for a deadlock, in a RAC environment?
Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations Jewelry Television
Mobile: 865 335 7652
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel Wittenmyer Sent: Thursday, March 05, 2009 2:58 PM
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_at_jtv.com> 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
> > 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