Re: Mysterious Deadlock

Walter,

It may help to see where and why the problem occurs: set the following event in the init.ora
"60 trace name errorstack level 10"

This will show where in the code oracle gets the deadlock and may help to diagnose your problem.

Anjo.

Walter K 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).

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