RE: determining bind values in deadlock situations

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Mon, 2 Mar 2009 13:51:24 -0500
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F0D971B3D_at_AAPQMAILBX02V.proque.st>

Note:

A minor correction, if you'll allow me, Jared.

What Jared highlighted:
Session 358: obj - rowid = 00016AC6 - AAAWrGAAbAAGT9VAAE

Is actually the object id AND rowid. Note that the object id, in hex, is the part before the hyphen, 16AC6, and the remainder is actually the rowid: AAAWrGAAbAAGT9VAAE

So, if you convert the object id to decimal, you get 92870. If then you look up 92870 in your data dictionary: Select owner,object_type,object_name from dba_objects where data_object_id = 92870;

And then you can use:
Select * from whatever where rowid='AAAWrGAAbAAGT9VAAE';

To see the row that's involved in the deadlock.

Hope that helps,

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still 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 - 12:51:24 CST