RE: determining bind values in deadlock situations

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "jkstill@xxxxxxxxx" <jkstill@xxxxxxxxx>, "Barun, Vlado" <Vlado.Barun@xxxxxxx>
  • Date: Mon, 2 Mar 2009 13:51:24 -0500

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Jared Still
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: