RE: determining bind values in deadlock situations

  • From: "Barun, Vlado" <Vlado.Barun@xxxxxxx>
  • To: Jared Still <jkstill@xxxxxxxxx>
  • Date: Mon, 2 Mar 2009 21:21:12 -0500

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@xxxxxxx

From: Jared Still [mailto:jkstill@xxxxxxxxx]
Sent: Monday, March 02, 2009 1:28 PM
To: Barun, Vlado
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: determining bind values in deadlock situations


On Fri, Feb 27, 2009 at 5:54 PM, Barun, Vlado 
<Vlado.Barun@xxxxxxx<mailto:Vlado.Barun@xxxxxxx>> 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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2009