Administration Guide

Understanding the Two-Phase Commit Process

The following example illustrates the steps of the example transaction (described in "Updating Multiple Databases") and the participants in the transaction. If an error occurs during the two-phase commit process, understanding how a transaction is managed will help you to resolve the problem.

Figure 36. Updating Multiple Databases

(1)

When the database client wants to connect to SAVINGS_DB, it first connects to the Transaction Manager (TM) database. The TM database returns an acknowledgement to the database client.

(2)

The connection to the SAVINGS_DB 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 necessarily during connect time.

(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 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 Embedded SQL Programming Guide and 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 fashion.

(8)

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

(9)

Connecting to and using the CHECKING_DB follows the same rules as described by (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 their 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 to inform 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 client.

(13)

After the database client receives a positive response from all participating databases, it sends a request to the transaction manager database to inform it that the unit of work has been completed. The transaction manager database then:

• Writes a "COMMITTED" record to its log file, to indicate that the unit of work is complete
• Replies to the client to indicate it has finished.

[ DB2 List of Books | Search the DB2 Books ]