The method for managing transactions involving remote servers uses a two-phase commit protocol. SAP Sybase IQ 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:
SAP Sybase IQ prefaces work to a remote server with a BEGIN TRANSACTION notification.
When the transaction is ready to be committed, SAP Sybase IQ sends a PREPARE TRANSACTION notification to each remote server that has been part of the transaction. This ensures that the remote server is ready to commit the transaction.
If a PREPARE TRANSACTION request fails, all remote servers are instructed 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.