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

This Error not allow application sql query to run on the table that hold lock through distributed transaction.

Application team may hit below error while try to execute any sql on lock table.

 DB00153):  Get row failed for db statement.
 (DB10032):  Failed executing SQL.
 (DB10032):  Oracle Error: ORA-01591: lock held by in-doubt distributed transaction 9.6.339635
 (DB10032):  SQL: select ltrim(rtrim(V.ACCOUNT_NBR)) as ACCOUNT_NBR ,V.ACCOUNT_ID,V.OTHER_PAYMENT_AMOUNT,V.BANK_ACCOUNT_ID,V.PAYMENT_AMOUNT_TYPE_ID,V.BANK_ACCOUNT_TYPE_CD,V.BANK_ACCOUNT_NBR,V.BANK_ROUTING_NBR from v_autopay V inner join purgatory_autopay_enrollment PAE on V.account_id = PAE.account_id where PAE.AUTOPAY_ENROLLMENT_STATUS = '2' and to_char(V.NEXT_CYCLE_DT,'YYYYMMDD') between (to_char((SYSDATE -5), 'YYYYMMDD')) and (to_char((SYSDATE-1 ), 'YYYYMMDD'))
 (DB10032):  Parse Error Offset: 0
 (DB10034):  ORA-01591: lock held by in-doubt distributed transaction 9.6.339635
 (*):        Database Package Version 3-2-6-2-e11-1

Before moving to solution what is distributed transaction: 

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, assume the database configuration depicted in Figure :

Figure  : Distributed System

The following distributed transaction executed by scott updates the local sales database, the remote hq database, and the remote maint database:

UPDATE scott.dept@hq.us.acme.com

  SET loc = 'REDWOOD SHORES'

  WHERE deptno = 10;

UPDATE scott.emp

  SET deptno = 11

  WHERE deptno = 10;

UPDATE scott.bldg@maint.us.acme.com

  SET room = 1225

  WHERE room = 1163;

COMMIT;

You can execute DML and DDL statements in parallel, and INSERT direct load statements serially, but note the following restrictions:

•           All remote operations must be SELECT statements.

•           These statements must not be clauses in another distributed transaction.

•           If the table referenced in the table_expression_clause of an INSERT, UPDATE, or DELETE     statement is remote, then execution is serial rather than parallel.

•           You cannot perform remote operations after issuing parallel DML/DDL or direct load INSERT.

•           If the transaction begins using XA or OCI, it executes serially.

•           No loopback operations can be performed on the transaction originating the parallel operation. For example, you cannot reference a remote object that is actually a synonym for a local object.

•           If you perform a distributed operation other than a SELECT in the transaction, no DML is parallelized.

In-Doubt Transactions :

The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if any of the three phases fails because of a system or network error? The transaction becomes in-doubt.

Distributed transactions can become in-doubt in the following ways:

•           A server machine running Oracle software crashes

•           A network connection between two or more Oracle databases involved in distributed processing is disconnected

•           An unhandled software error occurs

•           If RECO process is not running or terminated

The RECO process automatically resolves in-doubt transactions when the machine, network, or software problem is resolved. Until RECO can resolve the transaction, the data is locked for both reads and writes. Oracle blocks reads because it cannot determine which version of the data to display for a query.

Manual Resolution of In-Doubt Transactions :

You should only need to resolve an in-doubt transaction in the following cases:

•           The in-doubt transaction has locks on critical data or rollback segments.

•           The cause of the machine, network, or software failure cannot be repaired quickly.

Resolution of in-doubt transactions can be complicated. The procedure requires that you do the following:

•           Identify the transaction identification number for the in-doubt transaction.

•           Query the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views to determine whether the databases involved in the transaction have committed.

•           If necessary, force a commit using the COMMIT FORCE statement or a rollback using the ROLLBACK FORCEstatement.

To find out more details use below views: 

select * from dba_2pc_pending;

select * from dba_2pc_neighbors;

select * from sys.pending_trans$;

select * from sys.pending_sessions$;

select * from sys.pending_sub_sessions$;

select * from dba_pending_transactions;

Here are some codes to help you through the process:

This one brings in-doubt transactions details:

select * from DBA_2PC_PENDING where state='prepared';

 Below  one prepares the rollback script for the transactions:

select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

 At last run the  below script to purge the force /rollback  transaction with the transaction id:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<LOCAL_TRAN_ID');

Reference: https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txns001.htm#ADMIN12213