Message-ID: <F001.00476ACD.20020606123557@fatcity.com>
Date: Thu, 06 Jun 2002 12:35:57 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Walter K" <ORA1034@sbcglobal.net>
Subject: Re: Mysterious Deadlock
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

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@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@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@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@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@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@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: Jack Silvey
>  INET: jack_silvey@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@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@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@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).