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 33-1:

Figure 33-1 Distributed System

Description of "Figure 33-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
Transaction Control Statements

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 Language Reference for more information about these SQL statements