RE: determining bind values in deadlock situations

From: Barun, Vlado <Vlado.Barun_at_JTV.com>
Date: Mon, 2 Mar 2009 21:21:12 -0500
Message-ID: <0181A069127F1944B484ED5B858D0C160C119877FB_at_KPMSPW02.jewelry.acn>

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<mailto: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

Received on Mon Mar 02 2009 - 20:21:12 CST