ORA-02049 Distributed Lock Timeout - Resolution Guide

ORA-02049: Timeout - Distributed Transaction Waiting for Lock

Error Overview

Error Text: ORA-02049: timeout: distributed transaction waiting for lock

This error occurs when a distributed transaction (one that spans multiple databases via database links) waits for a lock on a remote database and the wait exceeds the DISTRIBUTED_LOCK_TIMEOUT parameter. It’s common in environments with database links, distributed applications, and cross-database operations.

Common Causes

1. Lock Contention on Remote Database

• Another session on the remote database holds a conflicting lock
• Long-running transactions on remote tables
• Uncommitted changes blocking the distributed transaction

2. DISTRIBUTED_LOCK_TIMEOUT Too Low

• Default is 60 seconds — often too short for complex operations
• Large distributed DML operations timing out
• Network latency adding to wait time

3. Network Latency

• Slow network between databases
• Database link going through WAN connections
• Intermittent connectivity issues adding to lock wait

4. Distributed Deadlocks

• Circular lock dependencies across databases
• Two transactions locking resources in opposite order on different databases

5. In-Doubt Transactions

• Failed two-phase commits leaving locks held
• Pending distributed transactions blocking new operations

Diagnostic Queries

Check DISTRIBUTED_LOCK_TIMEOUT

-- Current setting (default: 60 seconds)
SHOW PARAMETER distributed_lock_timeout;

-- Or query from v$parameter
SELECT name, value, description
FROM v$parameter
WHERE name = 'distributed_lock_timeout';

Identify Distributed Transactions

-- Active distributed transactions
SELECT
  local_tran_id,
  global_tran_id,
  state,
  mixed,
  advice,
  tran_comment,
  fail_time
FROM dba_2pc_pending;

-- Distributed transaction locks on LOCAL database
SELECT
  s.sid, s.serial#, s.username, s.machine,
  s.sql_id, s.status,
  l.type, l.id1, l.id2, l.lmode, l.request
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
WHERE s.taddr IN (
  SELECT taddr FROM v$transaction WHERE flag = 15  -- Distributed flag
);

Check Remote Database Locks

-- Run on the REMOTE database to find blocking sessions
SELECT
  s.sid, s.serial#, s.username, s.machine, s.program,
  o.object_name,
  l.type, l.lmode, l.request,
  s.sql_id, s.status,
  s.last_call_et as seconds_waiting
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE l.type IN ('TX', 'TM')
ORDER BY s.last_call_et DESC;

-- Find the actual blocking chain
SELECT
  s1.sid as blocking_sid,
  s1.username as blocking_user,
  s1.machine as blocking_machine,
  s2.sid as waiting_sid,
  s2.username as waiting_user,
  s2.event as wait_event
FROM v$session s1
JOIN v$session s2 ON s1.sid = s2.blocking_session;

Check Database Link Health

-- Verify database links are functioning
SELECT db_link, username, host, created
FROM dba_db_links
WHERE owner = 'YOUR_SCHEMA';

-- Test connectivity
SELECT * FROM dual@your_db_link;

-- Check active database link sessions
SELECT
  username, command, server, machine,
  program, sql_id, status
FROM v$session@your_db_link
WHERE program LIKE '%TNS%'
   OR machine = (SELECT host_name FROM v$instance);

Check In-Doubt Transactions

-- In-doubt transactions can hold locks indefinitely
SELECT
  local_tran_id,
  global_tran_id,
  state,
  mixed,
  host,
  db_user,
  commit#,
  advice,
  fail_time,
  force_time
FROM dba_2pc_pending;

-- Related locks from in-doubt transactions
SELECT * FROM dba_2pc_neighbors
WHERE local_tran_id IN (SELECT local_tran_id FROM dba_2pc_pending);

Resolution Steps

Solution 1: Increase DISTRIBUTED_LOCK_TIMEOUT

-- Increase timeout for complex distributed operations
ALTER SYSTEM SET distributed_lock_timeout = 300 SCOPE=BOTH;
-- 300 seconds = 5 minutes

-- For a specific session only (not commonly supported, use system level)
-- Note: This is a system-level parameter only

Solution 2: Resolve Lock Contention on Remote Database

-- On the REMOTE database, find and resolve blocking sessions

-- 1. Identify the blocker
SELECT sid, serial#, username, sql_id, status, last_call_et
FROM v$session
WHERE sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

-- 2. If safe to kill, terminate the blocking session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 3. If it's an in-doubt transaction, force rollback
ROLLBACK FORCE 'local_transaction_id';
-- Or force commit if appropriate
COMMIT FORCE 'local_transaction_id';

Solution 3: Resolve In-Doubt Transactions

-- Check for pending distributed transactions
SELECT local_tran_id, state, advice FROM dba_2pc_pending;

-- If advice is 'ROLLBACK' or state is 'prepared'
ROLLBACK FORCE '1.23.456';  -- Use actual local_tran_id

-- Purge the pending transaction entry
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.23.456');

-- Clean up on both local and remote databases

Solution 4: Redesign to Avoid Distributed Locks

-- Instead of distributed DML, use local staging tables
-- Step 1: Pull data locally
CREATE TABLE local_staging AS
SELECT * FROM remote_table@db_link WHERE conditions;

-- Step 2: Process locally
UPDATE local_staging SET ...;

-- Step 3: Push back in a single transaction
INSERT INTO remote_table@db_link
SELECT * FROM local_staging;
COMMIT;
DROP TABLE local_staging;

Solution 5: Use Autonomous Transactions for Logging

-- If distributed transaction is for logging/auditing,
-- use autonomous transactions to avoid holding locks

CREATE OR REPLACE PROCEDURE log_event(p_msg VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log@logging_db (msg, ts) VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;
END;
/

Prevention Strategies

1. Minimize Distributed Transaction Scope

-- Keep distributed transactions as short as possible
-- BAD: Long transaction spanning multiple databases
BEGIN
  UPDATE local_table SET ...;
  -- ... many operations ...
  UPDATE remote_table@db_link SET ...;  -- Lock held for entire transaction
  COMMIT;
END;

-- GOOD: Separate local and remote operations
BEGIN
  UPDATE local_table SET ...;
  COMMIT;  -- Release local locks first
END;
/
BEGIN
  UPDATE remote_table@db_link SET ...;
  COMMIT;  -- Separate, shorter distributed transaction
END;
/

2. Monitor Distributed Lock Wait Times

-- Track distributed lock waits
SELECT event, total_waits, time_waited_micro/1000000 as time_waited_sec
FROM v$system_event
WHERE event LIKE '%distributed%'
ORDER BY time_waited_micro DESC;

3. Set Appropriate Timeout Values

-- Based on your network latency and transaction complexity
-- Low latency LAN: 60-120 seconds
-- WAN connections: 300-600 seconds
-- Complex batch operations: 900+ seconds
ALTER SYSTEM SET distributed_lock_timeout = 120 SCOPE=BOTH;

Related Errors

• ORA-00060: Deadlock Detected - Local deadlock detection
• ORA-02063: Database Link Error - Database link connectivity problems
• ORA-00054: Resource Busy - Local lock timeout
• ORA-03113: End-of-file on Communication - Connection lost during distributed operation