Sync Pull Notify example

solidDB Help : Replication : Advanced Replication : solidDB Advanced Replication architecture components : Sync Pull Notify : Sync Pull Notify example

This example demonstrates how to implement the Sync Pull Notify feature by using the START and CALL statements, and triggers.

Consider a scenario where there is a master database M1 and replica databases R1 and R2.

To carry out Sync Pull Notify, complete the following steps:

1 Define a procedure Pm1 in M1, and include the statements:

EXECDIRECT CALL Pr1 AT R1; EXECDIRECT CALL Pr1 AT R2;

You will have one call for each associated replica database. Even though the replica database name changes, typically the procedure name has the same in each replica database.

2 Define a procedure Pr1 in R1. If a master database is to invoke the Pr1 procedure in more than one replica, then Pr1 should be defined in every associated replica database.

3 Define a trigger for all relevant DML operations (insert, update, and delete).

4 In each trigger body, embed the following statement:

EXECDIRECT START [UNIQUE] CALL Pm1;

5 Grant EXECUTE authority to the appropriate user in each replica database. (A user, Ur1, in the replica database should already be mapped to a corresponding user, Um1, in the master database. When the user Um1 calls the procedure remotely, the call will actually execute with the privileges of the user Ur1 when the call is executed on the replica.)

In this example, a sales application has a table named CUSTOMER, which has a column named SALESMAN. The master database contains information for all salespersons. Each salesperson has their own replica database, and that replica database has the data for that salesperson. If the salesperson assigned to a particular customer changes, then the correct replica databases should be notified. If XYZ corporation is reassigned from salesperson Smith to salesperson Jones, then the Jones replica database should add the data related to XYZ corporation, and the Smith replica should delete the data related to XYZ corporation. The following code is used to update both replica databases:

-- If a customer is reassigned to a different salesman, then we
-- must notify both the old and new salesmen.
-- NOTE: This sample shows only the "UPDATE" trigger, but in
-- reality, you'd also need to define INSERT and DELETE triggers. CREATE TRIGGER T_CUST AFTERUPDATE ON CUSTOMER
AFTER UPDATE
REFERENCING NEW SALESMAN AS NEW_SALESMAN,
REFERENCING OLD SALESMAN AS OLD_SALESMAN BEGIN
IF NEW_SALESMAN <> OLD_SALESMAN THEN
  EXEC SQL EXECDIRECT
    START AFTER COMMIT
      FOR EACH REPLICA WHERE NAME=OLD_SALESMAN
      UNIQUE CALL CUST(OLD_SALESMAN);
  EXEC SQL EXECDIRECT
    START AFTER COMMIT
      FOR EACH REPLICA WHERE NAME=NEW_SALESMAN
      UNIQUE CALL CUST(NEW_SALESMAN);
  ENDIF
END;

Suppose that in the application, the user assigns all customers in sales area CA to salesperson Mike.

UPDATE CUSTOMER SET SALESMAN='Mike' WHERE SALES_AREA='CA';
COMMIT WORK;

The master database has the following procedure:

CREATE PROCEDURE CUST(salesman VARCHAR)
BEGIN
EXEC SQL EXECDIRECT CALL CUST(salesman) AT salesman;
COMMIT WORK;
END

Each replica database has the following procedure:

CREATE PROCEDURE CUST(salesman VARCHAR)
BEGIN
  MESSAGE s BEGIN;
  MESSAGE s APPEND REFRESH CUSTS(salesman);
  MESSAGE s END;
  COMMIT WORK;
  MESSAGE s FORWARD TIMEOUT FOREVER;
  COMMIT WORK;
END

In the procedure CUST(), the salesperson replica database is forced to refresh from the data in the master database. This procedure CUST() is defined on all the replica databases. If the procedure is called on both the replica database that the customer was reassigned to, and the replica database that the customer was reassigned from, then the procedure updates both those replica databases. Effectively, this removes the out-of-date data from the replica database that no longer has this customer, and inserts the data in the replica database that is now responsible for this customer. If the publication and its parameters are properly defined, additional detailed logic is not required to handle each possible operation, such as reassigning a customer from one salesperson to another; instead, each replica is instructed to refresh from the most current data.

Notes

▪ It is possible to implement a Sync Pull Notify without triggers. The application can call appropriate procedures to implement SyncPull. Triggers are a way to achieve Sync Pull Notify in conjunction with the statement START AFTER COMMIT and remote procedure calls.

▪ Sometimes, in the Sync Pull Notify process, it is possible that a replica database might have to exchange one extra round trip of messages unnecessarily. This could happen if the procedure that was invoked by the master database tries to send a message to the replica database that just sent the changes to the master database, and that causes a change in the "hot data" in the master database. However, this issue can be avoided with careful usage of the START AFTER COMMIT statement. Be careful not to create an infinite loop, where each update on the master database leads to an immediate update on the replica database, which leads to an immediate update on the master database, and so on. The best way to avoid this is to be careful when creating triggers on the replica that might immediately send updated data to the master database, which in turn immediately notifies the replica database to refresh again.

▪ It is possible that not all replicas have a procedure named CUST(). If this is the case, the procedure is executed only on those replicas that have the procedure.

▪ It is possible that not all replicas that do have a procedure named CUST() have the same procedure. Each replica might have its own custom version of the procedure.

Go up to

Sync Pull Notify