ORA-24761: Transaction Rolled Back - Handle Distributed Transaction Failures

ORA-24761: Transaction Rolled Back

Error Overview

Error Text: ORA-24761: transaction rolled back

The ORA-24761 error is raised when Oracle has forcibly rolled back a transaction, most commonly in the context of distributed transactions, serialization conflicts, or deadlock-adjacent scenarios. Unlike ORA-00060 (deadlock), ORA-24761 frequently appears in conjunction with ORA-02049 (distributed transaction timeout) or as a result of Oracle’s internal transaction management choosing to sacrifice one transaction to resolve a conflict. The error signals that the application must handle a rollback that it did not explicitly request and, in most cases, should retry the failed operation.

Common Causes

1. Distributed Transaction Failures (ORA-02049 Relationship)

A distributed transaction (involving database links) exceeded the DISTRIBUTED_LOCK_TIMEOUT parameter
The remote database became unavailable mid-transaction, leaving the transaction in a prepared but uncommitted state
Oracle chose to roll back the local participant to resolve a distributed deadlock
A two-phase commit coordinator timed out, forcing the subordinate to roll back

2. Serialization Failures with Serializable Isolation

A transaction using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE encountered a conflicting change made after its read snapshot
The application expected strict serializability but the database detected a write-write conflict
Long-running serializable transactions in high-concurrency OLTP environments

3. Transaction Table Overflow and Internal Conflicts

Internal Oracle resource contention causing a transaction slot to be reused
Shared server configuration issues causing transaction state loss
RAC cross-instance transaction conflicts under extreme concurrency

4. Application-Level Misuse of Savepoints and Rollbacks

Application calling ROLLBACK TO SAVEPOINT incorrectly, causing the entire transaction to be rolled back by Oracle
Implicit rollbacks triggered by DDL statements executed inside a transaction
Connection pool returning a dirty connection with a pending transaction that Oracle rolls back on reuse

5. ORA-24761 from Autonomous Transactions

An autonomous transaction block raising an unhandled exception, causing Oracle to roll back the autonomous transaction
The calling transaction receiving ORA-24761 as a side effect of the autonomous block’s failure

Diagnostic Queries

Identify Active Distributed Transactions

-- View in-doubt distributed transactions
SELECT
  local_tran_id,
  global_tran_id,
  state,
  mixed,
  host,
  db_user,
  os_user,
  commit#
FROM dba_2pc_pending
ORDER BY local_tran_id;

-- Pending distributed transaction participants
SELECT
  local_tran_id,
  interface,
  dbuser_owner,
  link
FROM dba_2pc_neighbors
ORDER BY local_tran_id;

-- Distributed lock timeout parameter
SHOW PARAMETER distributed_lock_timeout;

Check for Serialization Conflicts

-- Identify serializable transactions currently active
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.sql_id,
  t.isolation_level,
  t.start_scn,
  t.start_time
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE t.isolation_level = 'SERIALIZABLE'
ORDER BY t.start_time;

-- Historical serialization failures from AWR
SELECT
  TO_CHAR(sample_time, 'DD-MON-YYYY HH24:MI:SS') AS sample_time,
  session_id,
  sql_id,
  event,
  wait_class
FROM dba_hist_active_sess_history
WHERE sql_id IN (
  SELECT sql_id FROM v$sql WHERE sql_text LIKE '%ORA-24761%'
)
  AND sample_time > SYSDATE - 7
ORDER BY sample_time DESC;

Examine Recent Transaction Errors

-- Check alert log for ORA-24761 occurrences
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%24761%'
   OR message_text LIKE '%ORA-02049%'
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

-- Active transactions with database link activity
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.sql_id,
  s.machine,
  s.program,
  t.used_urec,
  t.used_ublk
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
WHERE s.sql_id IN (
  SELECT sql_id FROM v$sql WHERE sql_text LIKE '%@%'
)
ORDER BY t.start_time;

Check Database Link Configuration

