There are some situations when a critical data or rollback segment is locked, but you cannot find the blocker session. In those cases you can suspect an in-doubt transaction. When you use the two-phase commit mechanism -- such as using a joined query with dblink -- there are some situations when your transaction becomes in-doubt. Distributed transactions become in-doubt in the following ways:
- A server machine running Oracle software crashes.
- A network connection between two or more Oracle databases involved in distributed processing is disconnected.
- An unhandled software error occurs.
The user application that commits a distributed transaction is informed of a problem by one of the following error messages:
You can manually force the commit or rollback of a local, in-doubt distributed transaction. Usually you do not need to manually handle the in-doubt transaction, but in a situation when it has locked a critical data or rollback segment, you cannot wait until Oracle will handle it, and you need to commit or rollback the blocker transaction manually. Because this operation can generate consistency problems, perform it only when specific conditions exist.
To manually override in-doubt transactions, use the COMMIT or ROLLBACK statement with the FORCE option and a text string that indicates either the local or global transaction ID of the in-doubt transaction to commit.
SQL> select * from DBA_2PC_PENDING; SQL> select * from sys.PENDING_TRANS$; SQL> COMMIT FORCE 'transaction_id'; or SQL> ROLLBACK FORCE 'transaction_id';The variable transaction_id is the identifier of the transaction as specified in either the LOCAL_TRAN_ID or GLOBAL_TRAN_ID columns of the DBA_2PC_PENDING data dictionary view. These methods have been tested in 8.0.5, and all versions of 8i.
Bill C. writes: The tip is okay as far as it goes. However, there are systems that require no in-doubt transactions. How to delete them is an interesting challenge for the DBAs who aren't afraid of being de-supported if they get it wrong. BTW: Another reason for an in-doubt transaction is that a third-party agent (such as BEA) is managing the transaction and the connection to the database has failed in mid-transaction.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.