Re: DeadLock: session wait on itself with no rows.

From: Arup Nanda <orarup_at_hotmail.com>
Date: Sun, 02 Mar 2003 08:58:50 -0800
Message-ID: <F001.0055DEF6.20030302085850@fatcity.com>

chao_ping,

Read MetaLink Doc Id 166924.1. It offer some insights into this kind of "Self Deadlocks". Here is an excerpt.

<QUOTE>
1. Self Deadlocks

It occur when one session tries to get a lock on a resource that he already has in some way. Normally, the Oracle engine should detect those situations and should avoid the signalling of the ORA-04020 to the end-users. When a self deadlock detection occurs, Oracle generates a trace file in the user_dump_dest. It is only considered as a bug if an ORA-04020 is signalled to the end-user.

</QUOTE>

HTH. Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, March 02, 2003 8:13 AM

> hi, friends:
>
> >>>select count(*) into :b0 from USERCONFIRMINFO where USER_ID=:b1
> >>>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-000e005d-00037cfa 306 484 X 306 484 S
> >>>session 484: DID 0001-0165-00010802 session 484: DID 0001-0165-00010802
> >>>Rows waited on:
> >>>Session 484: no row
>
> Another trace file like:
> >>>*** 2003-03-02 16:50:22.447
> >>>DEADLOCK DETECTED
> >>>Current SQL statement for this session:
> >>>select USER_ID ,USER_EMAIL into :b0,:b1 from USERPRIVATEINFO where
USER_ACCOUNT=:b2
> >>>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-0012004a-000374f8 293 263 X 306 512
S
> >>>session 263: DID 0001-0165-0004EC65 session 512: DID
0001-0165-0004EC65
> >>>Rows waited on:
> >>>Session 512: no row
>
>
>
> I ever saw message talking about two session deadlock with no rows waited
on, maybe it is because of pctfree/initrans, How about session wait on itself then? And how can it happen?
>
>
>
> Regards
> zhu chao
> msn:chao_ping_at_163.com
> www.happyit.net
> www.cnoug.org(China Oracle User Group)
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: chao_ping
> INET: chao_ping_at_vip.163.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Sun Mar 02 2003 - 10:58:50 CST