tencent cloud

Overview

DTS supports complex topology structures, including many-to-one, one-to-many, cascading one-way, two-way, and cascading two-way sync. In such a structure, data is written to multiple nodes at the same time, so primary key conflicts may occur. To address this issue, DTS detects primary key conflicts and provides the following resolution policies:

Examples

Primary key conflict resolution policies take effect only for INSERT and UPDATE primary key conflicts but not in non-conflict scenarios. After a policy is applied, the task can report an error or proceed once a conflict occurs. Below are examples of two primary key conflict scenarios with results under different policies.

INSERT primary key conflict

An A > B one-way sync with ID as the primary key is created. When an INSERT statement in A has a primary key conflict with the data in B during data sync, DTS will handle the conflict according to the configured conflict resolution policy.

The respective sync results in B under different policies are as detailed below:

• Report: The task reports an error, and the data in B remains unchanged (ID=1, Price=10).
• Ignore: The task ignores the data with the same primary key in A, and the data in B remains unchanged (ID=1, Price=10).
• Overwrite: The task overwrites the data in B with the data with the same primary key in A, and the data in B becomes ID=1, Price=20.

UPDATE primary key conflict

In some scenarios, you may modify the primary key, leading to a primary key conflict. For example, the primary key in A is updated (ID=1 > ID=2), which will conflict with the data with primary key ID being 2 in B.

The respective sync results in B under different policies are as detailed below:

• Report: The task reports an error, and the data in B remains unchanged.
• Ignore: The task reports an error, and the data in B remains unchanged. Note that DTS ignores the conflict in this case.
• Overwrite: The task overwrites the data in B with the data with the same primary key in A, and only the data with primary key 2 exists in B (ID=2, Price=10).

Conflict Resolution Policy and Data Consistency

In complex data architectures such as 2-region-3-DC and multi-site active-active architectures, data may need to be written to three or more nodes at the same time, and it is crucial to guarantee the data consistency across multiple nodes. Many users believe that they can use a primary key conflict resolution policy to sync the data on the specified node to other nodes, but this actually doesn't work.

In the following two-way sync scenario, the Overwrite policy is set for both A > B and B > A sync. If different data records with the primary key 1 are inserted into nodes A and B at the same time, they will be swapped with each other between A and B eventually.

In real-world scenarios, to implement data consistency across nodes, you generally need to partition the database by primary key, introduce additional coordination mechanisms such as data overwriting by version number, and use other methods in addition to a conflict resolution policy.