ORA-04020: deadlock detected while trying to lock object

I got ” ORA-04020: deadlock detected while trying to lock object ”  error in Oracle database.

 

ORA-04020: deadlock detected while trying to lock object

 

Details of error are as follows.

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.

 

 

deadlock detected while trying to lock object

This ORA-04020 error is related to the trying to lock a library object, a deadlock is detected.

 

Getting the below error while installing the XML DB

ORA-04020: deadlock detected while trying to lock object XDB.XDh1jUheYAR5PgNAgAILJCxg==
XDB SGA reset to NULL.

 

Lock when trying to lock an object that was in the recyclebin

Internal processing that was removing recyclebin objects was locked.

 

XML DB was successfully installed using the utility DBCA

 

Probably cleaning the recyclebin connected as / as ssydba could have avoided the error

conn / as sysdba

purge recyclebin;

 

 

 

Reasons for getting a ORA-04020 error

An ORA-04020 error occurs when a session tries to lock a library object and a deadlock is detected. It’s error definition is:

04020, 00000, "deadlock detected while trying to lock object %s%s%s%s%s"
// *Cause: While trying to lock a library object, a deadlock is detected.
// *Action: Retry the operation later.

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 re-validation of them when you start to use them. In general, the first things to check when you receive an ORA-04020 are:

  • Are invalid objects involved? If so, try to recompile/validate them.
  • Are DDL commands being executed concurrently by different sessions at the same time? If so, serialize this processing.

 

We can distinguish two types of deadlocks:

 

 

 

1. Self-Deadlocks

Self Deadlock occurs when a session attempts to get a lock on a resource that it already holds in an incompatible mode. In normal circumstances, Oracle will detect and avoid those situations without the signalling an 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.

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   waiting  waiting       blocking blocking
handle   session  lock     mode session  lock     mode
-------- -------- -------- ---- -------- -------- ----
 4269c48  26535dc  2ae1d5c    X  26535dc  2ae2428    S

--------------------------------------------------------
---------- 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 trace above, you can see that the waiting session, ‘26535dc’ is trying to get an eXclusive lock on the object SCOTT.CHARTEST. The blocking session (which is the same ‘26535dc’ – hence ‘self-deadlock’) is holding the lock in an incompatible mode (Share – you cannot get an eXclusive lock if a share lock is already held).

 

Note that you can find out what the waiting/blocking session is (assuming that it is still active) by looking up the session address(es) from the trace in V$SESSION. For example, using the ‘26535dc’ value above, you could select:

 

column line format a79
set heading off
SELECT 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
FROM v$session s , v$process p
WHERE p.addr = s.paddr
AND s.saddr LIKE '%&address';

Common reasons for ORA-04020 self deadlocks include:

 

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 of recompiling the triggers before executing the DML.
See following note:

Document 108755.1 ORA-04020 When A Trigger is Fired
  • Compiling circular dependent code

    It should normally not be possible to get circular dependent PLSQL/VIEW/SYNONYM/ROLES code in the database.  The following errors may occur:

    “ORA-01731 : circular view definition encountered”,
    “ORA-01775 : looping chain of synonyms”,
    “ORA-01934 : circular role grant detected”…

    In some cases, an ORA-04020 can be encountered.  For example:

  • Workaround: find the circular dependency and remove it. 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 "dependent name" format a20 ;
    column "dependent 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';
    
  • 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

 

  • ORA-04020 when creating functional indexes due to the usage of %TYPE

    Workaround: avoid using %TYPE in the definition of the function

 

  • ALTER TABLE Command Fails Using INDEX ONLINE Option

    e.g. alter table emp add primary key (empno) using index online gives ORA-4020 if there are no unique index on empno.

  • DBCA scripts contain an error “ORACLE_SID PIPPO” instead of correct “ORACLE_SID=PIPPO”.

    For this reason, scripts running on default instance, the procedure failed on running catalog  scripts. For that moment when trying to connect or start instance, ora-4020 is dumped: ERROR: ORA-04020: deadlock detected while trying to lock object SYS.DBMS_STANDARD.
    Modify init.ora or spfile to contain:

 

 

_system_trig_enabled=FALSE
job_queue_processes=0
aq_tm_processes=0

Hint: Try to know the current value for those parameters in order to be reverted later to it’s current value. For _system_trig_enabled, use the following query:

 

select x.ksppinm PARANAME,
decode( bitand(ksppiflg/256,1),1,'TRUE','FALSE') SESSION_VALUE,
decode( bitand(ksppiflg/65536,3),
1,'IMMEDIATE', 2,'DEFERRED', 3,'IMMEDIATE', 'FALSE' ) SYSTEM_VALUE,
ksppdesc DESCRIPTION from x$ksppi x
where x.inst_id = userenv('Instance') and
translate(ksppinm,'_','#') like '#%' and
x.ksppinm like '%_system_trig_enabled%'
order by 1;

 

 

 

  • (The following are to be completed from sqlplus as the SYS user): Issue a shutdown immediate:

     

    1.SQL> shutdown immediate
    2. SQL>startup pfile=''
    3. SQL>@catalog.sql
    4. SQL>@catproc.sql
    5. Check for invalids from dba_objects: SQL> select owner, object_name from dba_objects where status='INVALID';
    6. Run utlrp.sql: SQL>@utlrp.sql
    7. Check for invalids again.

    Truncating a synonym raises an ORA-04020 “deadlock detected” rather than an ora-942 “invalid table or view”.

    Prior to Oracle 9i, the following steps will reproduce this problem :

    Create synonym emp_syn for <user_name>.emp;
    truncate table emp_syn;

    The workaround is to avoid using synonyms where they are not officially supported. This is fixed in ORacle 9i with :

    Bug 1579190 – ORA-04020 instead of ORA-942 truncating synonym

 

 

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. The most obvious workaround is to serialize the processing. Common reasons for ORA-04020 include:

  • Concurrently analyzing different partitions of the same base object can fail with an ORA-04020 “deadlock detected”.
  • Creating different materialized views against the same base table may result in a ORA-04020 deadlock if executed concurrently.
  • Jobs inserting into a partitioned IOT and another job concurrently trying to move one of the partitions

 

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *