What Are Distributed Transactions?

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. For example, assume the database configuration depicted in Figure 32-1:

Figure 32-1 Distributed System

Description of Figure 32-1 follows
Description of "Figure 32-1 Distributed System"

The following distributed transaction executed by scott updates the local sales database, the remote hq database, and the remote maint database:

UPDATE scott.dept@hq.us.acme.com
  SET loc = 'REDWOOD SHORES'
  WHERE deptno = 10;
UPDATE scott.emp
  SET deptno = 11
  WHERE deptno = 10;
UPDATE scott.bldg@maint.us.acme.com
  SET room = 1225
  WHERE room = 1163;
COMMIT;

Note:

If all statements of a transaction reference only a single remote node, then the transaction is remote, not distributed.

There are two types of permissible operations in distributed transactions:

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: