How to Ensure the atomicity of transaction submission at the destination in Database Synchronization

In the database synchronization process, both the Database loading efficiency and the atomicity of each transaction in the database must be ensured, that is, the execution of Multiple SQL statements in a transaction must be successful for the above purposes, we select the multi-Table concurrent and multi-breakpoint mode for synchronization. The principle is to extract the transactions to be synchronized and divide the transactions according to the table and SQL statement volume for different processes to execute, each process has a breakpoint. When an SQL statement of a table fails to be executed, the process records the breakpoint, the table is stopped, and data synchronization of other tables continues, after you manually process the SQL statement that has an error in the table, the process re-executes or skips the SQL statement that needs to be synchronized to the error table based on the mark, so that the synchronization process continues, in addition, errors in some tables do not affect the overall synchronization.

So how can atomicity be consistent during synchronization? You can achieve this by using grid breakpoints. A grid breakpoint is a flow file that associates the transaction SCN number with the table sequence of each SQL statement and records the execution of an SQL statement in the SCN number, the log analysis process creates multiple grid files based on the table name. The content is the SCN number and the execution statement, which are sorted by the SCN number. In this way, each synchronization process can read its own table files, and concurrently load according to the sequence of the SCN number, so that the data at the same time point is loaded according to the SCN number, and the query statistics consistency on the slave database is ensured.

When each process is successfully executed, it will modify its corresponding breakpoint file. Therefore, when the system encounters an exception, we will check all the breakpoint files so that the database can continue Synchronization Based on the breakpoint, handle errors for different DDL and DML statements.