Distributed Transactions

Oracle automatically initiates a distributed transaction 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.

By default, SQL Server must be instructed to participate in a distributed transaction. SQL Server’s participation in an MS DTC transaction can be started by either of the following:

• The BEGIN DISTRIBUTED TRANSACTION statement. This statement begins a new MS DTC transaction.
• A client application calling DTC transaction interfaces directly.

In the example, notice the distributed update to both the local table GRADE and the remote table CLASS (using a class_name procedure):

BEGIN DISTRIBUTED TRANSACTION

UPDATE STUDENT_ADMIN.GRADE

    SET GRADE = 'B+' WHERE SSN = '111111111' AND CCODE = '1234'

DECLARE @retvalue1 int

EXECUTE @retvalue1 = CLASS_SVR1.dept_db.dept_admin.class_name '1234',     'Basketweaving'

COMMIT TRANSACTION

GO

If the application cannot complete the transaction, the application program cancels it by using the ROLLBACK TRANSACTION statement. If the application fails or a participating resource manager fails, MS DTC cancels the transaction. MS DTC does not support distributed savepoints or the SAVE TRANSACTION statement. If an MS DTC transaction is aborted or rolled back, the entire transaction is rolled back to the beginning of the distributed transaction, regardless of any savepoints.