Distributed Transactions

Submitted by dave on Wed, 01/27/2010 - 12:14

Distributed Transactions are transactions that occur across multiple nodes. These nodes can be databases (Oracle, SQL Server, MySQL, etc), Application Servers, or any system that implements the Distributed Transaction Processing Interface (XA). OraFaq has a great page about Oracle XA here.

They are useful for coordinating data modification across multiple systems. A simple example is 2 Oracle databases that are connected via a DB link. If you create update data in the local and the remote database, a Distributed Transaction is used to coordinate the commit or rollback at the end of your transaction. A more complex example is a WebLogic application server that connects to an Oracle, SQL Server, and a MySQL database. Weblogic can start a transaction that coordinates all 3 databases. A successful commit of the Distributed Transaction whole guarantees to the customer that all data across all databases was successfully committed.

The biggest issue I have with Oracle and Distributed transactions is that the Oracle documentation is very Oracle centric. There do not seem to be any indications that Distributed Transactions can come from anywhere but over a DB link. The Troubleshooting guides all mention 'going to the source database'... well Distributed Transactions can be implemented by any number of software packages on the client side - a SQL Server database, MSDTC, a Java application server, or anything else following the XA standard.

To dig deeper into a Distributed Transaction issue you may need to visit diagnostic data on all nodes. This will probably be the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views on the Oracle database. For an Application Server involved in a Distributed Transaction you may need to visit application logs.

Oracle Documentation
10g Admin Guide, Distributed Database Management chapters

100664.1 "How to Troubleshoot Distributed Transactions"
13229.1 "Distributed Database, Transactions and Two Phase Commit"
126069.1 "Manually Resolving In-Doubt Transactions: Different Scenarios"