A table-level bidirectional synchronization realization method and system based on trigger

Abstract

The invention relates to the technical field of data synchronization, in particular to a table-level bidirectional synchronization realization method and system based on a trigger. The method comprises the following steps: source data changes in a current base table are captured; the source data changes are triggered by a synchronization system and/or a common user; source data changes triggered by a dedicated user are filtered out the source data changes triggered by a non-dedicated user is written into a corresponding CDC table; The synchronization system reads records in the CDC table and synchronizes another base table participating in bidirectional synchronization according to the recorded data. The table-level bidirectional synchronization is realized, a special user is created for the synchronization system, corresponding to the special user ID, which is different from the ordinary user, so that the trigger can filter the record according to the user of the current triggering operation, thereby preventing the data change triggered by the synchronization system from being written into the CDC table, thus solving the problem of the cycle triggering of the bidirectional synchronization.

Description

【Technical field】 [0001] The invention relates to the technical field of data synchronization, in particular to a trigger-based table-level bidirectional synchronization implementation method and system. 【Background technique】 [0002] With the deepening of data exchange and data sharing applications, users are no longer satisfied with one-way data integration or synchronization, and the flow of data between different departments or between upper and lower levels is increasingly showing two-way characteristics. For example, in data center applications, it is generally necessary to synchronize data from various departments to the central library. However, with the development of departmental applications, a department may also need to obtain data from other departments from the center while providing data to the center. Departmental applications and analytics, which lead to the need for bi-directional synchronization. [0003] Table-level two-way data synchronization is gene...

AI Technical Summary

Problems solved by technology

[0009] The first is the problem of cyclic synchronization conflicts. The data changes triggered by the synchronization system cannot be distinguished from the data changes triggered by ordinary users. The data synchronized from base table A to base table B will be re-synchronized to base table A as changed data. , the data synchronized from base table B to base table A will be re-synchronized to base table B as changed data, resulting in an endless loop of data synchronization; when there are three or more base tables, if every two base tables Two-way synchronization of data is required between tables, and an infinite loop of data synchronization will be formed between every two base tables, which will have a wider impact

The second is the problem of data modification conflicts, that is, base table A and base table B modify the same field of the record with the same primary key at the same time. At this time, if the two-way synchronization from base table A to base table B and from base table B to base table A is executed at the same time , the records of the modified base table A and base table B only exchange positions, and the data on both sides are not consistent

The third is the problem of data insertion conflicts, that is, base table A and base table B insert data with the same primary key. At this time, the two-way synchronization database will report a violation of unique constraint error, resulting in synchronization failure.

Images