Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mysterious Deadlock
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_NODE
> >
>
_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,CRED
> >
>
IT_LIMIT=:CREDIT_LIMIT,UNBILLED_AMOUNT=:UNBILLED_AMOUNT
> >
>
,ACCOUNT_ACTION_CODE=:ACCOUNT_ACTION_CODE,ACCOUNT_ACTIO
> > 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
> >
>
-- 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 - 14:44:25 CDT