-- View database links accessible to the current user
SELECT
  owner,
  db_link,
  username,
  host,
  created
FROM dba_db_links
ORDER BY owner, db_link;

-- Check for open database link sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.machine,
  s.program,
  s.sql_id
FROM v$session s
WHERE s.type = 'USER'
  AND s.username IS NOT NULL
ORDER BY s.logon_time DESC;

Investigate ORA-00060 Deadlock Relationship

-- Deadlock history from AWR (ORA-24761 often follows a deadlock resolution)
SELECT
  TO_CHAR(snap_time, 'DD-MON-YYYY HH24:MI') AS snap_time,
  executions_delta,
  rows_processed_delta,
  optimizer_cost
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn USING (snap_id, dbid, instance_number)
WHERE s.sql_id IN (
  SELECT sql_id FROM v$sql WHERE last_oracle_error IN (24761, 2049, 60)
)
  AND sn.begin_interval_time > SYSDATE - 1
ORDER BY snap_time DESC;

Step-by-Step Resolution

1. Resolve In-Doubt Distributed Transactions

-- List all in-doubt transactions
SELECT local_tran_id, global_tran_id, state, host
FROM dba_2pc_pending;

-- If the transaction is confirmed committed on all nodes, force commit:
EXECUTE DBMS_TRANSACTION.COMMIT_FORCED('local_tran_id_here');

-- If the transaction should be rolled back:
EXECUTE DBMS_TRANSACTION.ROLLBACK_FORCED('local_tran_id_here');

-- After forcing, verify it is cleared from dba_2pc_pending
SELECT COUNT(*) FROM dba_2pc_pending;

-- Purge the two-phase commit log if all pending transactions are resolved
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id_here');

2. Adjust DISTRIBUTED_LOCK_TIMEOUT

-- Increase timeout to reduce spurious ORA-24761 on slow network links
-- Default is 60 seconds; increase to 120 or 300 for WAN-connected databases
ALTER SYSTEM SET distributed_lock_timeout = 120 SCOPE=BOTH;

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

3. Implement Application Retry Logic

For applications receiving ORA-24761, the correct response is to retry the transaction. The following PL/SQL pattern demonstrates a robust retry loop:

-- Retry pattern for transactions susceptible to ORA-24761
CREATE OR REPLACE PROCEDURE process_with_retry(
  p_max_retries IN NUMBER DEFAULT 3,
  p_retry_wait  IN NUMBER DEFAULT 2  -- seconds between retries
) AS
  v_retry_count NUMBER := 0;
  e_rollback    EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_rollback, -24761);
  e_dist_lock   EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_dist_lock, -2049);
BEGIN
  LOOP
    BEGIN
      -- Your transactional work here
      INSERT INTO target_table SELECT * FROM source_table@remote_db WHERE status = 'PENDING';
      UPDATE target_table SET status = 'PROCESSED' WHERE status = 'PENDING';
      COMMIT;
      EXIT;  -- Success: exit retry loop

    EXCEPTION
      WHEN e_rollback OR e_dist_lock THEN
        v_retry_count := v_retry_count + 1;
        ROLLBACK;
        IF v_retry_count >= p_max_retries THEN
          RAISE;  -- Exhausted retries: propagate the error
        END IF;
        DBMS_SESSION.SLEEP(p_retry_wait);  -- Wait before retrying
    END;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

4. Switch From Serializable to Read Committed Where Appropriate

-- Check if serializable isolation is actually required
-- Most OLTP applications work correctly with the default READ COMMITTED isolation

-- Change a session from serializable to read committed
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Or change the application's JDBC connection:
-- conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

-- If serializable is genuinely required, reduce transaction duration
-- by selecting only the rows actually needed rather than scanning large sets

5. Fix Autonomous Transaction Error Handling

