Mysterious Deadlock

From: Walter K <ORA1034_at_sbcglobal.net>
Date: Thu, 06 Jun 2002 08:33:32 -0800
Message-ID: <F001.00476438.20020606083332@fatcity.com>

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). Received on Thu Jun 06 2002 - 11:33:32 CDT