ORA-01591: lock held by in-doubt distributed transaction
ORA-01591: lock held by in-doubt distributed transaction
If we got ‘ORA-01591:
lock held by in-doubt distributed transaction’ error, it means there are some
uncommitted transactions in the database. We need to perform rollback or commit
force for uncommitted transactions to solve this problem.
“Trying to
access resource that is locked by a dead two-phase commit transaction that is
in prepared state”. The two phase-commit mechanism is used to ensure data
integrity in a distributed transaction.
Example of
handling the In-Doubt Transactions - in this example we are taking two servers ACCT
and FIN.
Got error ORA-1591
: lock held by in-doubt distributed transaction 3.1.855 at FIN Server
Scenario A: - LOCAL_TRAN_ID and
GLOBAL_TRAN_ID are same.
Execute below query in SYS user at FIN server.
SQL> SELECT
LOCAL_TRAN_ID, STATE FROM
dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID
STATE
------------- ---------------
----------------
3.1.855
--------.3.1.855 prepared
Here local id and Global id is same, so it is the global
coordinator.
State column is prepared means server wait for the coordinator to
send commit or roll-back request.
[ Execute below query in SYS user at ACCT server.
SQL> SELECT
LOCAL_TRAN_ID, STATE FROM
dba_2pc_pending;
no rows selected ]Means 'nothing' happened at ACCT
Server end.
At FIN Server
Rollback of local part of transaction is required at FIN Server-
SQL> EXECUTE Dbms_Transaction.ROLLBACK_FORCE('3.1.855')
PL/SQL PROCEDURE
successfully completed.
SQL> COMMIT;
COMMIT COMPLETE.
SQL> SELECT
LOCAL_TRAN_ID , STATE FROM
dba_2pc_pending;
LOCAL_TRAN_ID
STATE
---------------------- ----------------
3.1.855
forced ROLLBACK
To purge the in-doubt transaction entry at FIN Server -
SQL> EXECUTE Dbms_Transaction.PURGE_LOST_DB_ENTRY('3.1.855')
PL/SQL PROCEDURE
successfully completed.
SQL> COMMIT;
COMMIT COMPLETE.
SQL> SELECT
LOCAL_TRAN_ID, STATE FROM
dba_2pc_pending;
no rows selected
Scenario B: - LOCAL_TRAN_ID and
GLOBAL_TRAN_ID are different.
Some more articles you
might also be interested in :-
Distributed transaction in oracle
ORA-02050: transaction rolled back, some remote DBs may be
in-doubt
ORA-01591: lock held by in-doubt distributed transaction
ORA-31693: Table data object failed to load/unload in expdp
Comments
Post a Comment