Re: locking issues

Joe,

Add this to your init.ora and bounce the database:

event = "10046 trace name context forever, level 4"

Here are the possible level values:

1 Standard SQL_TRACE functionality
4 As level 1 plus tracing of bind variables 8 As level 1 plus wait events
12 As level 1 plus bind variables and wait events.

You can also do this per session, but that would make it pretty tough to catch the offenders.

> According to the duhvelopers, its an oracle issue not an app issue(i

Joe, I own the duhveloper.com domain, any ideas on what to do with it? ;)

Jared

On Mon, 7 Aug 2000, Joseph S. Testa wrote:

> 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
> also send the HELP command for other information (like subscribing).
>
>
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Mon Aug 07 2000 - 13:55:34 CDT