Re: Mysterious Deadlock

I remember having this problem a long time ago and finding an explanation on www.ixora.com.au I don't have time to search my notes at the moment but if I can, I'll do it a bit later.

Henry

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, June 06, 2002 2:48 PM

> http://www.dbdomain.com/a120197.htm
>
> it's down near the end of the article, but they talk about a deadlock
> with no rows and say it appears to be a problem with the initrans or
> pctfree setting on that table
>
>
> --- 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
> > --------------------------------------------------------------------
> > 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: hporas_at_attbi.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).