Oracle ORA-04020 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-04020: Deadlock Detected While Trying to Lock Object

ORA-04020 is an Oracle error that occurs when two or more sessions enter a circular wait state while attempting to acquire locks on the same database objects at the library cache or dictionary cache level. Unlike the more common ORA-00060 (row-level deadlock), ORA-04020 specifically involves DDL operations such as package compilation, procedure recompilation, or schema object modifications. It is most frequently seen during deployment activities when multiple sessions simultaneously attempt to compile or access shared objects.

Top 3 Causes

1. Concurrent DDL Operations on the Same Object

When two or more sessions simultaneously attempt to compile or alter the same database object (package, procedure, view, trigger), they can enter a deadlock state waiting for each other's locks to be released.

-- Check current library cache lock holders
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    lo.object AS locked_object,
    lo.mode_held,
    lo.mode_requested
FROM
    v$session s,
    v$library_cache_lock lo
WHERE
    s.saddr = lo.session_id
ORDER BY
    lo.mode_held DESC;

-- Kill the blocking session if necessary
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- Example: ALTER SYSTEM KILL SESSION '45,2301' IMMEDIATE;

2. Automatic Recompilation of INVALID Objects Under Concurrent Load

Oracle marks dependent objects as INVALID when a referenced object is modified. When multiple sessions simultaneously invoke the same INVALID object, each session races to acquire the recompilation lock, potentially causing a circular wait.

-- Find all INVALID objects in a schema
SELECT
    owner,
    object_name,
    object_type,
    status
FROM
    dba_objects
WHERE
    status = 'INVALID'
    AND owner = 'YOUR_SCHEMA'
ORDER BY
    object_type, object_name;

-- Manually recompile all INVALID objects (serial mode recommended)
BEGIN
    UTL_RECOMP.recomp_serial(schema => 'YOUR_SCHEMA');
END;
/

-- Recompile a specific object
ALTER PACKAGE your_schema.your_package COMPILE BODY;
ALTER PROCEDURE your_schema.your_procedure COMPILE;

3. Distributed Transactions via DB Links

In environments with bidirectional DB Links, lock chains can form across databases, causing ORA-04020 when both sides reference each other's objects concurrently.

-- Check for pending distributed transactions
SELECT
    local_tran_id,
    global_tran_id,
    state,
    host,
    db_link
FROM
    dba_2pc_pending;

-- Force rollback of an orphaned distributed transaction
ROLLBACK FORCE 'local_tran_id';
-- Example: ROLLBACK FORCE '3.14.8821';

Quick Fix Solutions

Identify and kill the blocking session using v$library_cache_lock joined with v$session.
Recompile INVALID objects using UTL_RECOMP.recomp_serial() before opening traffic after a deployment.
Force rollback orphaned distributed transactions found in dba_2pc_pending.
Serialize DDL operations — avoid running multiple DDL scripts in parallel on the same schema.

Prevention Tips

Set DDL_LOCK_TIMEOUT to avoid infinite waits:

-- Session level (recommended for deployment scripts)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;

-- System level
ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30 SCOPE = BOTH;

-- Verify the setting
SELECT name, value FROM v$parameter WHERE name = 'ddl_lock_timeout';

Recompile objects before reopening traffic after deployments:

-- Standard post-deployment recompile and validation
BEGIN
    UTL_RECOMP.recomp_serial(schema => 'APP_SCHEMA');
END;
/

-- Confirm no INVALID objects remain
SELECT COUNT(*), object_type
FROM dba_objects
WHERE status = 'INVALID'
  AND owner = 'APP_SCHEMA'
GROUP BY object_type;

Always perform DDL operations in a single, serial session during a controlled maintenance window, and run a full recompile before restoring user traffic to eliminate automatic recompilation races.

Related Errors:

ORA-00060 — Row-level deadlock (DML operations)
ORA-04021 — Timeout waiting to lock object (non-deadlock version of ORA-04020)
ORA-02049 — Distributed transaction lock timeout via DB Link

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community