Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mysterious Deadlock
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
>> ----------------------------------------------------
-- 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