Distributed Transaction Processing: Case Study

Stage 1: Client Application Issues DML Statements

At the Sales department, a salesperson uses SQL*Plus to enter a sales order and then commit it. The application issues several SQL statements to enter the order into the sales database and update the inventory in the warehouse database:

CONNECT scott@sales.example.com ...;
INSERT INTO orders ...;
UPDATE inventory@warehouse.example.com ...;
INSERT INTO orders ...;
UPDATE inventory@warehouse.example.com ...;
COMMIT;

These SQL statements are part of a single distributed transaction, guaranteeing that all issued SQL statements succeed or fail as a unit. Treating the statements as a unit prevents the possibility of an order being placed and then inventory not being updated to reflect the order. In effect, the transaction guarantees the consistency of data in the global database.

As each of the SQL statements in the transaction executes, the session tree is defined, as shown in Figure 34-7.

Figure 34-7 Defining the Session Tree

Description of "Figure 34-7 Defining the Session Tree"

Note the following aspects of the transaction:

• An order entry application running on the sales database initiates the transaction. Therefore, sales.example.com is the global coordinator for the distributed transaction.

• The order entry application inserts a new sales record into the sales database and updates the inventory at the warehouse. Therefore, the nodes sales.example.com and warehouse.example.com are both database servers.

• Because sales.example.com updates the inventory, it is a client of warehouse.example.com.

This stage completes the definition of the session tree for this distributed transaction. Each node in the tree has acquired the necessary data locks to execute the SQL statements that reference local data. These locks remain even after the SQL statements have been executed until the two-phase commit is completed.

Stage 2: Oracle Database Determines Commit Point Site

The database determines the commit point site immediately following the COMMIT statement. sales.example.com, the global coordinator, is determined to be the commit point site, as shown in Figure 34-8.

Figure 34-8 Determining the Commit Point Site

Description of "Figure 34-8 Determining the Commit Point Site"

Stage 3: Global Coordinator Sends Prepare Response

The prepare stage involves the following steps:

1. After the database determines the commit point site, the global coordinator sends the prepare message to all directly referenced nodes of the session tree, excluding the commit point site. In this example, warehouse.example.com is the only node asked to prepare.

2. Node warehouse.example.com tries to prepare. If a node can guarantee that it can commit the locally dependent part of the transaction and can record the commit information in its local redo log, then the node can successfully prepare. In this example, only warehouse.example.com receives a prepare message because sales.example.com is the commit point site.

3. Node warehouse.example.com responds to sales.example.com with a prepared message.

As each node prepares, it sends a message back to the node that asked it to prepare. Depending on the responses, one of the following can happen:

• If any of the nodes asked to prepare responds with an abort message to the global coordinator, then the global coordinator tells all nodes to roll back the transaction, and the operation is completed.

• If all nodes asked to prepare respond with a prepared or a read-only message to the global coordinator, that is, they have successfully prepared, then the global coordinator asks the commit point site to commit the transaction.

Figure 34-9 Sending and Acknowledging the Prepare Message

Description of "Figure 34-9 Sending and Acknowledging the Prepare Message"

Stage 4: Commit Point Site Commits

The committing of the transaction by the commit point site involves the following steps:

1. Node sales.example.com, receiving acknowledgment that warehouse.example.com is prepared, instructs the commit point site to commit the transaction.

2. The commit point site now commits the transaction locally and records this fact in its local redo log.

Even if warehouse.example.com has not yet committed, the outcome of this transaction is predetermined. In other words, the transaction will be committed at all nodes even if the ability of a given node to commit is delayed.

Stage 5: Commit Point Site Informs Global Coordinator of Commit

This stage involves the following steps:

1. The commit point site tells the global coordinator that the transaction has committed. Because the commit point site and global coordinator are the same node in this example, no operation is required. The commit point site knows that the transaction is committed because it recorded this fact in its online log.

2. The global coordinator confirms that the transaction has been committed on all other nodes involved in the distributed transaction.

Stage 6: Global and Local Coordinators Tell All Nodes to Commit

The committing of the transaction by all the nodes in the transaction involves the following steps:

1. After the global coordinator has been informed of the commit at the commit point site, it tells all other directly referenced nodes to commit.

2. In turn, any local coordinators instruct their servers to commit, and so on.

3. Each node, including the global coordinator, commits the transaction and records appropriate redo log entries locally. As each node commits, the resource locks that were being held locally for that transaction are released.

In Figure 34-10, sales.example.com, which is both the commit point site and the global coordinator, has already committed the transaction locally. sales now instructs warehouse.example.com to commit the transaction.

Figure 34-10 Instructing Nodes to Commit

Description of "Figure 34-10 Instructing Nodes to Commit"

Stage 7: Global Coordinator and Commit Point Site Complete the Commit

The completion of the commit of the transaction occurs in the following steps:

1. After all referenced nodes and the global coordinator have committed the transaction, the global coordinator informs the commit point site of this fact.

2. The commit point site, which has been waiting for this message, erases the status information about this distributed transaction.

3. The commit point site informs the global coordinator that it is finished. In other words, the commit point site forgets about committing the distributed transaction. This action is permissible because all nodes involved in the two-phase commit have committed the transaction successfully, so they will never have to determine its status in the future.

4. The global coordinator finalizes the transaction by forgetting about the transaction itself.

After the completion of the COMMIT phase, the distributed transaction is itself complete. The steps described are accomplished automatically and in a fraction of a second.