ora-01591 lock held by in-doubt distributed transaction

It is possible that an application displays in its log an ORA-01591 error, which according to official documentation is due to a 2-phase trasacción that can not be completed.

Such transactions are used, for example, when a commit using tables obtained from a dblink becomes.It verifies that you can make a remote commits, another local and finally performs in the 2 locations atomically.

Well when connecting with a dblink is lost during a transaction, the transaction is in an uncertain state and if another session attempts to modify the objects involved see the ORA-01591 error.

The real problem is a lock held on an object, to detect and make a roolback him to do:

1- Locate pending transactions, this can be done with the query:

Source   
SELECT * FROM pending_trans$

or

Source   
SELECT * FROM DBA_2PC_PENDING

These queries include the STATE field if it is equal to “Prepared” is possibly the transaction is giving us problems, we take the value of the “LOCAL_TRAN_ID ‘.

2- Cancellation of the transaction (where ‘LOCAL_TRAN_ID’ was obtained in the previous step):

Source   
ROLLBACK FORCE 'LOCAL_TRAN_ID';

or

Source   
execute dbms_transaction.rollback_force('LOCAL_TRAN_ID');

3- Deleting entries in the dictionary, that is delete the entry from the tables that we have consulted:

Source   
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');

Problem solved.

Leave a Reply