# 11. Data Replication, Synchronization and Transformation Services

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Data Replication, Synchronization and Transformation Services
Introduction
Snapshot Replication
Transactional Replication
Publishable Items Errors in Replication Publisher Transactional Replication Functions Subscriber Functions Common Status Functions Bi-Directional Transactional Replication Purging replication logs Objects created by transactional replication
Virtuoso scheduler
Transactional Replication Example
Replication Logger Sample

## 11.3. Transactional Replication

The unit of replication is a publication. A publication is an ordered sequence of transaction entries. One database transaction can add data to zero or more publications. The data contributed to a publication by a transaction is appended to the publication at the time of commit. Because commits are serialized database wide, items in a publication have a well defined order.

Each transaction entry in a publication has a unique sequence number within the publication. Each subscriber of a publication has a level of synchronization, which is the serial number of the last transaction from the publication which this subscriber has processed.

Each publication has exactly one publisher and zero or more subscribers. Any multi-master merge replication schemes will be based on this notion, with data to be merged back into the original source regarded as a separate publication and the merge regarded as a process between publications.

In order to publish data for replication by others a server must have a unique name within the group of servers participating in the replication. This server name is assigned to the server in its virtuoso.ini file in the DBName setting.

To publish data the publishing server initializes a publication with the repl_publish function, where it names the publication and assigns a log file name for it. The server can then start adding transactions to the publication, which can happen either under application control or implicitly.

Tip

See the repl_text function.

To subscribe to publications a server must also have a distinct DBName. It identifies the publishing server by associating a host name and port number to its logical name with the repl_server function. It can then call repl_subscribe(). Replication feeds from publisher are replayed by 'dba' user by default. The default can be changed (see repl_subscribe() function). for each of the publications it subscribes to. A publication is uniquely identified on the subscriber with the publishing server name and the publication name. Note that several servers in a network may publish like named publications and these will be logically distinct, having each their own distinct publisher.

A subscriber may or may not be connected to the publisher at any point in time. If a subscriber is connected to the publisher it may either be 'in sync' or syncing'. In the syncing state it is receiving transaction entries with numbers consecutive from its sync level up until the last committed serial number committed on the server.

At the start of the sync communication the subscriber indicates the level of the last successfully processed transaction in the publication. The sync exchange terminates when the subscriber reaches the last committed item on the publication. At this point the subscriber is said to be 'in sync'. The connection to the publisher is then maintained by default and is used to send sync information as it becomes available. This means that once an entry is appended to the publication by a committing a transaction it is sent to the 'in sync' subscribers without separate request.

The publisher can terminate the replication feed by unilateral decision. It will do it if the sending of the message times out for too long or if the queue of 'to be sent' replication records exceeds a settable threshold. This essentially happens with communication failures or if the subscriber continuously processes the feed at a speed lower than the feed production speed of the publisher. A disconnected subscriber can reconnect at will, in which case it enters the 'syncing' state and will receive transactions from the point where the feed was cut.

A subscriber can disconnect from the publisher at any time without ill effect.

A table

    SYS_REPL_ACCOUNTS (
        SERVER varchar,
        ACCOUNT varchar,

        NTH integer,
        LEVEL integer,
        IS_MANDATORY integer,
        IS_UPDATEABLE integer,
        SYNC_USER varchar,

        P_MONTH integer,
        P_DAY integer,
        P_WDAY integer,
        P_TIME time,

        primary key (SERVER, ACCOUNT))

is used to store information about published accounts and accounts this server is subscribed to.

A table

    SYS_REPL_SUBSCRIBERS (
        RS_SERVER varchar,
        RS_ACCOUNT varchar,
        RS_SUBSCRIBER varchar not null,

        RS_LEVEL integer NOT NULL,
        RS_VALID integer NOT NULL,

        primary key (RS_SERVER, RS_ACCOUNT, RS_SUBSCRIBER))

