RE: determining bind values in deadlock situations

From: Joel Wittenmyer <joel.wittenmyer_at_templarstable.com>
Date: Tue, 03 Mar 2009 10:25:52 -0600
Message-Id: <E1LeXRI-0002mt-R4_at_host.ckr-hosting.com>

In a non-RAC environment, get the 'handle' value for the State Object where you find your 'select' statement. Search the dump file for that handle. It will lead you to the cursor (near the bottom of the trace) and show you your bind variables.

The trace file for a RAC environment is actually an LMD trace file. To get the SQL in your trace file for a RAC environment then apply the patch for Bug 3268802. This fix is included in 9.2.0.6 and above and 10.1.0.3 and above. Once the problem has reproduced with this fix, search bdump and udump for files with the pids from the deadlock graph.  Using these files you will have information about the processes involved in the deadlock.

Alternatively, you could bump up the _lm_dd_interval and debug the TX locking with the racdiag.sql script (Note 135714.1). Fair warning: Setting _lm_dd_interval too low can cause LMD to consume too much CPU. Setting it too high can cause applications to hang up because deadlocks aren't being detected quickly enough.

>
> 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 Tue Mar 03 2009 - 10:25:52 CST