SELECT order_number, item_number
o.item_number = i.item_number;
A distributed update modifies data on two or more nodes. A distributed update is possible using a PL/SQL subprogram unit, such as a procedure or trigger, that includes two or more remote updates accessing data on different nodes. In the example below, I have decremented the inventory at the Rochester plant before adding the order to the database at Raleigh.
SET quantity_on_hand = quantity_on_hand - 1
WHERE item_id = 12345;
INSERT INTO order@raleigh
The two-phase commit
This ensures that the inventory adjustment and the order entry happen as a single transaction. The RECO Oracle process on the remote nodes is used to ensure that both nodes complete their piece of the transaction before the whole distributed transaction is committed to each database. This is known as a two-phase commit.
As the name implies, the two-phase commit works in two distinct steps.
The first step is the prepare phase, where the rows are prepared to be committed to the remote databases.
The second phase, called the commit phase, is where the entire distributed transaction is either committed or rolled back as a single unit of work. The following figure illustrates the process.
Remember, location transparency is easily achieved by hiding the service name with a public synonym. For example, if you do not want your
programmers to be concerned that your inventory database is in Rochester, you could create the following synonym:
CREATE PUBLIC SYNONYM item for item@rochester
Now all SQL references to the ITEM table will transparently reference item@rochester. The next lesson concludes this module.
Distributed query: A distributed query retrieves information from two or more nodes.