LLI Wiki — My Digital Knowledge Base

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.

Mastodon LLIMastodon SocialFosstodon