32 Distributed Transactions Concepts

DML and DDL Transactions

The following are the DML and DDL operations supported in a distributed transaction:

• CREATE TABLE AS SELECT

• DELETE

• INSERT (default and direct load)

• LOCK TABLE

• SELECT

• SELECT FOR UPDATE

You can execute DML and DDL statements in parallel, and INSERT direct load statements serially, but note the following restrictions:

• All remote operations must be SELECT statements.

• These statements must not be clauses in another distributed transaction.

• If the table referenced in the table_expression_clause of an INSERT, UPDATE, or DELETE statement is remote, then execution is serial rather than parallel.

• You cannot perform remote operations after issuing parallel DML/DDL or direct load INSERT.

• If the transaction begins using XA or OCI, it executes serially.

• No loopback operations can be performed on the transaction originating the parallel operation. For example, you cannot reference a remote object that is actually a synonym for a local object.

• If you perform a distributed operation other than a SELECT in the transaction, no DML is parallelized.

Transaction Control Statements

The following are the supported transaction control statements:

• COMMIT

• ROLLBACK

• SAVEPOINT

  See Also:

  Oracle Database SQL Reference for more information about these SQL statements

Clients

A node acts as a client when it references information from a database on another node. The referenced node is a database server. In Figure 32-2, the node sales is a client of the nodes that host the warehouse and finance databases.

Database Servers

A database server is a node that hosts a database from which a client requests data.

In Figure 32-2, an application at the sales node initiates a distributed transaction that accesses data from the warehouse and finance nodes. Therefore, sales.acme.com has the role of client node, and warehouse and finance are both database servers. In this example, sales is a database server and a client because the application also modifies data in the sales database.

Local Coordinators

A node that must reference data on other nodes to complete its part in the distributed transaction is called a local coordinator. In Figure 32-2, sales is a local coordinator because it coordinates the nodes it directly references: warehouse and finance. The node sales also happens to be the global coordinator because it coordinates all the nodes involved in the transaction.

A local coordinator is responsible for coordinating the transaction among the nodes it communicates directly with by:

• Receiving and relaying transaction status information to and from those nodes

• Passing queries to those nodes

• Receiving queries from those nodes and passing them on to other nodes

• Returning the results of queries to the nodes that initiated them

Global Coordinator

The node where the distributed transaction originates is called the global coordinator. The database application issuing the distributed transaction is directly connected to the node acting as the global coordinator. For example, in Figure 32-2, the transaction issued at the node sales references information from the database servers warehouse and finance. Therefore, sales.acme.com is the global coordinator of this distributed transaction.

The global coordinator becomes the parent or root of the session tree. The global coordinator performs the following operations during a distributed transaction:

• Sends all of the distributed transaction SQL statements, remote procedure calls, and so forth to the directly referenced nodes, thus forming the session tree

• Instructs all directly referenced nodes other than the commit point site to prepare the transaction

• Instructs the commit point site to initiate the global commit of the transaction if all nodes prepare successfully

• Instructs all nodes to initiate a global rollback of the transaction if there is an abort response

Commit Point Site

The job of the commit point site is to initiate a commit or roll back operation as instructed by the global coordinator. The system administrator always designates one node to be the commit point site in the session tree by assigning all nodes a commit point strength. The node selected as commit point site should be the node that stores the most critical data.

Figure 32-3 illustrates an example of distributed system, with sales serving as the commit point site:

Figure 32-3 Commit Point Site

Description of "Figure 32-3 Commit Point Site"

The commit point site is distinct from all other nodes involved in a distributed transaction in these ways:

• The commit point site never enters the prepared state. Consequently, if the commit point site stores the most critical data, this data never remains in-doubt, even if a failure occurs. In failure situations, failed nodes remain in a prepared state, holding necessary locks on data until in-doubt transactions are resolved.

• The commit point site commits before the other nodes involved in the transaction. In effect, the outcome of a distributed transaction at the commit point site determines whether the transaction at all nodes is committed or rolled back: the other nodes follow the lead of the commit point site. The global coordinator ensures that all nodes complete the transaction in the same manner as the commit point site.

