Re: determining bind values in deadlock situations

The big question is how do you know you are getting a deadlock?

ORA-60, RAC or not will still get generated. It may take longer in RAC because the detection method does change. However you can still get non-table deadlocks in RAC that are not always dumped in to a tracefile. Then you need to run hanganalyze or a systemstate dump and look for open chains. There serveral bugs in 10g that related to library cache related deadlocks that are not always indicated in a tracefile.

Scott




________________________________
From: "Barun, Vlado" <Vlado.Barun_at_JTV.com>
To: Jared Still <jkstill_at_gmail.com>
Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Sent: Monday, March 2, 2009 7:21:12 PM
Subject: RE: determining bind values in deadlock situations

 
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