User's Guide
   PART 4. Database Administration and Advanced Use
     CHAPTER 28. Accessing Remote Data       

Transaction management and remote data

Transactions provide a way to group SQL statements so that they are treated as a unit—either all work performed by the statements is committed to the database, or none of it is.

For the most part, transaction management with remote tables is the same as transaction management for local tables in Adaptive Server Anywhere, but there are some differences. They are discussed in the following section.

     For a general discussion of transactions, see Using Transactions and Locks.

  Remote transaction management overview

The method for managing transactions involving remote servers uses a two-phase commit protocol. Adaptive Server Anywhere implements a strategy that ensures transaction integrity for most scenarios. However, when more than one remote server is invoked in a transaction, there is still a chance that a distributed unit of work will be left in an undetermined state. Even though two-phase commit protocol is used, no recovery process is included.

The general logic for managing a user transaction is as follows:

1. Adaptive Server Anywhere prefaces work to a remote server with a BEGIN TRANSACTION notification.

2. When the transaction is ready to be committed, Adaptive Server Anywhere sends a PREPARE TRANSACTION notification to each remote server that has been part of the transaction. This ensures the that remote server is ready to commit the transaction.

3. If a PREPARE TRANSACTION request fails, all remote servers are told to roll back the current transaction.

   If all PREPARE TRANSACTION requests are successful, the server sends a COMMIT TRANSACTION request to each remote server involved with the transaction.

Any statement preceded by BEGIN TRANSACTION can begin a transaction. Other statements are sent to a remote server to be executed as a single, remote unit of work.

  Restrictions on transaction management

Restrictions on transaction management are as follows:

• Savepoints are not propagated to remote servers.

• If nested BEGIN TRANSACTION and COMMIT TRANSACTION statements are included in a transaction that involves remote servers, only the outermost set of statements is processed. The innermost set, containing the BEGIN TRANSACTION and COMMIT TRANSACTION statements, is not transmitted to remote servers.