信息太少，参考一下这个：
Subject:Ora-04020 Deadlocks Most Common Causes
Doc ID:Note:166924.1 Type:TROUBLESHOOTING
Last Revision Date:18-MAY-2004 Status:PUBLISHED
PURPOSE
-------
This article explains the most common scenarios for ORA-04020 Deadlocks and
how to avoid them.
SCOPE & APPLICATION
-------------------
This article is intended for Oracle Support Analysts , Oracle Consultants,
Database Administrators and Application Developers.
Reasons for the ORA-4020
------------------------
An ORA-4020 occurs when a session tries to lock a library object and a deadlock is detected.
It mostly occur during the compilation of packages/triggers/views or the executions of
DDL statements. It sometimes can happen after an software upgrade due to the invalidations
of database objects and the required revalidation of them when you start to use them
(see Note 130409.1).
In general, the first thing to control when you get an ORA-4020 is to see if they are
invalid objects used, and try to recompile/validate them. Secondly, control if DDL commands
are executed by different sessions at the same time, and serialize this processing.
We can distinguish two types of deadlocks:
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.
2. Deadlocks between concurrent sessions
The ORA-04020 deadlock error usually occurs when two user processes
cannot complete their transactions because they are trying to access
the same resource.
Self Deadlocks situations
-------------------------
When a self deadlock is detected, a trace file will be generated in the user_dump_dest, e.g.
*** SESSION ID
12.49926) 2001-12-02 10:22:55.364
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SCOTT.CHARTEST
--------------------------------------------------------
object waitingwaiting blocking blocking
handle session lock mode session lock mode
---------------- -------- ------------ -------- ----
4269c48 26535dc2ae1d5cX 26535dc2ae2428S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 2ae1d5c, type: 35, owner: 29a8ed8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=2ae1d5c handle=4269c48 request=X
call pin=0 session pin=0
user=26535dc session=2652d70 count=0 flags=[00] savepoint=0
LIBRARY OBJECT HANDLE: handle=4269c48
name=SCOTT.CHARTEST
hash=93440965 timestamp=12-02-2001 10:22:49
.................
In the previous trace file, we see that the session hold a shared lock (S)
and needs a exclusive lock (X). Therefore, an ora-4020 is generated.
Following examples generate self deadlocks:
1. Truncating a synonym raises an ora-04020 "deadlock detected" rather than an ora-942 "invalid table or view".
The following steps will reproduce this problem :
create synonym emp_syn for scott.emp;
truncate table emp_syn;
The workaround is to avoid using synonyms where they are not
officially supported. (see Note 146926.1)
2. The recompilation of a trigger can sometimes result in an ORA-04020
Sequence of events leading to this problem are:
(1)create table and create trigger on the table.
(2)modify the table by adding partitions or modifying columns, resulting in the invalidation
of the trigger
(3)executing a DML (that requires the trigger to be recompiled at DML time) can generate a self deadlock
ORA-04020 situation.
The workaround consists in recompiling the triggers before executing the DML.
(see Note 108755.1)
3. Compiling circular dependent code
It should normally not be possible to get circular dependent PLSQL/VIEW/SYNONYM/ROLES code
in the database.Errors like "ORA-01731 : circular view definition encountered",
"ORA-01775 : looping chain of synonyms", "ORA-01934 : circular role grant detected"...
should be the result. In some cases, an ORA-4020 can be encountered.
e.g.
create or replace procedure proc1 as
rows integer;
begin
proc2;
end proc1;
/
create or replace procedure proc2 as
rows integer;
begin
proc1;
end proc2;
/
create or replace procedure proc1 as
rows integer;
begin
proc2;
end proc1;
/
gives an ORA-4020.
Workaround: find the circular dependency and remove it
It is also possible to fall on Bug 1612147 if the plsql code is
complex (see Note 156900.1).
If the circular code could get compiled due to whatever reason, it will be
impossible to drop the objects and you will require to contact Oracle Support
to remove them. It is the case if the following script returns rows:
column "dependant name"format a20;
column "dependant owner" format a15;
column "parent owner" format a15;
select d_obj#,
doa.object_name "dependant name",
doa.owner "dependant owner",
doa.status "dep status",
p_obj#,
dob.object_name "parent name",
dob.owner "parent owner"
from dba_objects doa,
dba_objects dob,
dependency$ dp
where (dp.d_obj#, dp.p_obj#)
in (select dpb.p_obj#, dpb.d_obj# from dependency$ dpb)
and dp.d_obj#=doa.object_id
and dp.p_obj#=dob.object_id
and dob.status = 'INVALID';
4. ORA-4020 self deadlock on drop table with a functional index
Dropping a functional index on a table can result in an Ora-4020 deadlock message if the functional index's associated
function references the base table.
Workaround: Drop the functional index(es) first and then drop the table.
5. ORA-04020 when creating functional indexes due to the usage of %TYPE
workaround: avoid using %TYPE in the definition of the function Note 162872.1
6. ALTER TABLE Command Fails Using INDEX ONLINE Option
e.g. alter table emp add primary key (empno) using index online
gives ORA-0420 if there are no unique index on empno
(see Note 153391.1)
Deadlocks between concurrent sessions situations
------------------------------------------------
The most obvious workaround is the serialize the processing. ORA-4020 did occur in following situations:
1. Concurrently analyzing different partitions of the same base object can
fail with an Ora-4020 "deadlock detected".
2. Creating different materialized views against the same base table may result in a Ora-4020 deadlock if executed
concurrently (see Note 103077.1).
3. You can receive an Ora-4020 when jobs insert into a partitioned IOT and another job concurrently tries to move
one of the partitions.
RELATED DOCUMENTS
-----------------
Note 103077.1 ORA-04020 deadlock detected creating MATERIALIZED VIEWS
Note 153391.1 ALTER TABLE Command Fails Using INDEX ONLINE Option
Note 156900.1 ORA-04020 Compiling a Package
Note 162872.1 Creation of Function Based Index Fails With ORA-4020
Note 146926.1 Truncating a Synonym Returns ORA-04020 Deadlock Detected Message
Note 108755.1 ORA-04020 when a trigger is fired
Note 130409.1 ORA-04020 After Upgrade
Bug 1612147Self deadlock ORA-04020 possible compiling PLSQL
Note 131885.1 Analyzing Deadlock Graphs when ORA-4020 occurs
|
