同一会话中的自身死锁情况

在单个会话中也可能出现死锁。比如在创建on prebuilt的materialized view的时候，如果select语句中访问了基表，就会出现死锁。

create">NING@ning>create materialized view sales
2 on prebuilt table with reduced precision
3 as
4 select * from sales;
create materialized view sales
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object
30x66BB78C00x66DDDC680x689480A8

该session对应的dump文件中有如下记录：

A self-deadlock among DDL and parse locks is
detected. In most cases, this self-deadlock
is handled internally.
This should be reported to Oracle Support
ONLY IF an error is signalled back to the
user on a command-line or screen.
The following information may aid in finding
the problem.
ORA-04020: deadlock detected while trying to lock object NING.SALES
----------------------------------------------
session: 689480A8 request: X
LIBRARY OBJECT HANDLE: handle=66dddc68 mutex=66DDDD1C(0)
name=NING.SALES
hash=f3c98f203a3fcea7c4b33529b1b9bb39 timestamp=02-11-2007 22:45:28
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=S pin=0 latch#=1 hpc=0004 hlc=0004
lwt=66DDDCC4[66DDDCC4,66DDDCC4] ltm=66DDDCCC[66DDDCCC,66DDDCCC]
pwt=66DDDCA8[66DDDCA8,66DDDCA8] ptm=66DDDCB0[66DDDCB0,66DDDCB0]
ref=66DDDCE4[66DDDCE4,66DDDCE4] lnd=66DDDCF0[66BD3F04,66A2EB30]
LIBRARY OBJECT: object=66bae31c
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr alloc(K) size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 6711a82c 66bae3b4 I/-/A/-/- 0 NONE 00 0.52 0.00
8 66bae544 6719005c I/-/A/-/- 0 NONE 00 1.16 2.09
9 66bae620 67b8fee0 I/-/A/-/- 0 NONE 00 0.17 1.05
10 66bae670 67b8fae8 I/-/A/-/- 0 NONE 00 2.27 3.22

说明这是同一个session中的self-deadlock，在DDL分析期间，需要持有对sales的排他锁，同时自身又要访问sales表，所以需要持有对sales的共享锁，导致自身死锁的发生。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50281/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50281/