Commit Point Strength

Every database server must be assigned a commit point strength. If a database server is referenced in a distributed transaction, the value of its commit point strength determines which role it plays in the two-phase commit. Specifically, the commit point strength determines whether a given node is the commit point site in the distributed transaction and thus commits before all of the other nodes. This value is specified using the initialization parameter COMMIT_POINT_STRENGTH. This section explains how the database determines the commit point site.

The commit point site, which is determined at the beginning of the prepare phase, is selected only from the nodes participating in the transaction. The following sequence of events occurs:

1. Of the nodes directly referenced by the global coordinator, the database selects the node with the highest commit point strength as the commit point site.

2. The initially-selected node determines if any of the nodes from which it has to obtain information for this transaction has a higher commit point strength.

3. Either the node with the highest commit point strength directly referenced in the transaction or one of its servers with a higher commit point strength becomes the commit point site.

4. After the final commit point site has been determined, the global coordinator sends prepare responses to all nodes participating in the transaction.

Figure 32-4 shows in a sample session tree the commit point strengths of each node (in parentheses) and shows the node chosen as the commit point site:

Figure 32-4 Commit Point Strengths and Determination of the Commit Point Site

Description of "Figure 32-4 Commit Point Strengths and Determination of the Commit Point Site"

The following conditions apply when determining the commit point site:

• A read-only node cannot be the commit point site.

• If multiple nodes directly referenced by the global coordinator have the same commit point strength, then the database designates one of these as the commit point site.

• If a distributed transaction ends with a rollback, then the prepare and commit phases are not needed. Consequently, the database never determines a commit point site. Instead, the global coordinator sends a ROLLBACK statement to all nodes and ends the processing of the distributed transaction.

As Figure 32-4 illustrates, the commit point site and the global coordinator can be different nodes of the session tree. The commit point strength of each node is communicated to the coordinators when the initial connections are made. The coordinators retain the commit point strengths of each node they are in direct communication with so that commit point sites can be efficiently selected during two-phase commits. Therefore, it is not necessary for the commit point strength to be exchanged between a coordinator and a node each time a commit occurs.

See Also:

• "Specifying the Commit Point Strength of a Node" to learn how to set the commit point strength of a node

• Oracle Database Reference for more information about the initialization parameter COMMIT_POINT_STRENGTH

Prepare Phase

The first phase in committing a distributed transaction is the prepare phase. In this phase, the database does not actually commit or roll back the transaction. Instead, all nodes referenced in a distributed transaction (except the commit point site, described in the "Commit Point Site") are told to prepare to commit. By preparing, a node:

• Records information in the redo logs so that it can subsequently either commit or roll back the transaction, regardless of intervening failures

• Places a distributed lock on modified tables, which prevents reads

When a node responds to the global coordinator that it is prepared to commit, the prepared node promises to either commit or roll back the transaction later, but does not make a unilateral decision on whether to commit or roll back the transaction. The promise means that if an instance failure occurs at this point, the node can use the redo records in the online log to recover the database back to the prepare phase.

Commit Phase

The second phase in committing a distributed transaction is the commit phase. Before this phase occurs, all nodes other than the commit point site referenced in the distributed transaction have guaranteed that they are prepared, that is, they have the necessary resources to commit the transaction.

Forget Phase

After the participating nodes notify the commit point site that they have committed, the commit point site can forget about the transaction. The following steps occur:

1. After receiving notice from the global coordinator that all nodes have committed, the commit point site erases status information about this transaction.

2. The commit point site informs the global coordinator that it has erased the status information.

3. The global coordinator erases its own information about the transaction.

Automatic Resolution of In-Doubt Transactions

In the majority of cases, the database resolves the in-doubt transaction automatically. Assume that there are two nodes, local and remote, in the following scenarios. The local node is the commit point site. User scott connects to local and executes and commits a distributed transaction that updates local and remote.

Failure During the Prepare Phase

Figure 32-5 illustrates the sequence of events when there is a failure during the prepare phase of a distributed transaction:

