About
A distributed database is a set of databases in a distributed system that can appear to applications as a single data source.
A distributed transaction is a transaction that includes one or more statements that update data on two or more distinct nodes of a distributed database, using a schema object called a database link. A database link describes how one database instance can log in to another database instance.
Unlike a transaction on a local database, a distributed transaction alters data on multiple databases.
Consequently, distributed transaction processing is more complicated because the database must coordinate the committing or rolling back of the changes in a transaction as an atomic unit. The entire transaction must commit or roll back. Oracle Database must coordinate transaction control over a network and maintain data consistency, even if a network or system failure occurs.
Articles Related
Two-Phase Commit
The two-phase commit mechanism guarantees that all databases participating in a distributed transaction either all commit or all undo the statements in the transaction.
The mechanism also protects implicit DML performed by:
- integrity constraints,
- remote procedure calls,
- and triggers.
In a two-phase commit among multiple databases:
- one database coordinates the distributed transaction.
- the initiating node is called the global coordinator.
- the coordinator asks the other databases if they are prepared to commit.
- if any database responds with a no, then the entire transaction is rolled back.
- if all databases vote yes, then the coordinator broadcasts a message to make the commit permanent on each of the databases.
The two-phase commit mechanism is transparent to users who issue distributed transactions. In fact, users need not even know the transaction is distributed. A COMMIT statement denoting the end of a transaction automatically triggers the two-phase commit mechanism. No coding or complex statement syntax is required to include distributed transactions within the body of a database application.
In-Doubt Transactions
An in-doubt distributed transaction occurs when a two-phase commit was interrupted by any type of system or network failure. For example, two databases report to the coordinating database that they were prepared to commit, but the coordinating database instance fails immediately after receiving the messages. The two databases who are prepared to commit are now left hanging while they await notification out of the outcome.
The recoverer (RECO) background process automatically resolves the outcome of in-doubt distributed transactions. After the failure is repaired and communication is reestablished, the RECO process of each local Oracle database automatically commits or rolls back any in-doubt distributed transactions consistently on all involved nodes.
In the event of a long-term failure, Oracle Database enables each local administrator to manually commit or undo any distributed transactions that are in doubt because of the failure. This option enables the local database administrator to free any locked resources that are held indefinitely because of the long-term failure.
If a database must be recovered to a past time, then database recovery facilities enable database administrators at other sites to return their databases to the earlier point in time. This operation ensures that the global database remains consistent.