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 X306 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).
