Distributed Transactions in Oracle and SQL Server

Remote Transactions

To perform remote transactions in Oracle, you must have access to a remote database node with a database link. In Microsoft® SQL Server™, you must have access to a remote server. When a server is set up as a remote server, users can use the system procedures and the stored procedures on the remote server without explicitly logging in to it.

Distributed Transactions

Oracle initiates a distributed transaction automatically if changes are made to tables in two or more networked database nodes. SQL Server distributed transactions use the two-phase commit services of the Microsoft Distributed Transaction Coordinator (MS DTC), included with SQL Server.

Two-phase Commit Processing

The Oracle and MS DTC two-phase commit mechanisms are similar in operation. In the first phase, the transaction manager requests each enlisted resource manager (SQL Server in this case) to prepare to commit. If any resource manager cannot prepare, the transaction manager broadcasts a terminate decision to everyone involved in the transaction.

If all resource managers can prepare successfully, the transaction manager broadcasts the commit decision. This is the second phase of the commit process. While a resource manager is prepared, it is in doubt about whether the transaction is committed or rolled back. MS DTC keeps a sequential log so that its commit or roll back decisions are durable. If a resource manager or transaction manager fails, they reconcile in-doubt transactions when they reconnect.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.