-- Ensure autonomous transactions handle exceptions gracefully
CREATE OR REPLACE PROCEDURE log_audit_event(
  p_event VARCHAR2,
  p_user  VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_log (event_text, username, event_time)
  VALUES (p_event, p_user, SYSTIMESTAMP);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- Always commit or rollback in autonomous transactions
    -- to avoid leaving the calling transaction in an indeterminate state
    ROLLBACK;
    -- Optionally re-raise or log to a fallback mechanism
END;
/

Prevention Strategies

1. Minimise Distributed Transaction Duration

-- Keep distributed transactions as short as possible
-- BAD: Long-running distributed transaction
BEGIN
  -- Expensive local processing
  FOR i IN 1..100000 LOOP
    process_local_data(i);
  END LOOP;
  -- Then touch the remote DB (now the transaction is long)
  INSERT INTO remote_table@db_link VALUES (...);
  COMMIT;
END;

-- GOOD: Touch remote DB at the end, keep the window minimal
BEGIN
  -- Do all local work first
  FOR i IN 1..100000 LOOP
    process_local_data(i);
  END LOOP;
  COMMIT;  -- Commit local work separately

  -- New short transaction for distributed work
  INSERT INTO remote_table@db_link VALUES (...);
  COMMIT;
END;

2. Monitor In-Doubt Transaction Accumulation

-- Schedule a check for accumulating in-doubt transactions
CREATE OR REPLACE PROCEDURE check_2pc_pending AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM dba_2pc_pending;
  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || v_count || ' in-doubt distributed transactions pending');
    FOR rec IN (SELECT local_tran_id, state, host FROM dba_2pc_pending) LOOP
      DBMS_OUTPUT.PUT_LINE('  Tran: ' || rec.local_tran_id || ' State: ' || rec.state || ' Host: ' || rec.host);
    END LOOP;
  END IF;
END;
/

3. Configure Appropriate Isolation Levels

-- Document and enforce isolation level requirements per application module
-- Use v$session to confirm applications are using expected isolation levels
SELECT
  username,
  machine,
  program,
  COUNT(*) AS session_count
FROM v$session
WHERE type = 'USER'
  AND username IS NOT NULL
GROUP BY username, machine, program
ORDER BY session_count DESC;

-- Review transactions currently using serializable isolation
SELECT COUNT(*)
FROM v$transaction
WHERE isolation_level = 'SERIALIZABLE';

4. Connection Pool Configuration

Configure connection pools to roll back and reset connections before returning them to the pool
Use Oracle’s DRCP (Database Resident Connection Pooling) which handles session reset automatically
Set pool validation queries to detect dirty connection state before use

Diagnostic Scripts

These Oracle Day by Day scripts can help identify transaction and session issues:

gvsess.sql — Active session analysis with transaction state
health.sql — Database health including lock and transaction status

ORA-00060 - Deadlock detected while waiting for resource
ORA-02291 - Integrity constraint violated (parent key not found)
ORA-01555 - Snapshot too old

Emergency Response

Quick Fixes

Force resolve a stuck in-doubt distributed transaction

-- Commit if the transaction should have committed
EXECUTE DBMS_TRANSACTION.COMMIT_FORCED('4.3.123456');

-- Rollback if it should have rolled back
EXECUTE DBMS_TRANSACTION.ROLLBACK_FORCED('4.3.123456');

Identify and kill sessions holding distributed locks

SELECT sid, serial#, username, machine, program
FROM v$session
WHERE taddr IN (SELECT addr FROM v$transaction);

-- Kill the blocking session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Temporarily increase distributed lock timeout

ALTER SYSTEM SET distributed_lock_timeout = 300 SCOPE=MEMORY;

Post-Resolution Cleanup

-- Confirm all in-doubt transactions are cleared
SELECT COUNT(*) AS pending_count FROM dba_2pc_pending;

-- Review the alert log for repeated ORA-24761 / ORA-02049 patterns
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%24761%'
   OR message_text LIKE '%02049%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- Run AWR report to assess transaction throughput and conflict rate
@?/rdbms/admin/awrrpt.sql