Re: locking issues

Joe,

According to my $ORACLE_HOME/rdbms/msg/oraus.msg, the event is:

29721, 00000, "enable DLM deadlock event trace"

// *Document: NO
// *Cause:
// *Action: Dump trace for DLM deadlock.

In sql, do an

alter system SET EVENTS '60 TRACE NAME ERRORSTACK FOREVER';

In the initialization file, do

event = "60 trace name context forever, level 10"

According to my Dumps, Events and Traces Internals seminar.

• Original Message ----- From: Joseph S. Testa <teci_at_oracle-dba.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, August 07, 2000 1:54 PM Subject: locking issues

> I'm hitting up against locking(including deadlock) issues. Here is what
> we've done so far:
>
> using utllockt, dba_waiters, etc all of the normal delivered stuff has
> been used.
>
> I'm sure there is a way to see what the values of bind variables are, i
> think its an event, but i dont remember.
>
> According to the duhvelopers, its an oracle issue not an app issue(i
> disagree totally), short of me going thru their code line by line(and i
> want to hurt someone since it has goto statements in a stored proc), is
> there anything else i can do to determine the sql(on deadlock i get a
> trace file so i can see what is happening).
>
> At first it we thought it had to do with a bitmap index but we converted
> it to a normal index and it didnt go away.
>
> here is part of the deadlock trace file(we're already ran the event for
> 60, to get more info).
>
> It appears that there is a share lock issue, anyone willing to take a
> stab at this?
>
>
> DEADLOCK DETECTED
> Current SQL statement for this session:
> INSERT INTO ATTEMPT_STATE (
> ATTEMPT_STATE_OID,ATTEMPT_OID,ACTUAL_DATE,PLAN_DATE,
> SEQUENCE_NUMBER,NAME ) VALUES ( :b1,:b2,:b3, NULL ,:b4,:b5 )
> ----- PL/SQL Call Stack -----
> object line object
> handle number name
> 82feecd4 296 package body FTC.ATTEMPT_STATE_CHANGE_PKG
> 82ff75f0 1 anonymous block
> 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-0002001d-0003050c 12 20 X 13
> 18 S
> TX-00050022-000302db 13 18 X 12
> 20 S
> session 20: DID 0001-000C-00000002 session 18: DID
> 0001-000D-00000002
> session 18: DID 0001-000D-00000002 session 20: DID
> 0001-000C-00000002
> Rows waited on:
> Session 18: no row
> Session 20: no row
>
>
> thanks, joe
>
>
> --
> Author: Joseph S. Testa
> INET: teci_at_oracle-dba.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Aug 07 2000 - 17:32:33 CDT