Oracle7 Server Distributed Systems Volume II: Replicated Data

Synchronously Propagating DML Changes Among Master Sites

As shown in Figure 4 - 1, whenever you make a Data Manipulation Language (DML) change to a local table replicated using synchronous row-level replication, this change is synchronously propagated to the other master sites in the replicated environment using generated triggers and their associated packages. When you apply your local change, these triggers issue calls to generated procedures at the remote master sites.

Oracle ensures that all distributed transactions either commit or rollback in the event of a failure. See the discussion of distributed updates in Oracle7 Server Distributed Systems, Volume I for more information.

Restrictions

LONG and LONG RAW columns cannot be not replicated using row-level replication and, if present, are skipped prior to logging in the deferred RPC queue.

Figure 4 - 1. Propagating Changes using Synchronous Row-Level Replication

Warning: Because of the locking mechanism used by synchronous replication, deadlocks can occur. When you perform a synchronously replicated update, Oracle first locks the local row and then uses an AFTER ROW trigger to lock the remote row. These locks are released when the transaction commits at each site.

Destination of Synchronously Replicated Transactions

The necessary remote procedure calls to support synchronous replication are included in the generated trigger and its associated package for each object. When you call GENERATE_REPLICATION_SUPPORT, Oracle regenerates these triggers at all master sites to add the necessary remote procedure calls for the new site. Conversely, if you remove a master site, Oracle removes these calls from the generated trigger.

Privilege Domain at the Remote Site

The privilege domain in which the transaction is executed at the remote site is determined by the database link that is used to connect to the remote site. Because the remote procedure is called from within a local generated procedure, Oracle looks for a private database link for the owner of this generated procedure. The owner of a generated procedure can also be specified by using the parameter GEN_TRIG_OWNER of the GENERATE_REP_SUPPORT procedure to place triggers and packages in any valid schema.

Oracle uses the username and password supplied with this link to connect to the remote database. The privilege domain of this user at the remote site determines whether the remote generated procedure can be successfully executed to apply the transaction at the remote site. If no username and password is supplied with the link, Oracle uses the username and password associated with the local connected session; that is, the username and password for the user performing the local update.

If Oracle cannot find a link for the owner of the table, it looks for a public link. If Oracle cannot find a private database link, it will use a public database link, if one is available. Once again, if no username and password is supplied with the link, Oracle uses the username and password for the local connected session.

Suggestion: Including a username and password as part of the database link greatly simplifies the number of privileges that you need to grant to each user at each site. For example, if all of the objects in your replicated object group have the same owner, by creating a private database link for this user (with the username and password), you could simply grant this user EXECUTE ANY PROCEDURE at each site.

Conflict Detection

If all of your sites are communicating synchronously with one another you should never experience an update conflict. However, if even one site is sending changes asynchronously to another site, you may experience conflicts at any site in your replicated environment.

If the change is being propagated synchronously, an error is raised and a rollback will be required. If the change is propagated asynchronously, Oracle automatically detects these conflicts and either logs the conflict or, if you designate an appropriate resolution method, resolves the conflict. Conflict detection and conflict resolution are described in Chapter 6.