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: Mysterious Deadlock

Re: Mysterious Deadlock

From: Walter K <ORA1034_at_sbcglobal.net>
Date: Thu, 06 Jun 2002 11:48:36 -0800
Message-ID: <F001.0047695A.20020606114836@fatcity.com>

Thanks for the link. The example shown at the bottom of the article looks like an unindexed foreign key issue rather than an initrans/pctfree issue. What's really strange about my deadlock is that it shows no other session waiting on the lock except itself.

Originally, I thought the deadlock we were encountering was a lack of ITL slots based on all the articles I had read at the time but when I attempt to reproduce the ITL shortage scenario I always get what looks like a traditional deadlock (X waiting for X) when I deliberately deprive a block of ITL slots.

I'm open to ideas, suggestions...

Thank you.
-w

>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_AC
TIO
>> 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: 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).
Received on Thu Jun 06 2002 - 14:48:36 CDT

Original text of this message

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