Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

ORA-04020: deadlock detected while trying to lock object tips

Oracle Error Tips

There are also many things to consider when the ORA-04020 is associated with a deadlock between concurrent sessions or within a single session that deadlocks itself.  The most obvious workaround for any ORA-040020 is to serialize the processing to perform the tasks one-at-a-time so that they don't conflict with each other.

Also see these notes on the concept of the perpetual embrace.

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 MOSC 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.

4.  You run utlrp.sql and get the ORA-04020 error with dbms_standard.

 

The Oracle oerr utility book note this on the ORA-04020 error:

ORA-04020 deadlock detected while trying to lock object
Cause: While trying to lock a library object, a deadlock is detected.
 
Action: Retry the operation later.

MOSC provides a very good rundown of ORA-04020, and more specifically, things that commonly cause deadlock, "this article explains the most common scenarios for ORA-04020 Deadlocks and how to avoid them."  There are two types of deadlocks:

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 signaling 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 signaled 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.

These deadlocks occur with ORA-04020, for reasons sated below:

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 MOSC 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.

MOSC Offers a great example of generated deadlocks (and subsequent ORA-04020) in a self deadlock situation:

1. Truncating a synonym raises an ORA-04020 "deadlock detected" rather than an ORA-00942 "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 MOS 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 MOSC 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;
/

This may throw an ORA-4020 error.

Workaround: find the circular dependency and remove it
It is also possible to fall on Bug 1612147 if the PL/SQL code is
complex (see MOSC 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)


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster