In-doubt Distributed Transactions

In-doubt Distributed Transactions are those distributed transactions whose commit source is lost.

set linesize 200

set pagesize 100

column LOCAL_TRAN_ID format a25

column GLOBAL_TRAN_ID format a25

select local_tran_id,global_tran_id,state,mixed, host, commit# from dba_2pc_pending;

Possible STATE values are COLLECTING, COMMITTED, PREPARED, FORCED COMMIT, FORCED ROLLBACK.

These transactions can either commited or rolled back manually provided the user is having required previlieges.

FORCE TRANSACTION - For the owner of transaction,

FORCE ANY TRANSACTION - For user other the owner

Manually Committing an In-Doubt Transaction:

--Using 'LOCAL_TRAN_ID'

--column of DBA_2PC_PENDING table.

COMMIT FORCE 'LOCAL_TRAN_ID';

--Example:  

COMMIT FORCE '1.45.13';

--Using an SCN

--GLOBAL_TRAN_ID - remote database involved with the transaction.

--SCN - Used for the commit of the transaction on that remote node.

select GLOBAL_TRAN_ID,SCN from DBA_2PC_PENDING; 

--Commit forcibily

COMMIT FORCE 'GLOBAL_TRAN_ID',SCN;

--Example

COMMIT FORCE 'SALES.COM.55d1c563.1.93.29', 829381993;

Manually Rolling Back an In-Doubt Transaction:

--Using 'LOCAL_TRAN_ID'

--column of DBA_2PC_PENDING table.

ROLLBACK FORCE 'transaction_id';

--Example,

ROLLBACK FORCE '2500.16.262202';

In-doubt transactions cannot be roll backed to a savepoint.

Few other Tables to work with pending transactions:

select * DBA_2PC_PENDING;

select * from DBA_2PC_NEIGHBORS;

select * from sys.pending_trans$;

select * from SYS.PENDING_SESSIONS$;

select * from SYS.PENDING_SUB_SESSIONS$