ORA-01591: lock held by in-doubt distributed transaction and then purge the in-doubt transaction

Issue

ORA-01591: lock held by in-doubt distributed transaction

Error Message

ORA-01591: lock held by in-doubt distributed transaction 2.96.17311

When Executing

SELECT *
FROM COMPLAINT
WHERE case_no = 'AP1706098703';

Error Description

ORA-01591 lock held by in-doubt distributed transaction

Cause:

Trying to access a resource that is locked by a dead two-phase commit

transaction that is in PREPARED state.

 

Action:

DBA should query pending distributed transactions and either

commit or rollback the in-doubt transaction.

---

Root Cause

• A distributed (two-phase commit) transaction was left in a PREPARED state.
• The coordinating database or network connection was interrupted.
• The transaction continues to hold locks, blocking access to the table.

---

Solution

Step 1: Identify the In-Doubt Transaction

SELECT * FROM dba_2pc_pending;

Output Example

LOCAL_TRAN_ID
-----------------
2.96.17311

---

Step 2: Verify Transaction State

SELECT local_tran_id, state, status FROM sys.pending_trans$;

Result

LOCAL_TRAN_ID   STATE     STATUS
--------------  --------  ------
2.96.17311      PREPARED  P

---

Step 3: Force the Transaction Resolution

Option 1: Force COMMIT (when changes are valid)

COMMIT FORCE '2.96.17311';

Option 2: Force ROLLBACK (if changes should be discarded)

ROLLBACK FORCE '2.96.17311';

✅ In this case, COMMIT FORCE was executed successfully.

---

Step 4: Purge the In-Doubt Transaction Entry

After resolving the transaction, remove the entry from Oracle’s internal tables:

EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('2.96.17311');

COMMIT;

---

Result

• Lock released successfully
• Table access restored
• Query executes without ORA-01591 error

---

Important Notes

• Always confirm with application owners before forcing COMMIT or ROLLBACK.
• Incorrect resolution can cause data inconsistency.
• This operation should be performed by a DBA or under DBA supervision.

---