Oracle7 Server Distributed Systems Volume II: Replicated Data
Propagating DML Changes
This section describes how updates made to a replicated snapshot are propagated to its associated master table, and how updates to the master table are, in turn, propagated to the snapshots. While master table changes are always propagated to the snapshot site asynchronously, in the form of a refresh, snapshot site changes can be propagated either synchronously or asynchronously. The PROPAGATION_MODE parameter of the CREATE_SNAPSHOT_REPGROUP and ALTER_SNAPSHOT_PROPAGATION commands determines how changes from the snapshot site are propagated to the master site.
How Changes are Propagated
The method that you choose to refresh and/or propagate your snapshot updates will be determined by the frequency of changes that you make to the data at the snapshot and master sites.
For example, you might want communication from the snapshot to the master to seem event-driven. By frequently propagating changes to the master site (such as every 10 seconds), you can ensure that shortly after each modification to an updatable snapshot, the change is forwarded to its associated master table. Yet, you might only refresh the snapshot once, at the start of each day, or you may never refresh the snapshot, if updates are performed only at the snapshot site.
Asynchronously Replicating Snapshot Updates to the Master Site
As shown in Figure 5 - 1, whenever you apply a change to a replicated updatable snapshot that is asynchronously propagating changes to its associated master site, the following events occur:
Figure 5 - 1. Applying Changes to an Updatable Snapshot
- The change is applied to the snapshot base table. This change is also reflected in the user view of the snapshot.
- The ROWID of any rows updated or deleted is recorded in the snapshot log. This information is used during a snapshot refresh to determine what changes to apply to the snapshot.
- A call is added to the deferred transaction queue. These calls are pushed to the associated master table at whatever interval you specify.
When Changes are Propagated
Updates are asynchronously propagated from the snapshot site to its master site whenever one of the following actions occurs:
- The updatable snapshot is refreshed by calling one of the following procedures, with the PUSH_DEFERRED_RPC argument set to TRUE:
Each of these methods of snapshot refresh is described, starting ![[*]](jump.gif)
. You may also want to set the REFRESH_AFTER_ERRORS argument to TRUE if you want the refresh to continue even if there are errors logged in the DefError table at the master definition site.
- The deferred transaction queue at the snapshot site is pushed by calling either DBMS_DEFER_SYS.EXECUTE or DBMS_DEFER_SYS.SCHEDULE_EXECUTION. The deferred transaction queue at a snapshot site is pushed in the same manner as the queue at an asynchronous master site is pushed; see for details. For snapshot sites, you should set the BATCH_SIZE parameter to 0.
Conflict Detection
When you asynchronously push changes from a snapshot to its master, Oracle compares the old values for the row at the snapshot site (that is, the values before any changes were applied) to the current values for the row at the master site. If the values are different, a conflict has occurred. If any conflicts are detected at the master site, Oracle invokes the appropriate conflict resolution routine, if any was specified. For example, you might create a routine to resolve a conflict between two rows by selecting the row with the most recent timestamp, or by combining the column values of the conflicting rows.
Note that if conflict resolution routines are employed, the values of some of the rows in your snapshot may change or even be removed after a refresh is performed. If you did not specify a conflict resolution routine at the master site, or if the routine specified is unable to resolve the conflict, the conflict is logged and must be resolved manually at the master site.
Additional Information: For additional information on conflict detection and resolution, refer to Chapter 6.
How Snapshot Changes are Applied to the Master Table
The DML changes propagated to the master table are applied in the same manner as changes are replicated from one master site to another master site. As shown in Figure 5 - 2, the deferred call is pushed from the snapshot site. The package at the master site applies the change to the master table. If this change results in a conflict, it must either be resolved by the appropriate conflict resolution routine, or logged in an error table.
Figure 5 - 2. Applying Changes to a Master Table 3
Two additional steps occur when changes are propagated from an updatable snapshot to the master table:
- The ROWID of any rows inserted, updated, or deleted is logged in the master snapshot log. This information is used during a fast snapshot refresh to determine what changes to apply to the snapshot.
- A call is added to the deferred transaction queue at the master site. These calls are pushed to the other master tables in the replicated environment. It is this final step that ultimately allows changes from one snapshot site to propagate to all other sites in the replicated environment.
Because changes from the master site are applied at the snapshot site using the refresh mechanism, there is no need for conflict detection or resolution at the snapshot site. All conflict detection and resolution occurs at the master site.
Refreshing a Snapshot
As shown in Figure 5 - 3, when you refresh an updatable snapshot the following events occur:
Figure 5 - 3. Snapshot Refresh
1. Any changes that you made to the snapshot site will have been added to the deferred transaction queue associated with the snapshot.
2. Any transactions queued for the snapshot are pushed to its associated master table (assuming PUSH_DEFERRED_RPC is TRUE), where they are applied, using the appropriate replication mechanism. If you used synchronous propagation for the snapshot, there should be not deferred transactions to propagate.
3. Oracle next determines which rows from the master table need to be updated at the snapshot. Conceptually, Oracle uses the view of the master table to create a list of the rows in the master snapshot log and the updatable snapshot log that are relevant for the updatable snapshot.
Although you might think that all changes in the snapshot log would be recorded in the master log when the changes from the snapshot were pushed to the master, it is possible that a change to the snapshot would not be applied to the master. For example, if a conflict was detected when the change was pushed to the master, application of a conflict resolution routine might have resulted in a change not being applied to the master table.
4. These changes are applied to the snapshot by copying the values of the changed rows in the master table to the snapshot base table.
Propagating Changes Between Snapshot Sites
As shown in Figure 5 - 4, snapshot sites never communicate with one another directly. Instead, they must communicate through their associated master sites. In order for a snapshot to see a change made to a snapshot at another snapshot site, the following series of events illustrates what must occur:
Figure 5 - 4. How Changes Propagate Between Snapshot Sites
1. The change is first applied to snapshot EMP at snapshot site A.
2. This change is either synchronously (using remote procedure calls) or asynchronously (by pushing the deferred transaction queue, for example as the result of a refresh) applied to the master table of snapshot EMP at site A. For changes propagated asynchronously, any conflicts between the snapshot and master table are detected and possibly resolved during this refresh.
3. Later when master site A communicates with master site B, the EMP table at site B is updated with this change. Any conflicts between the EMP table at site A and the EMP table at site B are detected and possibly resolved at this time.
4. Finally, the EMP snapshot at site B is updated with this change when it is refreshed.
Synchronously Replicating Snapshot Updates to the Master Site
If you choose to synchronously propagate your snapshot site changes to the associated master site, Oracle performs the following actions each time you modify an updatable snapshot:
1. Oracle obtains a lock on the local row and performs the update.
2. The AFTER ROW generated trigger for the snapshot fires, and its associated package makes the necessary remote procedure call to the generated package at the master site to apply the change.
3. The generated procedure at the master site locks the row at the master site and performs the update.
4. If the master detects a conflict that it cannot resolve, an error is raised immediately.
5. Using two-phase commit, Oracle commits or rolls-back (if an error occurred), the transaction at the snapshot and master sites and releases the locks.
The snapshot logs at both the updatable snapshot site and the master site, as well as the view and base table at the snapshot site, are updated in the same manner as if you had propagated the changes asynchronously.