Oracle: ORA-01591, lock held by an in-doubt transaction
Find the in-doubt distributed transactions (DBA_2PC_PENDING) that lock a row and generate the matching ROLLBACK FORCE statements.
The error ORA-01591: lock held by in-doubt distributed transaction appears when a row is locked by a distributed transaction that stayed in doubt, usually after an interrupted two-phase commit. The affected transactions are listed in the view DBA_2PC_PENDING.
List the in-doubt transactions
select * from DBA_2PC_PENDING where state='prepared';
Generate the ROLLBACK FORCE statements
select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'
Example of a generated statement (transaction identifier replaced):
rollback force '<local_tran_id>';
ROLLBACK FORCE is a manual resolution: use it only when the transaction cannot be resolved automatically.
Orphan sessions
If the transaction stays locked, the source note points to Oracle Support note 2580865.1 (support account required) for removing orphan sessions. To identify the orphan user, it suggests querying the view GV$OPEN_CURSOR.