Programming distributed transactions

The two-phase commit service allows an application to coordinate updates among two or more Adaptive Server Enterprises. This initial implementation of distributed transactions treats separate transactions (which may be on separate Adaptive Server Enterprises) as if they were a single transaction. The commit service uses one Adaptive Server Enterprise, the “commit server,” as a central record-keeper that helps the application determine whether to commit, or whether to roll back transactions in case of failure. Thus, the two-phase commit guarantees that either all or none of the databases on the participating servers are updated.

A distributed transaction is performed by submitting Transact-SQL statements to the Adaptive Server Enterprises through DB-Library routines. An application program opens a session with each server, issues the update commands, and then prepares to commit the transaction. Through DB-Library, the application issues the following to each participating server:

• A begin transaction with identifying information on 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

After the updates have been performed on all the servers participating in the distributed transaction, the two-phase commit begins. In the first phase, all servers agree that they are ready to commit. In the second phase, the application informs the commit service that the transaction is complete (that is, the commit will take place), and a commit transaction is then issued to all of the servers, causing them to commit.

If an error occurs between phase one and phase two, all servers coordinate with the commit service to determine whether the transaction should be committed or aborted.