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