Two-phase commit

Understanding how a transaction is managed will help you to resolve the problem if an error occurs during the two-phase commit process.

Figure 1 illustrates the steps involved in a multisite update.

0

The application is prepared for two-phase commit. This can be accomplished through precompilation options. This can also be accomplished through Db2® CLI (Call Level Interface) configuration.

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.

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.