How federated two-phase commit transactions are processed

The federated server maintains data consistency and atomicity of the data sources that it manages. The range of possible transactions depends on the type of connection and whether the federated server is the transaction manager or resource manager for the connection.

Atomicity is a database principal in which sets of operations are defined within indivisible transactions. This principal ensures that the database is consistent at all times because if a single operation within the indivisible transaction fails, the whole transaction fails rather than compromising data integrity due to a partial change.

For example, a transaction to transfer funds from one account to another involves withdrawing funds from the first account and adding funds to the second account. If only the withdrawal succeeds, the funds essentially cease to exist in the first account.

The federated server processes federated update requests under strict rules. A federated update is one of the following actions:
  • A federated insert, update, or delete operation where the corresponding data source supports insert, update, or delete operations. For example, some data sources do not support update operations. Some data sources are read-only, in which case federated insert, update, or delete operations are not allowed.
  • A successful pass-through operation inside a pass-through session.
  • A transparent DDL operation, which is considered to be both a local update and a federated update because it performs database update both locally and remotely.
  • A federated stored procedure with MODIFY SQL ACCESS.
Note: Do not use multiple paths to access the same data source within in a single transaction. Such a transaction can become deadlocked. That is, the transaction can hang. For example, do not use multiple federated servers to refer to the same data sources in the same transaction.

The following table lists what happens in a single distributed unit of work transaction including the type of connection, the type of commit, the federated server role in the transaction, what operations are allowed, and how transparent DDL can be used.

Table 1. What happens in a single distributed unit of work transaction
Type of commit Type of connection Federated server role Operations Transparent DDL
One-phase Type 1 or XA local transaction Sub-transaction manager. Also acts as the transaction coordinator, determines the transaction outcome, and delivers it to each participating resource manager. One-phase commit and two-phase commit read operations are permitted. One one-phase data source can be updated as long as it is the only update in the transaction. Allowed and managed according to one-phase commit data source rules. Each statement that is issued must be the only update in a one-phase commit transaction. Cannot coexist with other federated two-phase commit data source updates in the same transaction. It is strongly recommended that COMMIT or ROLLBACK statements be issued before and after transparent DDL transactions occur.
Two-phase Type 1 or XA local transaction Transaction manager. Also acts as the transaction coordinator, determines the transaction outcome, and delivers it to each participating resource manager. One-phase commit and two-phase commit read operations are permitted. Multiple two-phase data sources can be updated. Allowed and managed according to two-phase commit data source rules. Can coexist with other federated two-phase or one-phase commit data source updates in the same transaction.
One-phase Type 2 or XA global transaction Can be the transaction manager. If not the transaction manager, only relays the outcome from the external transaction coordinator to each participating resource manager. One-phase commit and two-phase commit read operations are permitted. One-phase updates are not allowed except for Db2® coordinated transactions which can perform federated one-phase updates over a Distributed Relational Database Architecture™ (DRDA) two-phase inbound connection. Allowed and managed according to one-phase commit data source rules. Each statement that is issued must be the only update in a one-phase commit transaction. Cannot coexist with other federated two-phase commit data source updates in the same transaction. It is strongly recommended that COMMIT or ROLLBACK statements be issued before and after transparent DDL transactions occur.
Two-phase Type 2 or XA global transaction Can be the transaction manager. If not the transaction manager, only relays the outcome from the external transaction coordinator to each participating resource manager. One-phase commit and two-phase commit read operations are permitted. Multiple two-phase data sources can be updated. Allowed and managed according to two-phase commit data source rules. Can coexist with other federated two-phase or one-phase commit data source updates in the same transaction.

How data consistency and atomicity are maintained

Federated servers attempt to ensure data consistency and maintain transaction atomicity of data sources.

Any conflict between an application synchronization point setting and the update capability of a target data source results in an error (SQL30090, reason code 18).

Local updates that include DDL made to the federated database cannot be mixed within the same transaction as an update to a federated one-phase data source. Transparent DDL

Using DDL and transparent DDL

Local updates that include DDL made to the federated database cannot be mixed within the same transaction as an update to a federated one-phase data source. Transparent DDL is an exception. For transparent DDL, both local updates and data source updates are allowed regardless of the type of connection and whether the data source is configured for one-phase or two-phase commit.

Transparent DDL creates a table on a remote data source and a nickname in the local federated database for the remote table. A federated server treats transparent DDL transactions as updates.

Transparent DDL provides the ability to create and modify remote tables through the Db2 database system, without the need to use pass-through sessions. The SQL statements for transparent DDL are CREATE TABLE, ALTER TABLE, and DROP TABLE. For example, a transparent DDL CREATE TABLE statement creates a remote table at the data source and a nickname for that table at the federated server. The statement contains a local update operation and a remote update operation.

Some data sources, such as Oracle, do not permit transparent DDL on a federated two-phase commit connection.