is used to store subscribers' status (pushback accounts for updateable subscriptions are there too). Subscribers for an account are added to this table automatically on each request to sync an account from subscriber or manually from Admin UI.

SYS_REPL_SUBSCRIBERS.RS_VALID column is be used to designate subscribers whose replication account level is valid (lags not more than REPL_MAX_DELTA behind the publisher's level).

RS_VALID state of subscriber is checked and updated on every sync request from subscriber. If subscriber is found to be invalid all further sync requests from it are ignored. Such subscriber need to be reinitialized manually and marked as valid using Admin UI.

### 11.3.1. Publishable Items

Tables, stored procedures and DAV collections may be added to a transactional publication. When a table is added, triggers are created for capturing changes to the table. When a procedure is added, all calls to this procedure will be recorded in the publication's log and the same procedure will be called on the subscriber. When a procedure is published in this manner, actions performed inside the procedure are not themselves recorded even if they touch on items that are part of a publication. It is assumed that the procedure on the subscriber will produce the equivalent effect. When a DAV collection is published, operations on direct and indirect members of the collection are logged into the publication's log. When this is replayed on the subscriber, the operations are repeated on like-named DAV resources, creating collections and resources as needed.

When a table or procedure is added to a publication, the creating statement is added also, so that the subscribers come to create the table or procedure before receiving any replication operations on said table or procedure. Also when the table or precedure is altered, the altering statements are added to the publication so s to be reflected on all subscribers. This can be overridden for procedures, since in some cases it is desirable to have a different definition on the subscriber. Table constraints are also replicated, except for foreign key constraints with tables outside the publication, since these would not be meaninfgful on the subscriber, as there is no knowledge on what tables may exist there outside of the ones in the publication. Changes to schema on subscribers are never replicated to the publisher, even if we had bidirectional replication. Identity and timestamp columns are replicated so that the vaues on the subscriber are assigned by the publisher.

When a procedure is published, it is possible to specify whether the definitions, calls or both are replicated. Usually specifying both calls and definition is reasonable, sometimes only calls are to be replicated if the procedure intentionally has a different definition in the subscriber. This is useful for example when the subscriber gathers statistics or maintains a data warehouse where the storage schema is not identifcal with that of the publisher. Only procedures with input parameters can be replicated. The rationale is that replication is a one way stream and no return values, result sets or output parameters can be captured by either publisher or subscriber. Procedure calls cannot be replicated bidirectionally, these go from publisher to subscriber exclusively.

Even though replication can carry schema changes, it is in no way a means of keping software installations in sync. Many schema elements such as triggers are not covered and a software upgrade is more complex than can be represented by replication alone.

### 11.3.2. Errors in Replication

A statement received for replication from a publisher may encounter an error. All Such errors are logged into the subscriber's error log file. If the error is retryable, like a deadlock, retries are made until the operation succeeds. If Other errors are simply skipped and replication proceeds. A replication subscription can be permanently broken and out of sync if for example columns are dropped from the subscriber copy of the tables or if the subscriber runs out of disk and can replay some transactions but not all. Note that in such situations gaps may be formed into the received transaction sequence. In such cases, it is best to drop the subscription, drop the tables and remake the subscription.

### 11.3.3. Publisher Transactional Replication Functions

The most generic form of the replication element is a stored procedure call. This associates a procedure name and set of parameters to a publication inside a transaction. When the transaction commits, all the publication entries are appended to the publication, forming a replication entry with its unique number.

These functions are available to the publishing Virtuoso server.

repl_publish() repl_unpublish() repl_pub_add() repl_pub_remove() repl_pub_init_image() repl_new_log() repl_text() repl_grant() repl_revoke()

### 11.3.4. Subscriber Functions

These are the functions that are available to the subscribing Virtuoso server.

repl_server() repl_subscribe() repl_unsubscribe() repl_init_copy() repl_sync() repl_sync_all() repl_sched_init() sub_schedule() repl_disconnect() repl_this_server() repl_purge()

### 11.3.5. Common Status Functions

repl_stat() repl_status()

The status () function shows a replication status summary. The same data can be obtained with the repl_stat and repl_status procedures.

### 11.3.6. Bi-Directional Transactional Replication

Virtuoso supports bi-directional transactional replication via a mechanism of updateable subscriptions. The following rules and conditions must be observed:

- Every table has only one publisher.
- Only direct subscribers are considered.
- Only replication of tables is allowed.

It is assumed that all the tables within a publication have primary keys and that the primary key columns are never modified.

Every transaction has an origin, i.e. the originating server on which the transaction was performed.

Modifications to a subscriber come from publisher only using the ordinary transactional replication technique: the subscriber initiates an update and pulls (requests) replication logs from publisher. The publisher sends the replication log from the replication log files and then places the subscriber into "synced" (or "online") mode. In this mode the replication logs are sent to subscriber immediately after each COMMIT.

Data flow from a subscriber to the publisher is very similar: the subscriber initiates the update and pushes replication logs to the publisher. After all replication log data has been sent to the publisher it is put into "synced" mode and will receive modifications immediately after each COMMIT on subscriber.

#### 11.3.6.1. Creating Publications for Updateable Subscriptions

In order to create a publication that allows transaction-based replication and updateable subscriptions use the repl_publish() with a non-zero third argument. Replication feeds from subscribers can be replayed by user different from 'dba' user.

    repl_publish('foo', 'foo.log', 1, 'demo');

This will create updateable publication 'foo'. Replication feeds from subscribers will be replayed as user 'demo'.

#### 11.3.6.2. Adding Tables to a Publication

When a table is added to an updateable publication a new 'ROWGUID varchar' column is automatically added to the table. This column will be used for conflict resolution (described later). If the table already has column with such a name, an existing column will be used (with checking for appropriate data type and width). ROWGUID columns hold globally unique identifiers of a row and are modified after each UPDATE of a row. ROWGUID column values are OSF DCE 1.1 compliant Universally Unique Identifiers (UUID).

ROWGUID columns are used for conflict resolution for INSERT/UPDATE/DELETE DML operations. Basically, if a ROWGUID column that came from a subscriber does not differ from the ROWGUID column of the publisher's table then it is assumed that there is no conflict, otherwise conflict resolution must take place.

#### 11.3.6.3. Conflict Resolution

Since every table may have only one publisher, conflicts resolution will always take place on the publisher.

Assume some DML operation that occurred on a subscriber is being replayed on publisher. There may be three types of conflicts:

1. uniqueness conflict (insert conflict)

   occurs when the row with some primary key <PK> already exists in publisher's table.

2. update conflict

   occurs when an UPDATE modifies a row which has already been modified on publisher (by the publisher or another subscriber).

3. delete conflict

   occurs when an UPDATE modifies a row or a DELETE removes a row that does not exist on publisher anymore.

Every table has a number of conflict resolvers that are used for conflict resolution. These are stored in DB.DBA.SYS_REPL_CR system table. Each conflict resolver has a type ('I', 'U', or 'D') and an order. Conflict resolvers are applied in ascending order.

The conflict resolver is a Virtuoso/PL procedure that receives a conflicting row from a subscriber and some other arguments. The conflict resolver can modify the row, which is passed as an 'inout' argument. The conflict resolver should return an integer value, which will be used for conflict resolution.

Conflict resolvers of different types have different signatures:

- 'I' - Insert conflict resolvers

  (<ALLCOLS>, inout _origin varchar)

- 'U' - Update conflict resolvers

  (<ALLCOLS>, , <ALLOLDCOLS>, inout _origin varchar)

- 'D' - Deletion conflict resolvers

  (<ALLOLDCOLS>, inout _origin varchar)

where

<ALLCOLS> are new values of all columns including the ROWGUID column, <ALLOLDCOLS> are old values of all columns, and _origin is transaction originator.

Conflict resolvers can return the following integer values; The conflict resolver types concerned for each are listed in parentheses:

- 0 - can't decide (I, U, D)

  next conflict resolver will be fired.

- 1 - subscriber wins (I, U, D)

  DML operation will be applied with <ALLCOLS> All the subscribers except originator will receive modifications (originator already has them).

- 2 - subscriber wins, change origin (I, U)

  DML operation will be applied with <ALLCOLS> and origin of transaction will be changed to publisher's server name. All the subscribers (including originator) will receive modifications. This return value is useful when conflict resolver changed some of the columns of the row that were passed in. Although all parameters of conflict resolver are inout only changing of <ALLCOLS> (non-PK columns) parameters makes sense.

- 3 - publisher wins (U)

  DML operation will be applied with <ALLCOLS> taken from publisher's table. All the subscribers will receive modifications.

- 4 - reserved

- 5 - ignore (D)

  DML operation is ignored.

Conflict resolution stops when conflict resolvers return a non-zero value meaning that it has made a decision.

Conflict Resolution

Suppose we have the following table:

    create table items(
      item_id integer primary key,

      name varchar,
      price decimal
    );

"Publisher wins" 'I' conflict resolver will look like:

    create procedure items_cr(
        inout _item_id integer,
        inout _name varchar,
        inout _price decimal,
        inout _origin varchar)
      returns integer
    {
      return 3;
    }

The conflict resolver that will make a decision based on the minimal price column will look like:

    create procedure items_cr(
        inout _item_id integer,
        inout _name varchar,
        inout _price decimal,
        inout _rowguid varchar,
        inout _old_item_id integer,
        inout _old_name varchar,
        inout _old_price decimal,
        inout _old_rowguid varchar,
        inout _origin varchar)
      returns integer
    {
      declare p decimal;
      -- get current price value
      select price into p from items where item_id = _item_id;
      if (p < _price)
        return 3;			-- publisher wins
      else if (p > _price)
        return 1;			-- subscriber wins
      return 0;			-- can't decide
    }

Conflict resolver that will change the price to the minimal value will look like:

    create procedure items_cr(
        inout _item_id integer,
        inout _name varchar,
        inout _price decimal,
        inout _rowguid varchar,
        inout _old_item_id integer,
        inout _old_name varchar,
        inout _old_price decimal,
        inout _old_rowguid varchar,
        inout _origin varchar)
      returns integer
    {
      declare p decimal;
      -- get current price value
      select price into p from items where item_id = _item_id;
      if (p < _price)
        {
          _price := p;
          return 2;			-- publisher wins, change origin
        }
      return 1;			-- subscriber wins
    }

Conflict resolution occurs differently for each kind of DML operation:

- INSERT

  When INSERT of some row with primary key <PK> is replayed, the row in the publisher's table with such <PK> is looked-up. If the row does not exist then there is no conflict, conflict resolution stops and the INSERT is replayed. If the row exists then we have a "uniqueness conflict". In this case 'I' conflict resolvers are fired-up. If none of the 'I' conflict resolvers were able to make a decision (return non-zero value) the default action is 'publisher wins'.

- UPDATE

  When there is an UPDATE of some row with primary key <PK> is replayed, the row (and its ROWGUID) in publisher's table with such <PK> is looked-up. If the row does not exist then we have a "delete conflict", 'D' conflict resolvers are fired up. If none of the 'D' conflict resolvers were able to make a decision the default action will be to 'ignore'. If the row exists in the publisher's table and its ROWGUID is the same as that from the subscriber then there is no conflict. Conflict resolution stops and the UPDATE is replayed. If the row exists and its ROWGUID differs from the one that came from subscriber then we have an "update conflict". In this case the 'U' conflict resolvers are fired-up. If none of the 'U' conflict resolvers were able to make a decision (return non-zero value) the default action will be 'publisher wins'.

- DELETE

  When DELETE of some row with primary key <PK> is replayed, the row in the publisher's table with such <PK> is looked-up. If the row does not exist or if the row exists but its ROWGUID differs from the one that came from subscriber then we have "delete conflict". The 'D' conflict resolvers are fired-up. If none of the 'D' conflict resolvers were able to make a decision then the default action will be taken to 'ignore'. Otherwise it is assumed that there is no conflict and DELETE statement is replayed.

#### 11.3.6.4. Automatically Generated Conflict Resolvers

Simple conflict resolvers can be generated automatically. This can be done by calling REPL_ADD_CR function.

See Also:

REPL_ADD_CR()

#### 11.3.6.5. Replication Log Data

Replication log data is different for each kind of DML operation:

- INSERT

  (stmt, <ALLCOLS>)

- UPDATE

  (stmt, <ALLCOLS>, <OLDPK>, <ALLOLDCOLS>, ncols)

- DELETE

  (stmt, <OLDPK>, <ALLOLDCOLS>, ncols)

where

stmt is DML statement (varchar), <ALLCOLS> is new values of all columns, <OLDPK> is primary key, specifying a row for which (UPDATE or DELETE) DML statement is executed, <ALLOLDCOLS> is old values of all columns, ncols is number of columns in table (integer).

The format of the log replication data is the same as in simple transactional replication with addition of <ALLOLDCOLS> and ncols for logging UPDATE and DELETE statements.

### 11.3.7. Purging replication logs

Every replication account has an associated sequence which holds replication account level (basically, transaction number). Each subscriber also maintains a sequence where it stores its replication account level.

When subscriber issues a sync request for an account it submits its replication account level so publisher can find a point in time from where it should start to submit replication logs to publisher.

Replication account level can roll over REPL_WRAPAROUND (0x7fffffff) to 1. Old replication logs need to be purged to allow this to work correctly. Procedure repl_purge() purges replication logs for a specified account.

Columns needed to store purger configuration in SYS_REPL_ACCOUNTS table are:

- P_MONTH integer (month, nullable)
- P_DAY integer (day of month, nullable)
- P_TIME time (time, nullable)

If P_TIME is NULL log purger will not be scheduled to run at all. If P_TIME is not NULL there can be the following combinations of P_MONTH, P_DAY and P_WDAY ('*' means NULL value, 'x' means any value):

Table: 11.3.7.1. Purger config settings
P_MONTH | P_DAY | P_WDAY | meaning
month | day | x | purger is run yearly on specified month and day
month | * | x | purger is run yearly on 1st of month
* | day | x | purger is run monthly on specified day of month
* | * | wday | purger is run weekly on specified day of week
* | * | * | purger is run daily

An entry to call purger is inserted (or updated) into SYS_SCHEDULED_EVENT after each modification of purger settings for an account (from Admin UI) or after each successful run of repl_purge() for this account.

### 11.3.8. Objects created by transactional replication

Virtuoso creates triggers "<name>_I", "<name>_U" and "<name>_D" for every published table. On subscriber "DB"."DBA"."SYS_REPL_ACCOUNTS" table and "DB"."DBA"."TP_ITEM" view are attached from publisher as "DB"."<dsn>"."SYS_REPL_ACCCOUNTS" and "DB"."<dsn>"."TP_ITEM" respectively.

If publication is updateable Virtuoso additionally creates the following objects:

- "<qual>"."<owner>"."replcr_<name>_I", "<qual>"."<owner>"."replcr_<name>_U" and "<qual>"."<owner>"."replcr_<name>_D" procedures for every published table. These procedures are used for conflict resolution.

- Triggers "<name>_I", "<name>_U" and "<name>_D" for every subscribed table.

Previous Snapshot Replication | Chapter Contents | Next Virtuoso scheduler