Re: locking issues

you mean the fact that the trace file specifically states that it is an application problem doesn't hold any weight with the developers?

do the users log in as separate accounts or does everyone log in as the same userid?

can you reproduce it with only one or two users on the system, each of whom is tracking what they are doing?

you are deadlocking on an insert statement???? are there triggers on the table that reference another table that could be locked?

just some "off the top of my head" thoughts

>From: "Joseph S. Testa" <teci_at_oracle-dba.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: locking issues
>Date: Mon, 07 Aug 2000 10:54:45 -0800
>
>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).