Figure 32-5 Failure During Prepare Phase

Description of "Figure 32-5 Failure During Prepare Phase"

The following steps occur:

1. User SCOTT connects to Local and executes a distributed transaction.

2. The global coordinator, which in this example is also the commit point site, requests all databases other than the commit point site to promise to commit or roll back when told to do so.

3. The remote database crashes before issuing the prepare response back to local.

4. The transaction is ultimately rolled back on each database by the RECO process when the remote site is restored.

Failure During the Commit Phase

Figure 32-6 illustrates the sequence of events when there is a failure during the commit phase of a distributed transaction:

Figure 32-6 Failure During Commit Phase

Description of "Figure 32-6 Failure During Commit Phase"

The following steps occur:

1. User Scott connects to local and executes a distributed transaction.

2. The global coordinator, which in this case is also the commit point site, requests all databases other than the commit point site to promise to commit or roll back when told to do so.

3. The commit point site receives a prepared message from remote saying that it will commit.

4. The commit point site commits the transaction locally, then sends a commit message to remote asking it to commit.

5. The remote database receives the commit message, but cannot respond because of a network failure.

6. The transaction is ultimately committed on the remote database by the RECO process after the network is restored.

   See Also:

   "Deciding How to Handle In-Doubt Transactions" for a description of failure situations and how the database resolves intervening failures during two-phase commit

Manual Resolution of In-Doubt Transactions

You should only need to resolve an in-doubt transaction in the following cases:

• The in-doubt transaction has locks on critical data or undo segments.

• The cause of the machine, network, or software failure cannot be repaired quickly.

Resolution of in-doubt transactions can be complicated. The procedure requires that you do the following:

• Identify the transaction identification number for the in-doubt transaction.

• Query the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views to determine whether the databases involved in the transaction have committed.

• If necessary, force a commit using the COMMIT FORCE statement or a rollback using the ROLLBACK FORCE statement.

  See Also:

  The following sections explain how to resolve in-doubt transactions:

  • "Deciding How to Handle In-Doubt Transactions"

  • "Manually Overriding In-Doubt Transactions"

Relevance of System Change Numbers for In-Doubt Transactions

A system change number (SCN) is an internal timestamp for a committed version of the database. The Oracle Database server uses the SCN clock value to guarantee transaction consistency. For example, when a user commits a transaction, the database records an SCN for this commit in the redo log.

The database uses SCNs to coordinate distributed transactions among different databases. For example, the database uses SCNs in the following way:

1. An application establishes a connection using a database link.

2. The distributed transaction commits with the highest global SCN among all the databases involved.

3. The commit global SCN is sent to all databases involved in the transaction.

SCNs are important for distributed transactions because they function as a synchronized commit timestamp of a transaction, even if the transaction fails. If a transaction becomes in-doubt, an administrator can use this SCN to coordinate changes made to the global database. The global SCN for the transaction commit can also be used to identify the transaction later, for example, in distributed recovery.

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 a number of SQL statements to enter the order into the sales database and update the inventory in the warehouse database:

CONNECT scott/tiger@sales.acme.com ...;
INSERT INTO orders ...;
UPDATE inventory@warehouse.acme.com ...;
INSERT INTO orders ...;
UPDATE inventory@warehouse.acme.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 32-7.

Figure 32-7 Defining the Session Tree

Description of "Figure 32-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 sales.acme.com and warehouse.acme.com are both database servers.

• Because sales.acme.com updates the inventory, it is a client of warehouse.acme.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.acme.com, the global coordinator, is determined to be the commit point site, as shown in Figure 32-8.

Figure 32-8 Determining the Commit Point Site

Description of "Figure 32-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.acme.com is the only node asked to prepare.

2. Node 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.

3. Node 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 32-9 Sending and Acknowledging the Prepare Message

Description of "Figure 32-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.acme.com, receiving acknowledgment that warehouse.acme.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.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.

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 32-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 32-10 Instructing Nodes to Commit

Description of "Figure 32-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.