Distributed Transaction Management and Update Processing
Distributed transaction management refers to an Oracle database that can manage an update, insert, or delete to multiple databases from
a single query. Most database vendors use the two-phase commit to implement this process.
The two-phase commit insures that all the remote databases have successfully completed their sub-updates before the entire transaction is committed to the database.
Oracle implements this feature with the Recoverer process (RECO). RECO manages two-phase commits and allows Oracle to perform truly distributed transactions.
A distributed system must guarantee the concurrency of distributed transactions. In other words, if a transaction is to update tables at two different sites, the transaction must either succeed both places or fail both places. This, of course, is what the two-phase commit protocol provides.
Distributed Transaction Management
Just as location transparency
implies support for distributed query processing, it also implies support for distributed transactions. A distributed transaction is one that applies DML at multiple locations. The classic example is the credit/debit transaction: when a bank wires funds from one branch to another, it must guarantee that the account receiving the funds is credited if and only if the account sending the funds is debited. The mechanism that guarantees this integrity is the two-phase commit protocol. Oracle introduced support of the two-phase commit with Version 7.0.
As with distributed query processing, distributed transactions will work without requiring any special coding or commands, but application developers and database administrators are well advised to make accommodations for the distributed work. Specifically, the application should include logic to trap errors relating to distributed transactions; these are errors in the range ORA-02040 to ORA-02099. Similarly, database administrators should ensure that the INIT.ORA configuration parameters are set appropriately for all participating databases.
These parameters include:
The DBA should also be prepared to monitor the data dictionary views DBA_2PC_NEIGHBORS and DBA_2PC_PENDING.
Introduction to Transactions
A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit.
The effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database). A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued. To illustrate the concept of a transaction, consider a banking database.
When a bank customer transfers money from a savings account to a checking account, the transaction can consist of three separate operations:
Oracle must allow for two situations.
- Decrement the savings account
- Increment the checking account
- Record the transaction in the transaction journal
- If all three SQL statements can be performed to maintain the accounts in proper balance, the effects of the transaction can be applied to the database.
- However, if a problem such as insufficient funds, invalid account number, or a hardware failure prevents one or two of the statements in the transaction from completing, the entire transaction must be rolled back so that the balance of all accounts is correct.