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

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


Cause: An attempt was made to access resource that is locked by a dead two-phase commit transaction that is in prepared state.


Action: The database administrator 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, the database administrator should contact the database administrator at the commit point if known or the 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.



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



I have the following problem:

SQL> select count(*) from wlpi11.notifierpost;
select count(*) from wlpi11.notifierpost
*
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 6.42.20551

I tried to release the lock:
SQL> commit force '6.42.20551';
commit force '6.42.20551'
*
ERROR at line 1:
ORA-02058: no prepared transaction found with ID 6.42.20551

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.42.20551');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.42.20551'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_TRANSACTION", line 86
ORA-06512: at line 1

I log on as sys then I try again:
SQL> execute dbms_transaction.purge_lost_db_entry('6.42.20551');
BEGIN dbms_transaction.purge_lost_db_entry('6.42.20551'); END;

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1




======================================================
======================================================
Solution
======================================================
======================================================




There are transactions involving at least 2 different databases in your system and one of your transaction is waiting on one of these distributed transactions which is taking too much time.


When you got ORA-1591 error, first of all, check the view DBA_2PC_PENDING. COMMIT FORCE or ROLLBACK FORCE the in-doubt transactions.

Sometimes, it is not possible because you do not have rows in DBA_2PC_PENDING view. If this is the case, you can insert dummy rows into SYS.PENDING_TRANS$, and SYS.PENDING_SESSIONS$ tables to correct this. Below is a script that can help you if you do not have rows in DBA_2PC_PENDING view. Just enter the TRANSACTION ID, got from ORA-1591 error or from alertlog files.

--
-- Execute it connected as SYS or SYSDBA
--
ACCEPT TRANSACTION_ID PROMPT "Enter TRANSACTION ID: "
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
INSERT INTO pending_trans$
(local_tran_id, global_tran_fmt, global_oracle_id, state,
status, session_vector, reco_vector, type#, fail_time,
reco_time
)
VALUES ('&&TRANSACTION_ID', 306206, /* */
'XXXXXXX.12345.1.2.3',
/* These values can be used without any */
'prepared',
'P', /* modification. Most of the values are */
HEXTORAW ('00000001'), /* constant. */
HEXTORAW ('00000000'), /* */
0, SYSDATE,
SYSDATE
);
INSERT INTO pending_sessions$
VALUES ('&&TRANSACTION_ID', 1, HEXTORAW ('05004F003A1500000104'), 'C', 0,
30258592, '', 146);
COMMIT ;
ROLLBACK FORCE '&&TRANSACTION_ID'; /* or commit force */
COMMIT ;
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
EXEC dbms_transaction.purge_lost_db_entry( '&&TRANSACTION_ID' );
COMMIT ;
DELETE FROM pending_trans$
WHERE local_tran_id = '&&TRANSACTION_ID';
DELETE FROM pending_sessions$
WHERE local_tran_id = '&&TRANSACTION_ID';
COMMIT ;



Ref:

See also from http://asktom.oracle.com/pls/ask/f?p=4950:8:7371047699027114947::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:839412906735

See also Managing Distributed Transactions in

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76960/ds_txnma.htm#9067