Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: locking issues

RE: locking issues

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Tue, 8 Aug 2000 09:31:37 -0400
Message-Id: <10583.114066@fatcity.com>


The latest attempts by the users to recreate the deadlocking have failed (succeeded?) In other words, there is no more deadlocking.

It appears the deadlocking was related to 3 issues:

  1. A BITMAP index on one of the tables
  2. Some sequence-caching scheme (I think it was generating duplicates)
  3. The use of GOTOs in the code.

Kevin

>
>
> Hi Kevin,
>
> Please note my second email to Joe (attached), and consider rebuilding
> the table with INITRANS 2. The fact mentioned here that one of the
> statements is an insert supports my suggestion that this is
> an ITL entry
> shortage.
>
> Regards,
> Steve Adams
> http://www.ixora.com.au/
> http://www.oreilly.com/catalog/orinternals/
> http://www.christianity.net.au/
>
>
> -----Original Message-----
> Sent: Tuesday, 8 August 2000 22:54
> To: Multiple recipients of list ORACLE-L
>
>
> All:
>
> I am the developer working with Joe on resolving the Duhveloper's
> problems
> (I didn't write the code, I'm just charged with fixing it. Ugh!)
>
> First of all, Steve Adams suggested we look at the PCTFREE on
> the tables
> involved. All have a PCTFREE of 10.
>
> First, Rachel's questions.
> >
> > you mean the fact that the trace file specifically states
> > that it is an
> > application problem doesn't hold any weight with the developers?
>
> Not with the Duhveloper that wrote the code. He still
> maintains it can't
> be
> his problem?!?!? He's turned the problem over to us stating to his
> manager
> that it is a DBA issue.
>
> >
> > do the users log in as separate accounts or does everyone log
> > in as the same
> > userid?
>
> Separate accounts.
>
> >
> > can you reproduce it with only one or two users on the
> > system, each of whom
> > is tracking what they are doing?
>
> It has been reproduced with exactly 2 users on the system
> each accessing
> the
> screen for a different Invoice.
>
> >
> > you are deadlocking on an insert statement???? are there
> > triggers on the
> > table that reference another table that could be locked?
>
> Deadlocking is occurring on and INSERT statement and an
> UPDATE statement
> (both on the same table). There are no triggers on the table.
> There is a
> single-column FK on the table. And it is properly indexed. The table
> that
> the deadlocking is occurring on is range partitioned by its
> Primary Key
> column.
>
> Kevin
>
> > >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).
> >
> > ______________________________________________________________
> > __________
> > Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com
>
> --
> Author: Rachel Carmichael
> INET: carmichr_at_hotmail.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).
> --
> Author: Toepke, Kevin M
> INET: ktoepke_at_cms.cendant.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
Received on Tue Aug 08 2000 - 08:31:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US