What Are Distributed Transactions? - Oracle 10g

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

Distributed System

Distributed System

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

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

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics