locking issues

From: Joseph S. Testa <teci_at_oracle-dba.com>
Date: Mon, 07 Aug 2000 13:37:46 -0400
Message-Id: <10582.113989@fatcity.com>

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 Received on Mon Aug 07 2000 - 12:37:46 CDT