Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g574at415542 for ; Fri, 7 Jun 2002 00:36:55 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id NAA74776; Thu, 6 Jun 2002 13:32:41 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 00476C59; Thu, 06 Jun 2002 13:07:38 -0800 Message-ID: Date: Thu, 06 Jun 2002 13:07:38 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Jack Silvey Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Jack Silvey Subject: Re: Mysterious Deadlock Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Seems that the "no rows" message was the identifying criteria of our problem, however, I have both slept and drank since then. Not at the same time, of course. Well, maybe a little overlap. How about your indexes - initrans? pctfree? any bitmap indexes involved? --- Walter K wrote: > Jack, > > Thanks for the reply. The table is defined as: > INI_TRANS = 1 > PCT_FREE = 10 > I meant to mention in my original posting that the > platform is Solaris 8, Oracle 8.1.7.0/32-bit. > > I don't know what the exact number of concurrent > transactions is, probably no more than a few (1-3). > The deadlock does not occur all the time. What's > even > stranger, is that the error can show up in the alert > > log at different times yet with the SAME pid for the > > trace file name--for example, today might be > prod_ora_12345.trc showing up at different times in > the alert log and next week Monday it might show > prod_ora_67890.trc multiple times. > > Did your deadlocks show up as "no rows" and only one > > line (S waiting for X) in the deadlock graph? > > Thanks again. > -w > > --- Original Message --- > To: Multiple recipients of list ORACLE-L L@fatcity.com> > > >Walter, > > > >we had a situation where our pctfree was 0, our > >initrans was 4, and we were trying to update the > table > >using 8 concurrent processes. Since we had no space > to > >grow, our ITL could not expand, and some of the > >processes deadlocked with a similiar error. > > > >What is your pctfree and initrans? how many procs > are > >trying to insert/delete/update it at once? are ALL > >dying, or just some? > > > > > >jack silvey > > > >> --- Walter K wrote: > >> > We have an application that is periodically > >> > encountering what appears to be a > self-deadlock. > >> Only > >> > one session is listed and it holds an exclusive > >> (X) > >> > lock and is waiting for a share (S) lock with > NO > >> ROWS > >> > waited. I have pasted the deadlock graph at the > >> bottom- > >> > -hopefully it will be legible. > >> > > >> > Does anyone know how such a lock could be > >> produced? > >> > I'd really like to be able to recreate such a > >> scenario. > >> > > >> > Thanks! > >> > -w > >> > > >> > Current SQL statement for this session: > >> > update ACCOUNT set > >> > > >> > >ACCOUNT_ID=:ACCOUNT_ID,ACCOUNT_NAME=:ACCOUNT_NAME,LAST > _ > >> > > >> > >MODIFIED=:LAST_MODIFIED,CUSTOMER_NODE_ID=:CUSTOMER_NOD > E > >> > > >> > >_ID,ACCOUNT_TYPE_ID=:ACCOUNT_TYPE_ID,ACCOUNT_BALANCE=: > A > >> > > >> > >CCOUNT_BALANCE,BALANCE_DATE=:BALANCE_DATE,INVOICE_ID=: > I > >> > > >> > >NVOICE_ID,PREVIOUS_INVOICE_ID=:PREVIOUS_INVOICE_ID,CRE > D > >> > > >> > >IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUN > T > >> > > >> > >,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTI > O > >> > N_DATE=:ACCOUNT_ACTION_DATE where > >> > ACCOUNT_ID=:key_ACCOUNT_ID > >> > 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-0007004c-000026bf 34 95 > >> > X 34 95 S > >> > session 95: DID 0001-0025-0002E096 session 95: > >> > DID 0001-0025-0002E096 > >> > Rows waited on: > >> > Session 95: no row > >> > > >> > -- > >> > Please see the official ORACLE-L FAQ: > >> http://www.orafaq.com > >> > -- > >> > Author: Walter K > >> > INET: ORA1034@sbcglobal.net > >> > > >> > 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@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). > >> > >> > >> > __________________________________________________ > >> Do You Yahoo!? > >> Yahoo! - Official partner of 2002 FIFA World Cup > >> http://fifaworldcup.yahoo.com > >> -- > >> Please see the official ORACLE-L FAQ: > >> http://www.orafaq.com > >> -- > >> Author: Rachel Carmichael > >> INET: wisernet100@yahoo.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@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). > > > > > >__________________________________________________ > >Do You Yahoo!? > >Yahoo! - Official partner of 2002 FIFA World Cup > >http://fifaworldcup.yahoo.com > >-- > === message truncated === __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: jack_silvey@yahoo.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@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).