Oracle Distributed Systems

Distributed Queries and Transactions

The database link is the key to location transparency in Oracle; you can perform operations on objects in multiple databases unfettered with details about where objects reside, network protocols, database names, and so on. However, if you are a DBA or a developer, you can create more efficient and robust systems by understanding the mechanisms behinddistributed queries and transactions.

Table 3.3 lists the operations that Oracle supports in a distributed environment.

Table 3-3. Supported Distributed Operations

Supported DML	Supported Transaction Control
SELECT	COMMIT
SELECT FOR UPDATE	ROLLBACK
INSERT SAVEPOINT
UPDATE	ROLLBACK TO SAVEPOINT
DELETE
LOCK TABLE

Behind the Scenes of a Distributed Transaction

As with local transactions, consistency is a fundamental requirement of distributed transactions. A distributed transaction must either succeed at all participating nodes or fail at all participating nodes. The classic example is the transfer of funds from one institution to another, each with its own database. The transfer must debit the payer in one database and credit the payee in the other. These updates must either succeed in both databases or fail in both databases.

Oracle ensures this transactional consistency through a mechanism called the two-phase commit, so named because transaction commits occur in two stages, the prepare phase and the commit phase. I’ll examine the activities associated with these phases in the sections that follow.

Two-phase commit: ...