In-Doubt Transactions in Distributed Database
The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. A transaction becomes in-doubt if the two-phase commit mechanism fails. RECO process automatically resolves in-doubt transactions when the system, network, or software problem is resolved.
Manual Resolution of In-Doubt Transactions
Need to resolve an in-doubt transaction manually in the following cases:
–The in-doubt transaction has locks on critical data or undo segments.
–The cause of the system, network, or software failure cannot be repaired quickly.
Viewing Information About Distributed Transactions
DBA_2PC_PENDING view to determine the global commit number for a particular transaction ID. Lists all in-doubt distributed transactions. The view is empty until populated by an in-doubt transaction. After the transaction is resolved, the view is purged.
COL LOCAL_TRAN_ID FORMAT A13
COL GLOBAL_TRAN_ID FORMAT A30
COL STATE FORMAT A8
COL MIXED FORMAT A3
COL HOST FORMAT A10
COL COMMIT# FORMAT A10
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT# FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT# ------------- -------------- ------ --- -------- --------- 1.15.870 1.15.870 commit no dlsun183 115499
that local transaction 1.15.870 has been committed on this node,but it may be pending on one or more other nodes as per COMMIT State.
The local and global has same value so node is global coordinator.
Note: When LOCAL_TRAN_ID and GLOBAL_TRAN_ID value is same then node is the global coordinator of the transaction.
LOCAL_TRAN_ID : Local transaction identifier
GLOBAL_TRAN_ID : Global database identifier
STATE : –Collecting: node is currently collecting information from other database servers before it can decide whether it can prepare.
–Prepared: Node has prepared and holding locks on resources. May or not acknowledge to local coordinator
–Committed: Node has committed the transaction but other node may or may not done.
–Forced Commit: Administrator manually forced to commit the pending transaction at local node.
–Forced rollback: A pending transaction forced to rollback at local node.
MIXED: YES means part of transaction committed on one node and rollback at other node.
TRAN_COMMENT: Transaction comment
COMMIT# : Global commit number for committed transaction
DBA_2PC_NEIGHBORS :Lists all incoming and outgoing in-doubt distributed transactions. It also indicates whether the local node is the commit point site in the transaction.
LOCAL_TRAN_ID: Local transaction identifier
IN_OUT: IN for incoming transactions; OUT for outgoing transactions
DATABASE: For incoming transactions, the name of the client database for outgoing transactions, the name of the database link used to access information on a remote server.
DBUSER_OWNER: For incoming transactions, the local account used to connect, for outgoing transactions: the owner of the database link.
INTERFACE: C is a commit message; N is either indicating a prepared state or read-only commit.
COL LOCAL_TRAN_ID FORMAT A13
COL IN_OUT FORMAT A6
COL DATABASE FORMAT A25
COL DBUSER_OWNER FORMAT A15
COL INTERFACE FORMAT A3
SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, DBUSER_OWNER, INTERFACE FROM DBA_2PC_NEIGHBORS
LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INT ------------- ------ ------------------------- --------------- --- 1.15.870 out SALES.EXAMPLE.COM SYS C
For more info: Example for Handling the In-Doubt transaction manually