Network Topology   «Prev  Next»
Lesson 11 Connection with a database link
Objective Establish connections over database links.

Connection with Database Link


SELECT order_number, item_number
FROM
   order@raleigh o,
   Item@rochester i
WHERE
o.item_number = i.item_number;

Distributed update

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.

BEGIN 
  UPDATE item@rochester 
    SET quantity_on_hand = quantity_on_hand - 1 
    WHERE item_id = 12345;
 
  INSERT INTO order@raleigh 
    VALUES (‘xxx’,’xxx);
 
END;

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.

Insert into customer@raleigh values ();
Insert into order@albug values ();
commit;
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.
[1]Distributed query: A distributed query retrieves information from two or more nodes.