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 <ORA1034_at_sbcglobal.net> 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 <ORACLE-
> L_at_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 <ORA1034_at_sbcglobal.net> 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_at_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_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).
> >>
> >>
> >>
> __________________________________________________
> >> 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_at_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_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).
> >
> >
> >__________________________________________________
> >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_at_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_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).
Received on Thu Jun 06 2002 - 16:07:38 CDT