Federated two-phase commit performance

Data sources that are configured for two-phase commit transactions incur a performance penalty when you compare them to data sources that are configured for one-phase commit transactions.

When a data source uses two-phase commit, the federated server acts as a coordinator, ensuring that all participants are correctly synchronized. This coordination is achieved by additional logging on the federated server and additional communication between data sources. As such, a federated transaction accessing a data source for two-phase commit requires more processing than a transaction that accesses a data source for one-phase commit. Consequently, a data source must be enabled for two-phase commit only when the federated transactions require two-phase commit.

A federated transaction that only requires one-phase commit, such as an update to a single site, but which is run on a data source for two-phase commit is likely to incur a performance penalty when you compare it to the same transaction that is run on a data source without two-phase commit.

A transaction under two-phase commit control incurs the following additional processing regardless of whether or not the transaction requires two-phase commit:

1. All transactions require additional database log writes to the federated server log file.

   The additional log writes enable the federated server to track the data sources in the transaction in order to coordinate subsequent commit and rollback operations.

2. All transactions require additional database communication between the federated server and the data source.
3. Insert, update, and delete operations require one or more additional database log writes to the remote data source.

Most of the additional processing occurs at the transaction boundaries and is not influenced by the contents within the transaction. As a result, the percentage increase in elapsed time for a short transaction is greater than that for a long transaction.

Concurrent transactions cause the federated server to write multiple log records from the log buffer to the log file in a single write operation. Consequently, applications running federated two-phase commit transactions concurrently incur less overhead than applications running the same transactions serially.