One of the user reported they got this error from application.
ORA-01591: lock held by in-doubt distributed transaction 4.7.533420
We don't really see this error often. So I did a little research.
The error message doc from Oracle has pretty good explanation but didn't provide a solution how to resolve this.
ORA-01591: | lock held by in-doubt distributed transaction string |
| Cause: | Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state. |
| Action: | DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction. |
What I end up did is pretty easy, rollback force didn't do the trick. The DBMS_TRANSACTION helped.
SQL> select local_tran_id from dba_2pc_pending;
LOCAL_TRAN_ID
----------------------
4.7.533420
SQL> rollback force '4.7.533420';
Rollback complete.
SQL> select local_tran_id from dba_2pc_pending;
LOCAL_TRAN_ID
----------------------
4.7.533420
SQL> exec dbms_transaction.purge_lost_db_entry('4.7.533420');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select local_tran_id from dba_2pc_pending;
no rows selected
