Synchronize tables without primary keys or UNIQUE constraints - Data Transmission Service

Data Transmission Service (DTS) provides the Exactly-Once write feature. This feature allows you to synchronize full and incremental data from source tables that do not have primary keys or UNIQUE constraints to destination tables.

Limits

The Exactly-Once write feature is supported only for full and incremental data synchronization in specific two-way data synchronization instances. For more information, see the Supported instances section of this topic.
The source database must contain tables that do not have primary keys or UNIQUE constraints and the tables must meet the following requirements:
- If the Database Type of the source database is MySQL or PolarDB for MySQL, the storage engine of the tables to be synchronized must be InnoDB.
- The size of a single table to be synchronized cannot exceed 200 GB.
- Only DDL operations that are performed to delete data can be synchronized by using the Exactly-Once write feature. In this case, DTS does not guarantee data consistency between the source and destination databases.

Supported instances

Important

You can use the Exactly-Once write feature only for two-way data synchronization instances.

Source database	Destination database
MySQL	MySQL and PolarDB for MySQL
PolarDB for MySQL	MySQL and PolarDB for MySQL
PostgreSQL	PostgreSQL and PolarDB for PostgreSQL
PolarDB for PostgreSQL	PostgreSQL and PolarDB for PostgreSQL.
PolarDB (Compatible with Oracle)	PolarDB (Compatible with Oracle)

Usage notes

If the Database Type of the source database is MySQL or PolarDB for MySQL, tables that do not have primary keys or UNIQUE constraints in the source database are temporarily locked during data synchronization.
During full or incremental data synchronization, DTS creates a database or schema named dts in the destination instance and creates multiple transaction tables in the database or schema. Do not perform DML operations on the transaction tables when the DTS task is running.
By default, the setting of Enable Exactly-Once Write for a reverse synchronization task is the same as that for the forward synchronization task and cannot be modified.

Procedure

Go to the Select Objects step of a forward synchronization task.
Note
- If you use the previous version of the configuration page, go to the Configure Objects and Advanced Settings step.
- For more information, see the topics listed in Overview of data synchronization scenarios.
Set the Enable Exactly-Once Write parameter to Yes.
Complete the configurations based on your needs.