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:

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)