Tables without primary keys or UNIQUE constraints have no unique row identifier, which means standard synchronization can write the same row more than once — for example, when the incremental write module restarts after an interruption. The Exactly-Once write feature in Data Transmission Service (DTS) prevents duplicate writes by tracking each committed row in transactional tables, so replayed operations are safely skipped.
How it works
During full synchronization, DTS performs a full table scan on every table without primary keys or UNIQUE constraints and generates table snapshots to establish a baseline.
For incremental synchronization, DTS maintains a database named dts in the destination instance. This database holds transactional tables that record which rows have already been written. When the incremental write module replays a batch — for example, after a restart — DTS checks these tables and skips rows that were already committed, preventing duplicates.
Exactly-Once write is supported only for two-way synchronization instances.
Supported integrations
Exactly-Once write supports the following source and destination database combinations. All combinations require a two-way synchronization instance.
| Source database | Destination database |
|---|---|
| MySQL | MySQL, PolarDB for MySQL |
| PolarDB for MySQL | MySQL, PolarDB for MySQL |
Scope and limitations
Before enabling this feature, confirm that your setup meets all of the following requirements:
The synchronization task must be a full and incremental data synchronization task in a specific two-way synchronization instance. For supported scenarios, see Supported data synchronization scenarios.
Multi-master Cluster (Limitless) Edition PolarDB for MySQL clusters do not support this feature.
Tasks using the serverless billing method do not support this feature.
When enabled, incremental write performance is limited to 3,000 RPS.
DDL operations are not supported. Running DDL during an active task may cause the task to fail. Data-clearing DDL operations such as
TRUNCATEmay cause data inconsistency.For tables without primary keys or UNIQUE constraints:
If the source database Database Type is MySQL and Access Method is not Alibaba Cloud Instance, the source database must be in global transaction identifier (GTID) mode, and the tables must use a transactional storage engine such as InnoDB.
A single table cannot exceed 200 GB.
Usage notes
Tables are locked during synchronization
When the source Database Type is MySQL or PolarDB for MySQL, tables without primary keys or UNIQUE constraints are temporarily locked during synchronization.
Data loss may occur after a data-clearing DDL operation
Running a data-clearing DDL operation such as TRUNCATE in the source database while Exactly-Once write is active may cause data loss, especially if the incremental write module restarts after the operation. You are responsible for ensuring data consistency.
Do not modify the dts database on the destination
Full and incremental synchronization tasks create a database or schema named dts in the destination instance and populate it with transactional tables. Do not perform DML operations on these tables while the DTS task is running.
Remote sync tasks inherit the Exactly-Once write setting
For a remote sync task, the Enable Exactly-Once Write setting defaults to the configuration of the forward sync task and cannot be changed.
Enable Exactly-Once write
In the forward sync task configuration, go to the Configure Objects step.
If you are using the previous version of the configuration page, go to the Configure Objects and Advanced Settings step instead. For configuration instructions, see Overview of synchronization solutions.
Set Enable Exactly-Once Write to Yes.
Complete the remaining configuration.