Programming Distributed Transactions

The two-phase commit service allows an application to coordinate updates among multiple SQL Servers. This implementation of distributed transactions treats transactions on separate SQL Servers as a single transaction. The service uses one SQL Server, the commit server, as a record keeper that helps the application determine whether to commit or to roll back transactions. Thus, the two-phase commit guarantees that either all the databases on the participating servers are updated or that none of them are.

A distributed transaction submits Transact-SQL statements to SQL Servers through DB-Library functions. An application opens a session with each server, executes the update commands, and then prepares to commit the transaction. Through DB-Library, the application executes the following to each participating server:

• A BEGIN TRANSACTION statement with identifying information about the application, the transaction, and the commit server.
• The Transact-SQL update statements.
• A PREPARE TRANSACTION statement that indicates that the updates have been performed and that the server is prepared to commit. This statement cannot be used outside the context of DB-Library.

After all servers participating in the distributed transaction have been updated, the two-phase commit begins:

1. In the first phase, all servers agree that they are ready to commit.
2. In the second phase, they all commit ¾ a COMMIT TRANSACTION statement is executed to all servers.

The application then tells the commit service that the transaction is complete, and the connections are closed. If an error occurs between phases one and two, all servers coordinate with the commit service to see if the transaction should be committed or canceled.