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" <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
