RE: determining bind values in deadlock situations
From: Barun, Vlado <Vlado.Barun_at_JTV.com>
Date: Tue, 3 Mar 2009 09:44:40 -0500
Message-ID: <0181A069127F1944B484ED5B858D0C160C11987885_at_KPMSPW02.jewelry.acn>
Ujang, thanks for confirming that the deadlock trace files are different between non-RAC and RAC.
Date: Tue, 3 Mar 2009 09:44:40 -0500
Message-ID: <0181A069127F1944B484ED5B858D0C160C11987885_at_KPMSPW02.jewelry.acn>
Ujang, thanks for confirming that the deadlock trace files are different between non-RAC and RAC.
I can't change it to non-RAC since it is production.
So, any other ideas how to get the bind variables?
Regards,
Vlado Barun, M.Sc.
Sr. Manager, Database Engineering and Operations
Jewelry Television
Mobile: 865 335 7652
Email: vlado.barun_at_jtv.com
-----Original Message-----
From: Ujang Jaenudin [mailto:ujang.jaenudin_at_gmail.com]
Sent: Tuesday, March 03, 2009 1:24 AM
To: Barun, Vlado
Cc: oracle-l_at_freelists.org
Subject: Re: determining bind values in deadlock situations
vlado,
I was have the same problem on RAC 10.2.0.3 and yes there is no rowid information in the trace file.
due to the dev environment, I have changed it to single instance and got the row.
-- thanks and regards ujang | oracle dba | mysql dba http://ora62.wordpress.com On Tue, Mar 3, 2009 at 9:21 AM, Barun, Vlado <Vlado.Barun_at_jtv.com> wrote:Received on Tue Mar 03 2009 - 08:44:40 CST
> Jared,
>
>
>
> Was your test done in a non-RAC environment?
>
> I can find the rowid's the way you mentioned in a non-RAC environment, but
> not in a RAC environment.
>
>
>
> There is no trace file generated for a deadlock in udump on any of my RAC
> nodes.
>
>
>
> Are you able to get a deadlock trace file in a RAC environment?
>
>
>
> Thank you for the time you are spending on this.
>
>
>
> Regards,
>
>
>
> Vlado Barun, M.Sc.
>
> Sr. Manager, Database Engineering and Operations
>
> Jewelry Television
>
> Mobile: 865 335 7652
>
> Email: vlado.barun_at_jtv.com
>
>
>
> From: Jared Still [mailto:jkstill_at_gmail.com]
> Sent: Monday, March 02, 2009 1:28 PM
> To: Barun, Vlado
> Cc: oracle-l_at_freelists.org
> Subject: Re: determining bind values in deadlock situations
>
>
>
>
>
> On Fri, Feb 27, 2009 at 5:54 PM, Barun, Vlado <Vlado.Barun_at_jtv.com> wrote:
>
> Jared,
>
> However I have trouble finding the rowid's.
>
> Following are the relevant excerpts of the lmd0 trace files from both nodes.
> Where are the rowid's? Am I looking in the correct files?
>
>
>
> The deadlock trace file will be found in udump.
>
> Here's an example from 10.2.0.2 on Windows
>
> DEADLOCK DETECTED
> [Transaction Deadlock]
> Current SQL statement for this session:
> SELECT "SQLX" , "EDTX" , "DBNA" , "CLAS" , "TYPE" , "OCCURS" , "SUBC" ,
> "APPL" , "SECU" , "CNAM" , "CDAT" , "VERN" , "LEVL" , "RSTAT" , "RMAND" ,
> "RLOAD" , "UNAM" , "UDAT" , "UTIME" , "DATALG" , "VARCL" , "DBAPL" , "FIXPT"
> , "SSET" , "SDATE" , "STIME" , "IDATE" , "ITIME" , "LDBNAME" , "UCCHECK" ,
> "MAXLINELN" FROM "REPOSRC" WHERE "PROGNAME" = :A0 AND "R3STATE" = :A1 FOR
> UPDATE
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session holds
> waits
> TX-000a002f-00039963 55 367 X 48
> 358 X
> TX-00030005-00007864 48 358 X 55
> 367 X
> session 367: DID 0001-0037-00000004 session 358: DID 0001-0030-00000004
> session 358: DID 0001-0030-00000004 session 367: DID 0001-0037-00000004
> Rows waited on:
> Session 358: obj - rowid = 00016AC6 - AAAWrGAAbAAGT9VAAE
> (dictionary objn - 92870, file - 27, block - 1654613, slot - 4)
> Session 367: obj - rowid = 00016AC6 - AAAWrGAAbAAGT9VAAD
> (dictionary objn - 92870, file - 27, block - 1654613, slot - 3)
> Information on the OTHER waiting sessions:
> Session 358:
> pid=48 serial=17 audsid=1283076 user: NN/SAPR3
> O/S info: user: SapServiceXXX, term: MACHINE , ospid: NNN:NNNN, machine:
> domain\machine
> program: disp+work.EXE
> client info: 0
> application name:XXXXXXXX , hash
> value=888649399
> action name: 389, hash value=1894668767
> Current SQL Statement:
>
> SELECT "SQLX" , "EDTX" , "DBNA" , "CLAS" , "TYPE" , "OCCURS" , "SUBC" ,
> "APPL" , "SECU" , "CNAM" , "CDAT" , "VERN" , "LEVL" , "RSTAT" , "RMAND" ,
> "RLOAD" , "UNAM" , "UDAT" , "UTIME" , "DATALG" , "VARCL" , "DBAPL" , "FIXPT"
> , "SSET" , "SDATE" , "STIME" , "IDATE" , "ITIME" , "LDBNAME" , "UCCHECK" ,
> "MAXLINELN" FROM "REPOSRC" WHERE "PROGNAME" = :A0 AND "R3STATE" = :A1 FOR
> UPDATE
> End of information on OTHER waiting sessions.
>
>
> Jared
-- http://www.freelists.org/webpage/oracle-l