DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-02091 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02091: Transaction Rolled Back — Causes, Fixes & Prevention

ORA-02091 is an Oracle error indicating that an entire transaction has been rolled back by the database engine. This error rarely appears alone — it typically accompanies another error (such as ORA-00060 or ORA-02050) that triggered the rollback. Understanding the root cause requires examining the full error stack in your alert log or trace files.

Top 3 Causes

1. Distributed Transaction Failure (Database Links)

When a transaction spans multiple databases via a Database Link, a failure during the Two-Phase Commit (2PC) process forces a full rollback. This is the most common cause in enterprise environments.

-- Check for in-doubt distributed transactions
SELECT local_tran_id,
       global_tran_id,
       state,
       host,
       fail_time
FROM   dba_2pc_pending;

-- Force commit or rollback an in-doubt transaction
COMMIT FORCE 'local_tran_id_here';
-- or
ROLLBACK FORCE 'local_tran_id_here';

-- Clean up after resolution
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id_here');
Enter fullscreen mode Exit fullscreen mode

2. Deadlock Detection (ORA-00060)

Oracle automatically resolves deadlocks by rolling back one of the conflicting sessions' statements, which triggers ORA-02091. Always check the alert log for a corresponding deadlock trace file.

-- Identify blocking sessions
SELECT s.sid,
       s.serial#,
       s.username,
       s.blocking_session,
       s.event,
       s.wait_class
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL;

-- View lock holders
SELECT l.sid,
       l.type,
       l.lmode,
       l.request,
       o.object_name
FROM   v$lock l
JOIN   dba_objects o ON l.id1 = o.object_id
WHERE  l.block = 1;

-- Kill the offending session if necessary
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
Enter fullscreen mode Exit fullscreen mode

3. Deferred Constraint Violation

When using deferrable constraints, violations are only checked at COMMIT time. If a violation is found, Oracle rolls back the entire transaction and raises ORA-02091.

-- Check deferrable constraints on a table
SELECT constraint_name,
       constraint_type,
       deferrable,
       deferred,
       status
FROM   dba_constraints
WHERE  table_name = 'YOUR_TABLE'
  AND  deferrable = 'DEFERRABLE';

-- Force immediate constraint checking before commit
SET CONSTRAINTS ALL IMMEDIATE;

-- Pre-check for FK violations before DML
SELECT child.rowid, child.fk_col
FROM   child_table child
WHERE  NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE  p.id = child.fk_col
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  1. Check the full error stack — ORA-02091 is always secondary. Find the primary error in your alert log ($ORACLE_BASE/diag/rdbms/...).
  2. Resolve in-doubt transactions using COMMIT FORCE or ROLLBACK FORCE on DBA_2PC_PENDING entries.
  3. Kill deadlocked sessions after identifying them via V$LOCK and V$SESSION.
  4. Validate data integrity before committing large batch transactions.

Prevention Tips

Implement robust exception handling in PL/SQL:

BEGIN
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = 101;
    INSERT INTO audit_log (action, log_time) VALUES ('UPDATE', SYSDATE);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        INSERT INTO error_log (err_code, err_msg, log_time)
        VALUES (SQLCODE, SQLERRM, SYSDATE);
        COMMIT;
        RAISE;
END;
/
Enter fullscreen mode Exit fullscreen mode

Monitor distributed transactions proactively:

-- Schedule this check regularly
SELECT COUNT(*) AS pending_count,
       MIN(fail_time) AS oldest_issue
FROM   dba_2pc_pending;

-- Tune the distributed lock timeout (default: 60 seconds)
ALTER SYSTEM SET distributed_lock_timeout = 60 SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00060 Deadlock detected — primary trigger for ORA-02091
ORA-02050 Distributed transaction rolled back; some remote DBs in-doubt
ORA-02055 Distributed update operation failed
ORA-01013 Operation canceled by user or system
ORA-02090 Network or communication failure in distributed environment

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