|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
In this scenario, a company has separate Oracle Database servers,
warehouse.acme.com. As users insert sales records into the
sales database, associated records are being updated at the
The definition of a session tree
How a commit point site is determined
When prepare messages are sent
When a transaction actually commits
What information is stored locally about the transaction
At the Sales department, a salesperson uses SQL*Plus to enter a sales order and then commit it. The application issues a number of SQL statements to enter the order into the
sales database and update the inventory in the
CONNECT email@example.com ...; INSERT INTO orders ...; UPDATE firstname.lastname@example.org ...; INSERT INTO orders ...; UPDATE email@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
Note the following aspects of the transaction:
An order entry application running on the
sales database initiates the transaction. Therefore,
sales.acme.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
warehouse.acme.com are both database servers.
sales.acme.com updates the inventory, it is a client of
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.
The database determines the commit point site immediately following the
sales.acme.com, the global coordinator, is determined to be the commit point site, as shown in Figure 34-8.
See Also:"Commit Point Strength" for more information about how the commit point site is determined
Figure 34-8 Determining the Commit Point Site
The prepare stage involves the following steps:
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.acme.com is the only node asked to prepare.
warehouse.acme.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.acme.com receives a prepare message because
sales.acme.com is the commit point site.
warehouse.acme.com responds to
sales.acme.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
The committing of the transaction by the commit point site involves the following steps:
sales.acme.com, receiving acknowledgment that
warehouse.acme.com is prepared, instructs the commit point site to commit the transaction.
The commit point site now commits the transaction locally and records this fact in its local redo log.
warehouse.acme.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.
This stage involves the following steps:
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.
The global coordinator confirms that the transaction has been committed on all other nodes involved in the distributed transaction.
The committing of the transaction by all the nodes in the transaction involves the following steps:
After the global coordinator has been informed of the commit at the commit point site, it tells all other directly referenced nodes to commit.
In turn, any local coordinators instruct their servers to commit, and so on.
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.acme.com, which is both the commit point site and the global coordinator, has already committed the transaction locally.
sales now instructs
warehouse.acme.com to commit the transaction.
Figure 34-10 Instructing Nodes to Commit
The completion of the commit of the transaction occurs in the following steps:
After all referenced nodes and the global coordinator have committed the transaction, the global coordinator informs the commit point site of this fact.
The commit point site, which has been waiting for this message, erases the status information about this distributed transaction.
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.
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.