Synchronization Replication with the Trigger-Based Method v6.2
If a publication in a single-master replication system is created that will be used in synchronization replications with the trigger-based method, the publication server installs an insert trigger, an update trigger, and a delete trigger on each publication table. In a multi-master replication system, each replicated table in each primary node employing the trigger-based method has an insert trigger, an update trigger, and a delete trigger.
Oracle and SQL Server databases can only be used as publication databases with the trigger-based method. Additionally, Oracle and SQL Server databases can be used as subscription databases with the WAL-based replication method.
The publication server also creates a shadow table for each source table on which triggers have been created. A shadow table is a table used by xDB Replication Server to record the changes (inserts, updates, and deletions) made to a given source table. A shadow table records three types of record images:
For each row inserted into the source table, the shadow table records the image of the inserted row.
For each existing row that is updated in the source table, the shadow table records the after image of the updated row.
For each row deleted from the source table, the shadow table records the primary key value of the deleted row.
Note
In a multi-master replication system, the before image of an updated row is also stored in the shadow table in order to perform update conflict detection. See Conflict Resolution for information on conflict detection in a multi-master replication system.
After each change on the source table, one of the insert, update, or delete triggers is executed. These are row triggers, so for each row affected by the change, the trigger executes. Each execution of the trigger records a row of the appropriate type (insert, update, or deletion) in the shadow table of the corresponding source table.
Though changes made to the source tables since the last replication occurred are applied to the target tables using SQL INSERT, UPDATE, and DELETE statements, the actual SQL statements run against the target tables are not the same SQL statements that were run against the source tables.
When synchronization replication occurs, the publication server executes JDBC batches of SQL statements (also referred to as transaction sets) against the target tables. The batches contain an INSERT statement for each shadow table row recording an insert operation, an UPDATE statement for each shadow table row recording an update operation, and a DELETE statement for each shadow table row recording a delete operation. Each batch is executed in one transaction.
Shadow table rows that were applied to target tables can be viewed as shadow table history in the xDB Replication Console (see Shadow Table History).
Note
A single SQL statement executed against a source table may result in many rows recorded in a shadow table, and therefore, many SQL statements executed against the target table. For example, if a single UPDATE statement affects 10 rows in the source table, 10 rows will be inserted into the shadow table – one for each row in the source table that was updated. When the publication server applies the changes to the target table, 10 UPDATE statements will be executed.
Note
For greater efficiency, when changes to the source tables consist of SQL statements that each affect a large number of rows, the publication server may employ the use of prepared SQL statements. See Optimizing Synchronization Replication for directions on how to control the usage of prepared SQL statements as well as information on various other configuration options to optimize synchronization replication.