DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02049 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02049: Timeout – Distributed Transaction Waiting for Lock

ORA-02049 occurs in Oracle distributed transaction environments when a transaction waits longer than the DISTRIBUTED_LOCK_TIMEOUT parameter (default: 60 seconds) to acquire a lock held by another session across a database link. This error is exclusive to distributed transactions involving DB Links connecting multiple Oracle instances and is not raised in single-database scenarios. When the timeout threshold is breached, Oracle automatically rolls back the waiting transaction and raises this error.

Top 3 Causes

1. Lock Contention Between Distributed Transactions

When multiple sessions access the same remote table via a DB Link simultaneously, lock contention arises. Network latency amplifies the wait time, making it easy to exceed the timeout threshold.

-- Identify blocking sessions in distributed transactions
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.seconds_in_wait,
    s.event,
    l.type        AS lock_type,
    DECODE(l.lmode,
        0, 'None',        1, 'Null',
        2, 'Row Share',   3, 'Row Exclusive',
        4, 'Share',       5, 'Share Row Exclusive',
        6, 'Exclusive'
    )             AS lock_mode
FROM
    v$lock l
    JOIN v$session s ON l.sid = s.sid
WHERE
    l.block = 1
   OR l.request > 0
ORDER BY
    s.seconds_in_wait DESC;

-- Kill a blocking session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- Example:
ALTER SYSTEM KILL SESSION '112,4532' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

2. Long-Running Uncommitted Transactions

Transactions that are never committed or rolled back — often due to missing exception handling in application code — hold locks indefinitely. Any distributed transaction trying to access the same rows will hit ORA-02049.

-- Find long-running active transactions (over 5 minutes)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    ROUND(
        (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 * 60,
        2
    ) AS elapsed_minutes
FROM
    v$transaction t
    JOIN v$session s ON t.addr = s.taddr
WHERE
    (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 * 60 > 5
ORDER BY
    elapsed_minutes DESC;

-- Handle in-doubt distributed transactions
SELECT local_tran_id, global_tran_id, state, fail_time
FROM   dba_2pc_pending;

-- Force commit or rollback an in-doubt transaction
COMMIT   FORCE 'your_local_tran_id';
ROLLBACK FORCE 'your_local_tran_id';
Enter fullscreen mode Exit fullscreen mode

3. DISTRIBUTED_LOCK_TIMEOUT Set Too Low

The default timeout of 60 seconds may be insufficient in high-latency or high-load environments, causing legitimate transactions to fail unnecessarily.

-- Check current setting
SELECT name, value, description
FROM   v$parameter
WHERE  name = 'distributed_lock_timeout';

-- Increase timeout to 120 seconds (apply immediately and persist)
ALTER SYSTEM SET DISTRIBUTED_LOCK_TIMEOUT = 120 SCOPE = BOTH;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Spot active waits related to distributed transactions
SELECT sid, serial#, username, seconds_in_wait, event, state
FROM   v$session
WHERE  event      LIKE '%distributed%'
  AND  wait_class != 'Idle'
ORDER BY seconds_in_wait DESC;

-- Step 2: Terminate the problematic blocking session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Step 3: Clean up any in-doubt transactions
COMMIT   FORCE 'tran_id';   -- or ROLLBACK FORCE 'tran_id';

-- Step 4: Adjust the timeout parameter if needed
ALTER SYSTEM SET DISTRIBUTED_LOCK_TIMEOUT = 120 SCOPE = BOTH;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always explicitly manage transactions in distributed code. Ensure every code path that performs DML over a DB Link includes a COMMIT on success and a ROLLBACK in every exception handler. Keep distributed transaction scope as small and short-lived as possible.

-- Recommended pattern for distributed transactions
BEGIN
    UPDATE remote_table@db_link SET status = 'DONE' WHERE id = :v_id;
    UPDATE local_table SET updated_at = SYSDATE WHERE id = :v_id;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Set up proactive lock monitoring. Schedule a monitoring job that queries v$session and v$lock every few minutes and alerts the DBA team when distributed wait times exceed a defined threshold — before ORA-02049 is triggered.

Related Errors

  • ORA-02050 – Transaction rolled back; some remote DBs may be in-doubt.
  • ORA-01591 – Lock held by an in-doubt distributed transaction.
  • ORA-02054 – Transaction is in-doubt; check dba_2pc_pending for forced resolution.
  • ORA-00060 – Deadlock detected; can co-occur with ORA-02049 in distributed environments.

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

Top comments (0)