DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01591 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01591: Lock Held by In-Doubt Distributed Transaction Identifier

ORA-01591 occurs when a distributed transaction enters an "in-doubt" state — meaning Oracle cannot determine whether to commit or roll back — and the locks held by that transaction block other sessions from accessing the same resources. This typically happens during Two-Phase Commit (2PC) processing across database links when a network failure or remote database crash interrupts the commit sequence. Until the transaction is resolved, any session attempting to access the locked rows will encounter this error.

Top 3 Causes

1. Network Failure During Two-Phase Commit

When Oracle executes a distributed transaction via a DB Link, it uses a 2PC protocol. If a network outage occurs between the Prepare and Commit phases, the local database is left in an in-doubt state and cannot release its locks.

-- Check for in-doubt transactions
SELECT local_tran_id,
       global_tran_id,
       state,
       fail_time,
       host,
       db_user
FROM   dba_2pc_pending
ORDER  BY fail_time;
Enter fullscreen mode Exit fullscreen mode

2. RECO Process Unable to Auto-Resolve

Oracle's background RECO (Recoverer) process periodically retries resolution of in-doubt transactions. If the remote database remains unavailable for an extended period, RECO cannot complete recovery and the locks persist indefinitely.

-- Verify RECO process is running
SELECT name, status
FROM   v$bgprocess
WHERE  name = 'RECO';

-- Check distributed transaction parameters
SELECT name, value
FROM   v$parameter
WHERE  name IN ('distributed_transactions', 'commit_point_strength');
Enter fullscreen mode Exit fullscreen mode

3. Session Disconnection During DB Link DML

If a session performing a large DML operation through a DB Link is killed or disconnects unexpectedly before issuing a COMMIT or ROLLBACK, the transaction is left in-doubt and locks remain on both the local and remote databases.

-- Identify blocking sessions related to in-doubt transactions
SELECT s.sid,
       s.serial#,
       s.username,
       s.event,
       l.type,
       l.lmode,
       l.request
FROM   v$session s
JOIN   v$lock    l ON s.sid = l.sid
WHERE  l.type = 'TX'
AND    l.request > 0;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Step 1 — Force Commit or Rollback (coordinate with the remote DBA first):

-- Force commit if remote DB committed the transaction
COMMIT FORCE 'local_tran_id';

-- Force rollback if remote DB rolled back the transaction
ROLLBACK FORCE 'local_tran_id';

-- Example
COMMIT FORCE '1.17.123456';
ROLLBACK FORCE '1.17.123456';
Enter fullscreen mode Exit fullscreen mode

Step 2 — Purge stale entries from DBA_2PC_PENDING:

-- Remove orphaned pending transaction record
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.17.123456');

-- Confirm cleanup
SELECT COUNT(*) FROM dba_2pc_pending;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Monitor DBA_2PC_PENDING proactively. Schedule a job to alert DBAs when in-doubt transactions persist beyond 30 minutes. Set COMMIT_POINT_STRENGTH appropriately across linked databases to establish a clear commit decision owner. 
-- Scheduled monitoring query
SELECT COUNT(*) AS stale_pending
FROM   dba_2pc_pending
WHERE  fail_time < SYSDATE - (30/1440);
Enter fullscreen mode Exit fullscreen mode
  1. Always use explicit COMMIT/ROLLBACK with proper exception handling in any PL/SQL code that uses DB Links. Never rely on implicit transaction management when distributed resources are involved. 
-- Recommended coding pattern for DB Link transactions
BEGIN
    INSERT INTO remote_table@my_db_link (col1, col2)
    VALUES ('val1', 'val2');

    COMMIT; -- Always explicit
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- Always explicit
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-02050 Distributed transaction rolled back; remote may be in-doubt
ORA-02054 Transaction is in-doubt
ORA-02058 No prepared transaction found for FORCE
ORA-01578 Data block corruption, may accompany recovery failure

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