Two-Phase Commit

A 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 as the commit server, the record keeper that helps the application determine whether to commit or to roll back transactions, which means that either all databases on the participating servers are updated or that none 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 sends the following statements to each participating server:

• A BEGIN TRANSACTION statement that includes identifying information about the application, the transaction, and the commit server
  
  
• Transact-SQL update statements
  
  
• A PREPARE TRANSACTION statement, which indicates the updates have been performed and the server is prepared to commit (This statement can’t 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. All servers agree that they are ready to commit.
   
   
2. All servers commit—that is, a COMMIT TRANSACTION statement is sent to all servers.

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