Administration Guide

Understanding the Two-Phase Commit Process

Figure 42 illustrates the steps involved in a multisite update. Understanding how a transaction is managed will help you to resolve the problem if an error occurs during the two-phase commit process.

Figure 42. Updating Multiple Databases

(0)

The application is prepared for two-phase commit. This can be accomplished through precompilation options (refer to the Application Development Guide for details). This can also be accomplished through DB2 CLI (Call Level Interface) configuration (refer to the CLI Guide and Reference for details).

(1)

When the database client wants to connect to the SAVINGS_DB database, it first internally connects to the transaction manager (TM) database. The TM database returns an acknowledgment to the database client. If the database manager configuration parameter tm_database is set to 1ST_CONN, SAVINGS_DB becomes the transaction manager database for the duration of this application instance.

(2)

The connection to the SAVINGS_DB database takes place and is acknowledged.

(3)

The database client begins the update to the SAVINGS_ACCOUNT table. This begins the unit of work. The TM database responds to the database client, providing a transaction ID for the unit of work. Note that the registration of a unit of work occurs when the first SQL statement in the unit of work is run, not during the establishment of a connection.

(4)

After receiving the transaction ID, the database client registers the unit of work with the database containing the SAVINGS_ACCOUNT table. A response is sent back to the client to indicate that the unit of work has been registered successfully.

(5)

SQL statements issued against the SAVINGS_DB database are handled in the normal manner. The response to each statement is returned in the SQLCA when working with SQL statements embedded in a program. (The SQLCA is described in the Application Development Guide and in the SQL Reference.)

(6)

The transaction ID is registered at the FEE_DB database containing the TRANSACTION_FEE table, during the first access to that database within the unit of work.

(7)

Any SQL statements against the FEE_DB database are handled in the normal way.

(8)

Additional SQL statements can be run against the SAVINGS_DB database by setting the connection, as appropriate. Since the unit of work has already been registered with the SAVINGS_DB database (4), the database client does not need to perform the registration step again.

(9)

Connecting to, and using the CHECKING_DB database follows the same rules described in (6) and (7).

(10)

When the database client requests that the unit of work be committed, a prepare message is sent to all databases participating in the unit of work. Each database writes a "PREPARED" record to its log files, and replies to the database client.

(11)

After the database client receives a positive response from all of the databases, it sends a message to the transaction manager database, informing it that the unit of work is now ready to be committed (PREPARED). The transaction manager database writes a "PREPARED" record to its log file, and sends a reply to inform the client that the second phase of the commit process can be started.

(12)

During the second phase of the commit process, the database client sends a message to all participating databases to tell them to commit. Each database writes a "COMMITTED" record to its log file, and releases the locks that were held for this unit of work. When the database has completed committing the changes, it sends a reply to the client.

(13)

After the database client receives a positive response from all participating databases, it sends a message to the transaction manager database, informing it that the unit of work has been completed. The transaction manager database then writes a "COMMITTED" record to its log file, indicating that the unit of work is complete, and replies to the client, indicating that it has finished.