IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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:
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 )
