A database incremental DDL synchronization method and device

Abstract

The present application relates to the technical field of database, in particular to a database incremental DDL synchronization method and device. The present application respectively starts independent reading threads for DDL operation log and DML operation log, realizes decoupling of the two from the architecture, and the module coupling degree is low, which makes the corresponding DDL capture module and DML capture module can be independently developed, tested, run and maintained, more importantly, the user can flexibly select whether to capture the DDL log according to the actual business scene, in the scene of only needing data synchronization but not needing structure synchronization, the DDL log reading thread can be simply closed, and any influence on the synchronization of DML operation will not be caused, the scene of adaptive DDL flexible synchronization is adapted, and incremental synchronization is realized.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method and apparatus for incremental DDL synchronization of a database. Background Technology

[0002] SQL Server, as an enterprise-level relational database, occupies a crucial position in various application scenarios. Data Definition Language (DDL) operations, as the cornerstone of database systems, use commands such as CREATE, ALTER, and DROP to build and adjust the database structure. With the rapid development of the intelligent economy, the demand for and use of DDL operations are becoming increasingly frequent, and achieving efficient synchronization has gradually become a core requirement for enterprise data management. Incremental DDL synchronization refers to the ability to synchronize structural changes in the source database to other target databases, thereby ensuring the consistency of the source and target database structures.

[0003] Current DDL synchronization implementations either only consider DDL synchronization or integrate DDL and Data Manipulation Language (DML) into a single thread, failing to separate the DDL log capture and processing module from the DML log capture and processing module. As a result, the two modules influence each other, have high coupling between them, and lack flexibility.

[0004] Therefore, overcoming the shortcomings of the existing technology is an urgent problem to be solved in this technical field. Summary of the Invention

[0005] The technical problem to be solved by this invention is to propose an incremental synchronization method for DDL and DML independent processing logs, so as to realize independent processing of DDL and DML without affecting each other.

[0006] The present invention adopts the following technical solution: Firstly, a method for incremental DDL synchronization in a database is provided, including: Enable extended events and CDC (Change Data Capture), and read the relevant parameters of the starting LSN to determine the first starting position of the DDL log reading thread capturing the DDL operation log and the second starting position of the DML log reading thread capturing the DML operation log, respectively. Start an independent DDL log reading thread, capture the DDL operation log of the source database according to the first starting position, and cache the DDL operation log to the DDL cache queue; Start a separate DML log reading thread, capture the DML operation logs of the source database according to the second starting position, and cache the DML operation logs to the DML cache queue; Start the log delivery thread, retrieve the corresponding incremental logs from the DDL cache queue and the DML cache queue respectively, and deliver them to the destination database in LSN order.

[0007] Preferably, the method further includes: Start the SQL Server Agent service and enable database-level CDC and table-level CDC for the source tables to be synchronized in order to capture DML logs; Enable extended events to capture DDL operations of type CREATE, DROP, and ALTER TABLE from the source database; Query the save path of the extended event XML file, and read the maximum transaction number in the extended event through the save path of the extended event XML file, and use the maximum transaction number as the first starting position; Query the largest start_lsn record in the lsn_time_mapping table under CDC mode where tran_id is not 0, and use the largest start_lsn record as the second starting position.

[0008] Preferably, the DDL log reading thread performs the following steps: Periodically query the current maximum transaction number in the extended event and compare it with the first starting position to determine whether there is an incremental DDL log; If incremental DDL logs exist, read the incremental DDL logs and convert the log execution time into the corresponding LSN using a preset system function; The incremental DDL logs are sorted according to their corresponding LSNs. When the LSNs are the same, they are sorted by transaction number. The sorted incremental DDL logs are then stored in the DDL cache queue.

[0009] Preferably, the step of converting the log execution time into the corresponding LSN through a preset system function specifically includes: The default system function sys.fn_cdc_map_time_to_lsn is used to query the lsn_time_mapping table for the largest start_lsn whose execution time is less than the corresponding incremental DDL log time, and use it as the corresponding LSN of the incremental DDL log.

[0010] Preferably, the DML log reading thread performs the following steps: Periodically query the current maximum start_lsn of the lsn_time_mapping table under CDC mode, and compare the current maximum start_lsn with the second starting position to determine whether there is an incremental DML log; If incremental DML logs exist, the corresponding incremental DML logs are read from the CT table according to the range between the second starting position and the current maximum start_lsn, and all incremental DML logs are sorted by LSN and then stored in the DML cache queue.

[0011] Preferably, the log distribution thread performs the following steps: When both the DDL cache queue and the DML cache queue are not empty, the first element in the two cache queues is obtained, and an incremental log with a smaller LSN is sent to the target database. When both cache queues are empty, it is determined that there are currently no incremental DDL logs or incremental DML logs, and the system waits for the next round of queries.

[0012] Preferably, the method further includes: When one cache queue is not empty and the other cache queue is empty, check whether there are unprocessed incremental logs in the log reading thread corresponding to the empty cache queue. If not, directly send the logs to the non-empty cache queue. If they exist, wait for the corresponding logs to be processed, compare the LSNs, and send the smaller one to the target database.

[0013] Preferably, when the DML cache queue is empty and the DDL cache queue is not empty, the method further includes: Create a Flush table and enable the CDC function, then insert a unique record into the Flush table; If no unique record is found in the CT table corresponding to the Flush table, or if the found record has no corresponding LSN in the lsn_time_mapping table, it is determined that there are unprocessed incremental DML logs in the DML cache queue, and we need to wait. If a unique record is found and the corresponding LSN exists in the lsn_time_mapping table, it is determined that there are no unprocessed incremental DML logs.

[0014] Secondly, a database incremental DDL synchronization device is provided, the database incremental DDL synchronization device comprising: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the database incremental DDL synchronization method.

[0015] Thirdly, a non-volatile computer storage medium is provided, the computer storage medium storing computer-executable instructions, which are executed by one or more processors to perform the database incremental DDL synchronization method described in the first aspect.

[0016] Fourthly, a chip is provided, comprising: a processor and an interface for calling and running a computer program stored in memory from memory, performing the database incremental DDL synchronization method as described in the first aspect.

[0017] Fifthly, a computer program product containing instructions is provided that, when executed on a computer or processor, causes the computer or processor to perform the database incremental DDL synchronization method as described in the first to fourth aspects and any one of them.

[0018] Compared with the prior art, the beneficial effects of the present invention are as follows: This invention starts independent reading threads for DDL operation logs and DML operation logs, achieving decoupling between the two at the architectural level. This results in low module coupling, allowing the corresponding DDL capture module and DML capture module to be developed, tested, run, and maintained independently. More importantly, users can flexibly choose whether to capture DDL logs based on actual business scenarios. In scenarios where only data synchronization is required and structure synchronization is not, the DDL log reading thread can be easily shut down without affecting the synchronization of DML operations. This adapts to scenarios requiring flexible DDL synchronization and enables incremental synchronization. Attached Figure Description

[0019] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0020] Figure 1 This is a flowchart illustrating a database incremental DDL synchronization method provided in an embodiment of the present invention; Figure 2 This is a schematic flowchart of obtaining a first starting position and a second starting position according to an embodiment of the present invention; Figure 3 This is a schematic diagram of the workflow of a DDL log reading thread provided in an embodiment of the present invention; Figure 4 This is a schematic diagram of the workflow of a DML log reading thread provided in an embodiment of the present invention; Figure 5This is a schematic diagram of the workflow of a log distribution thread provided in an embodiment of the present invention; Figure 6 This is a schematic diagram of a process for determining incremental DML logs in a DML cache queue, provided by an embodiment of the present invention; Figure 7 This is a schematic diagram of the structure of a database incremental DDL synchronization device provided in an embodiment of the present invention. Detailed Implementation

[0021] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0022] Unless the context otherwise requires, throughout the specification and claims, the term "comprising" is interpreted as openly inclusive, meaning "including, but not limited to." In the description of the specification, terms such as "one embodiment," "some embodiments," "exemplary embodiment," "example," "specific example," or "some examples" are intended to indicate that a particular feature, structure, material, or characteristic associated with that embodiment or example is included in at least one embodiment or example of this disclosure. The illustrative representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics mentioned may be included in any suitable manner in any one or more embodiments or examples; that is, although they may be incorporated into embodiments or examples using the above terms for reasons such as order and position, it does not limit them to be incorporated in combination by a single embodiment or example.

[0023] In the description of this invention, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features. Thus, a feature defined with "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of embodiments of this disclosure, unless otherwise stated, "a plurality of" means two or more. Furthermore, for example, the description may use the prefix "A" or "B" to describe the same type of nouns as two independent entities. In this case, the corresponding features defined with "A" and "B" are used only to distinguish between similar entities and should not be construed as indicating or implying relative importance or implicitly specifying the number of indicated technical features.

[0024] In describing some embodiments, the terms "coupled," "coupled," and "connected," and their derivative expressions, may be used. For example, the term "connected" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact with each other. Similarly, the term "coupled" may be used in describing some embodiments to indicate that two or more components have direct physical or electrical contact. However, the terms "connected" or "coupled" may also refer to two or more components that do not have direct contact with each other but still cooperate or interact with each other, such as "optical coupling," "wireless connection," etc. The embodiments disclosed herein are not necessarily limited to the scope of this invention.

[0025] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other.

[0026] Example 1: Currently, there are two main ways to achieve incremental DDL synchronization in SQL Server databases: one is manual execution, where operators manually perform DDL operations on the target database. This method is relatively traditional, and manual synchronization is not only inefficient, but also prone to errors due to omissions, leading to system failures. Furthermore, it requires operators to possess certain professional knowledge and experience, making it too costly.

[0027] Another approach is automatic execution, which involves capturing changed DDL operations in the source database and automatically synchronizing them to the target database. Currently, DDL operation capture primarily relies on third-party tools, analyzing and parsing database transaction log files, and enabling CDC (Data Catch-Up Directory) to capture source-side DDL logs. While all these methods can capture source-side DDL logs, each has its limitations. For example, third-party tools currently handle DDL operations incompletely, leading to missing or incomplete log entries. Parsing database transaction log files is complex, typically requiring a full scan of the entire transaction log, resulting in high parsing costs and impacting database performance. Furthermore, retrieving table-related DDL operation logs from the `ddl_history` table in CDC mode limits the types of DDL operations that can be captured, failing to capture common table and index creation and deletion operations. Moreover, current DDL synchronization implementations either only consider DDL synchronization or integrate DDL and DML operations into a single thread, failing to separate DDL and DML log capture / processing modules. This results in mutual interference, high coupling between modules, and a lack of flexibility.

[0028] To address the aforementioned issues, this embodiment proposes a database incremental DDL synchronization method. In one embodiment, such as... Figure 1As shown, the database incremental DDL synchronization method specifically includes: Step 101: Enable extended events and CDC, and read the relevant parameters of the starting LSN to determine the first starting position of the DDL log reading thread capturing the DDL operation log and the second starting position of the DML log reading thread capturing the DML operation log.

[0029] Extended events are a lightweight performance monitoring and event capture tool built into SQL Server. They allow for the capture of specific database events (such as DDL operations), consume few resources, and offer flexible capture ranges. The first starting position is the starting scan point for the DDL log reading thread, essentially the largest transaction number in the extended event log (since extended events do not have an LSN, the transaction number is used instead), ensuring that the DDL log reading thread starts scanning from the latest uncaptured log entry.

[0030] Change Data Capture (CDC) is a SQL Server change data capture technology that relies on SQL Server Agent. It captures DML operations and some DDL operations on data tables and stores the change log in the CT table for subsequent synchronization. The second starting position is the starting scan point of the DML log reading thread, which is essentially the maximum start_lsn in the CDC system table lsn_time_mapping, ensuring that the DML log reading thread starts scanning from the latest uncaptured DML log.

[0031] Step 102: Start an independent DDL log reading thread, capture the DDL operation log of the source database according to the first starting position, and cache the DDL operation log to the DDL cache queue.

[0032] The DDL log reading thread is only responsible for capturing and preprocessing DDL operation logs, and can be flexibly enabled and disabled through appropriate configuration (e.g., this thread can be disabled when only DML synchronization is required).

[0033] In one embodiment, DDL operation logs are captured at a first starting position. A DDL log reading thread periodically scans the XML file of extended events (with a configurable sleep interval, such as 5 seconds per scan) to query the maximum transaction number in the current extended event and compares it with the first starting position (initial maximum transaction number). If the current maximum transaction number > the first starting position, it indicates the existence of incremental DDL logs. The thread then reads the DDL operation logs (including operation type, execution time, transaction number, etc.) from the first starting position to the current maximum transaction number in the XML file. If the current maximum transaction number equals the first starting position, it indicates the absence of incremental DDL logs. The DDL log reading thread enters a sleep interval and repeats the scan upon expiration.

[0034] Incremental DDL logs refer to the DDL operation logs (i.e., logs that have not been captured) newly generated by the source database after the DDL log reading thread starts scanning from the first starting position.

[0035] Finally, all captured DDL operation logs are sorted according to LSN and cached in the DDL cache queue.

[0036] Step 103: Start an independent DML log reading thread, capture the DML operation logs of the source database according to the second starting position, and cache the DML operation logs to the DML cache queue.

[0037] The DML log reading thread is only responsible for capturing and preprocessing DML operation logs, and can be flexibly enabled and disabled through appropriate configuration (e.g., if only DDL synchronization is required, this thread can be disabled).

[0038] In one embodiment, DML operation logs are captured at the second starting position. The DML log reading thread periodically (with a configurable sleep interval, which can be consistent with or independently configured with the DDL log reading thread) queries the CDC system table cdc.lsn_time_mapping to obtain the current maximum start_lsn (denoted as curLsn), and compares it with the second starting position (the initial maximum start_lsn). If curLsn > the second starting position, it indicates the existence of incremental DML logs. Based on the LSN range from the second starting position to curLsn, query the corresponding CT table (e.g., cdc.dbo_table_name_CT) and read the DML operation logs within that range (including operation type, changed data, start_lsn, end_lsn, etc.). If curLsn ≤ the second starting position, it indicates the absence of incremental DML logs. The DML log reading thread enters a sleep interval and will repeat the scan upon expiration.

[0039] Incremental DML logs refer to the DML operation logs (i.e., INSERT / UPDATE / DELETE logs that have not been captured) newly generated by the source database after the DML log reading thread starts scanning from the second starting position.

[0040] Finally, all captured DML operation logs are sorted according to LSN and cached in the DML cache queue.

[0041] Step 104: Start the log delivery thread, retrieve the corresponding incremental logs from the DDL cache queue and the DML cache queue respectively, and deliver them to the destination database in LSN order.

[0042] The incremental logs include incremental DDL logs and incremental DML logs. The log delivery thread starts simultaneously with the log reading threads (i.e., the DDL log reading thread and the DML log reading thread), reading logs from two cache queues (i.e., the DDL cache queue and the DML cache queue) and delivering them. In one embodiment, the log delivery thread retrieves the incremental log with the smallest current LSN from the cache queues and delivers it to the target database. The specific delivery method will be described in detail below.

[0043] This embodiment starts independent reading threads for DDL operation logs and DML operation logs, achieving decoupling between the two from an architectural perspective. The module coupling is low, which allows the corresponding DDL capture module and DML capture module to be developed, tested, run and maintained independently. More importantly, users can flexibly choose whether to capture DDL logs according to actual business scenarios. In scenarios where only data synchronization is required and structure synchronization is not required, the DDL log reading thread can be simply turned off without affecting the synchronization of DML operations. This adapts to scenarios with flexible DDL synchronization and enables incremental synchronization.

[0044] In one embodiment, such as Figure 2 As shown, the step of enabling extended events and CDC, and reading the relevant parameters of the initial LSN to determine the first starting position for the DDL log reading thread to capture the DDL operation log and the second starting position for the DML log reading thread to capture the DML operation log specifically includes: Step 1011: Start the SQL Server Agent service, enable database-level CDC and table-level CDC for the source tables to be synchronized, in order to capture DML logs.

[0045] SQL Server Agent is a built-in service of SQL Server used to perform automated jobs (such as scheduled tasks and CDC log capture jobs). It is the runtime environment for CDC functions. Without this service, CDC cannot capture change logs.

[0046] Database-level CDC is the CDC function enabled for the entire source database. Once enabled, it will create the CDC infrastructure (CDC architecture, system tables, roles). Table-level CDC is the CDC function enabled for a specific data table to be synchronized in the source database. Once enabled, the system will create a CT table for that table, and only the change log of that table will be captured by CDC.

[0047] In one embodiment, after enabling table-level CDC, the system will automatically create a corresponding CT table (change table) for the table. Subsequent DML operations (INSERT / UPDATE / DELETE) on this table will be captured by CDC and recorded in the CT table for later reading by the DML log reading thread.

[0048] It is worth noting that table structure modification DDL logs refer to DDL operation logs that modify the data table structure (such as ALTERTABLE adding / deleting fields, modifying field types). These logs can be captured by CDC, while CREATE / DROP type DDL logs require extended event capture. In one embodiment, ALTER TABLE type DDL operations can also be captured using extended events, the difference being that extended events capture a wider range of table structure modification DDL types. In a preferred embodiment, all DDL operations in this embodiment can be captured using extended events.

[0049] Step 1012: Enable extended events to capture DDL operations of type CREATE, DROP, and ALTER TABLE from the source database.

[0050] Enabling extended events will monitor CREATE, DROP, and ALTER TABLE type DDL operations in the source database in real time and write operation details (such as operation time, transaction number, and DDL statement content) to an XML file at a specified path.

[0051] Step 1013: Query the save path of the extended event XML file, and read the maximum transaction number in the extended event through the save path of the extended event XML file, and use the maximum transaction number as the first starting position.

[0052] Before starting the DDL log reading thread, the relevant LSN needs to be read as the starting LSN for the DDL log reading thread to capture logs. The path to the extended event XML file needs to be obtained; the query SQL is shown in Table 1. The path to the extended event XML file is obtained based on the File name tag, facilitating subsequent retrieval of the captured DDL logs.

[0053] First, query the save path of the extended event XML file. This is done by querying the SQL Server system views "sys.dm_xe_sessions" (stores basic extended event session information) and "sys.dm_xe_session_targets" (stores extended event target storage information). Locate the record corresponding to the previously created extended event session, parse the "target_data" field (which is in XML format), and extract the save path of the XML file. The query code is: `SELECT s.name as session_name, cast (st.target_data as xml) as target_data FROM sus.dm_xe_sessions s inner join sys.dm_xe_session_targets st ONs.address = st.event_session_address WHERE s.name='filename'.`

[0054] Then, based on the obtained path, the maximum transaction number in the extended events is read. The SQL Server system function "sys.fn_xe_file_target_read_file" is used to read the XML log file at the specified path. The "transaction_id" field (transaction number) of each DDL operation log entry in the file is parsed, and the transaction number is sorted from largest to smallest. The largest transaction number is then selected. Finally, this largest transaction number is used as the first starting position of the DDL log reading thread, ensuring that the DDL log reading thread only captures new DDL logs after this transaction number, avoiding duplicate captures of existing logs. The specific implementation code for obtaining the first starting position is: `select top 1`. [xml data].value('( / event / data[@name=''transaction_id''] / value)[1]','BIGINT') as [transaction_id] from (select convert (XML, event_data) as [xml data] from sys.fn_xe_file_target_read_file(N'#XML',null,null,null)) as v order by [transaction_id]desc.

[0055] Step 1014: Query the maximum start_lsn record in the lsn_time_mapping table under CDC mode where tran_id is not 0, and use the maximum start_lsn record as the second starting position.

[0056] The lsn_time_mapping table is a core system table that is automatically created by the system after CDC is enabled. It stores the mapping relationship between LSN (log sequence number), transaction time, and transaction ID (i.e., tran_id). Each record corresponds to a database transaction and can help associate the transaction order with the LSN order.

[0057] Then, a query operation is performed to obtain the maximum `start_lsn`. This query retrieves records in the table where `tran_id` is not equal to 0 (excluding empty transactions, which have no actual DML operations and do not require synchronization). The maximum value of the `start_lsn` field is found; this value is the starting LSN of the latest DML transaction captured by CDC, in hexadecimal format. Finally, this maximum `start_lsn` is used as the second starting position for the DML log reading thread. This ensures that the DML log reading thread only captures new DML logs after this LSN, avoiding duplicate captures of existing logs, and synchronizing starts from the latest unsynchronized log. The specific implementation code for obtaining the second starting position is as follows: select max(start_lsn) from cdc.lsn_time_mapping where tran_id<>0x00.

[0058] In one embodiment, such as Figure 3 As shown, the DDL log reading thread performs the following steps: Step 1021: Periodically query the current maximum transaction number in the extended event and compare the current maximum transaction number with the first starting position to determine whether there is an incremental DDL log.

[0059] Once the DDL log reading thread starts, it will repeatedly execute query operations at pre-set time intervals (e.g., every 5 seconds). First, the extended event XML log file is read using SQL Server system functions (such as sys.fn_xe_file_target_read_file). The transaction numbers corresponding to all current DDL operation logs in the file are parsed out, and the largest transaction number is selected. This current maximum transaction number is then compared with the first starting position determined in step 1013 (i.e., the starting transaction number of the DDL log reading thread). If the current maximum transaction number is greater than the first starting position, it means that new DDL operations have been generated in the source database since the DDL log reading thread started or since the last query, and incremental DDL logs exist. If the current maximum transaction number is equal to the first starting position, it means that no new DDL operations have been generated, and there are no incremental DDL logs. In this case, the DDL log reading thread will enter a preset sleep interval, and the above query and comparison process will be executed again after the interval expires.

[0060] Step 1022: If incremental DDL logs exist, read the incremental DDL logs and convert the log execution time into the corresponding LSN using a preset system function.

[0061] Once step 1021 determines that an incremental DDL log exists, the DDL log reading thread will first read all DDL logs between the current maximum transaction number and the first starting position from the extended event XML log file. These logs contain detailed information about the new DDL operation, such as the operation execution time, the corresponding transaction number, and the specific DDL statement.

[0062] Since the DDL log captured by the extended event only records the operation execution time and not the LSN (Log Sequence Number), and subsequent log distribution needs to be sorted by LSN to ensure order, in one embodiment, the preset system function `sys.fn_cdc_map_time_to_lsn` can be used to query the `lsn_time_mapping` table for the largest `start_lsn` whose execution time is less than the corresponding incremental DDL log time, and use this as the corresponding LSN of the incremental DDL log. The query code is as follows: Select sys.fn_cdc_map_time_to_lsn('largest less than','DDL log time').

[0063] In one embodiment, the DDL log reading thread calls a default SQL Server system function (sys.fn_cdc_map_time_to_lsn) to convert the transaction number and LSN: that is, the execution time of each incremental DDL log is used as the function input parameter, the function queries the lsn_time_mapping table of CDC to find the largest start_lsn whose execution time is less than the execution time of the DDL log, and uses this start_lsn as the LSN corresponding to the DDL log, thereby supplementing the LSN information for each incremental DDL log.

[0064] Step 1023: Sort the incremental DDL logs according to the corresponding LSNs. When the LSNs are the same, sort them by transaction number and store the sorted incremental DDL logs into the DDL cache queue.

[0065] After completing the LSN transformation in step 1022, the DDL log reading thread will sort all incremental DDL logs: First, the LSN corresponding to each log is used as the main sorting criterion, and the logs are arranged in ascending order of LSN. This is because the larger the LSN, the later the execution time of the operation corresponding to the log. Sorting in this way can ensure that the log order is consistent with the actual execution order of DDL operations in the source database.

[0066] In one embodiment, if multiple incremental DDL logs have the same LSN (this usually occurs when multiple DDL operations are executed within the same transaction, resulting in identical LSNs), the transaction number corresponding to the log is used as a secondary sorting criterion, and the logs are further sorted in ascending order of transaction number to ensure that even if the LSNs are the same, the logs are arranged according to the order of operations within the transaction. After sorting, the DDL log reading thread stores these ordered incremental DDL logs one by one into a pre-created DDL cache queue. The DDL cache queue temporarily stores these sorted DDL logs, waiting for the subsequent log delivery thread to read and deliver them to the destination database.

[0067] In one embodiment, such as Figure 4 As shown, the DML log reading thread performs the following steps: Step 1031: Periodically query the current maximum start_lsn of the lsn_time_mapping table under CDC mode, and compare the current maximum start_lsn with the second starting position to determine whether there is an incremental DML log.

[0068] Once the DML log reading thread starts, it will repeatedly perform query and judgment operations at a pre-set fixed time interval (e.g., every 3 or 5 seconds).

[0069] First, the DML log reading thread queries the core system table lsn_time_mapping in CDC mode, filters out all valid records from the table (excluding records corresponding to empty transactions), and extracts the maximum value of the start_lsn field. This value is the current maximum start_lsn, which represents the start log sequence number of the latest DML transaction captured by CDC.

[0070] In one embodiment, the DML log reading thread compares the current maximum start_lsn with the second starting position determined in step 1014 (i.e., the starting LSN of the DML log reading thread): if the current maximum start_lsn is greater than the second starting position, it means that since the DML log reading thread started or the last query, new DML operations (insert, update, delete) have been generated in the source database, and there are uncaptured incremental DML logs; if the current maximum start_lsn is less than or equal to the second starting position, it means that there are no new DML operations and no incremental DML logs. At this time, the DML log reading thread will enter a preset sleep interval, and after the sleep ends, the above query and comparison process will be executed again to avoid continuously occupying database resources.

[0071] Step 1032: If incremental DML logs exist, read the corresponding incremental DML logs from the CT table according to the range between the second starting position and the current maximum start_lsn, sort all incremental DML logs by LSN, and store them in the DML cache queue.

[0072] When it is determined that incremental DML logs exist through step 1031, the DML log reading thread will first determine the reading range of the logs, that is, starting from the second starting position and ending at the current maximum start_lsn. This range covers all the incremental DML logs that have not been captured.

[0073] In one embodiment, the DML log reading thread reads the corresponding logs from the CT table (change table) automatically created by CDC for the data table to be synchronized, based on the above LSN range. Each data table to be synchronized has its own dedicated CT table, which stores detailed information about all DML operations on the table (such as operation type, data before and after the change, corresponding start_lsn, etc.). The DML log reading thread filters out all records in the CT table whose start_lsn is within the above range. These records are the incremental DML logs that need to be processed.

[0074] In one embodiment, since the DML logs stored in the CT table already contain `start_lsn`, the DML log reading thread directly sorts the incremental DML logs in ascending order of `start_lsn` (the smaller the `start_lsn`, the earlier the operation was executed), ensuring that the log order is completely consistent with the actual execution order of DML operations in the source database. After sorting, the DML log reading thread stores these ordered incremental DML logs one by one into a pre-created DML cache queue. The DML cache queue serves as a temporary storage container, waiting for subsequent log distribution threads to read and synchronize it to the destination database.

[0075] Since the DDL log reading thread and the DML log reading thread are asynchronous, to ensure that the issued logs have the smallest current LSN, the log issuance thread should read logs from the cache queue according to the following situations. In one embodiment, such as... Figure 5 As shown, the log distribution thread performs the following steps: Step 1041: When both the DDL cache queue and the DML cache queue are not empty, obtain the first element in the two cache queues and send an incremental log with a smaller LSN to the target database.

[0076] When the log delivery thread detects that both the DDL cache queue and the DML cache queue have logs stored (neither of which is empty), it will first retrieve the first element of each queue. Since the DDL cache queue and the DML cache queue have been sorted by LSN from smallest to largest before the logs are stored, the first element of each queue is the log with the smallest LSN in that queue (i.e., the earliest log that needs to be delivered).

[0077] In one embodiment, the log delivery thread extracts the LSNs corresponding to the first two elements and compares them to find the log with the smaller LSN value. It then determines whether the log comes from the DDL cache queue or the DML cache queue, retrieves the log with the smaller LSN from the corresponding cache queue, and delivers it to the destination database.

[0078] After the target database receives the log, it will execute the corresponding operation (such as DDL's CREATE TABLE and DML's INSERT). After successful execution, the log delivery thread will check the status of the two cache queues again and repeat the above process of taking the first element, comparing LSNs, and delivering smaller logs to ensure that each delivery is the earliest executed log among all the logs to be synchronized. This avoids conflicts in the target database due to disordered execution order (such as executing DML for inserting data first and then executing DDL for creating a table, causing the insertion operation to fail).

[0079] Step 1042: When both cache queues are empty, determine that there are currently no incremental DDL logs and incremental DML logs, and wait for the next round of queries.

[0080] Specifically, when the log delivery thread detects that neither the DDL cache queue nor the DML cache queue has any logs stored (both are empty), it determines that there are no newly generated incremental DDL logs or incremental DML logs in the current source database. This means that the DDL log reading thread and the DML log reading thread have not yet captured any new changes, or that all captured logs have already been delivered. At this time, the log delivery thread will not continuously occupy system resources to check the queue status, but will instead enter a preset waiting interval (such as 1 second or 2 seconds, which can be configured according to business needs). After the waiting time ends, it will check the status of the two cache queues again and start the next round of query process: if the queue is still empty, it will continue to wait; if any queue contains logs, it will process the log delivery according to the corresponding scenario (such as step 1041 or other queue status scenarios), balancing the real-time performance of synchronization with the efficiency of system resource consumption.

[0081] In one embodiment, in addition to the two cases mentioned above (both cache queues are empty or neither is empty), the method further includes: when one cache queue is not empty and the other cache queue is empty, determining whether there are unprocessed incremental logs in the log reading thread corresponding to the empty cache queue. If not, the logs of the non-empty cache queue are directly sent. If they exist, the corresponding logs are waited for to be processed, and the LSNs are compared and the smaller one is sent to the target database.

[0082] In one embodiment, when the DDL cache queue is empty but the DML cache queue is not empty, it is necessary to first determine whether the DDL log reading thread has incremental DDL logs and whether it is still processing logs but has not yet been placed into the DDL cache queue. If there are no incremental DDL logs, the corresponding logs are directly retrieved from the DML cache queue and sent. If there are still incremental DDL logs, it is necessary to wait for the corresponding DDL logs to be processed and placed into the DDL cache queue, and then retrieve the first element from it. This element is compared with the logs retrieved from the DML cache queue, and the log with the smaller LSN is directly sent to the target database (this process refers to step 1041).

[0083] In one embodiment, when the DDL cache queue is not empty but the DML cache queue is empty, it is necessary to first determine whether the DML log reading thread has incremental DML logs. If no incremental DML logs exist, the corresponding logs are directly retrieved from the DDL cache queue and sent. If incremental DML logs still exist, it is necessary to wait for the corresponding DML logs to be processed and placed into the DML cache queue, and then retrieve the first element from it. This element is compared with the logs retrieved from the DDL cache queue, and the log with the smaller LSN is sent directly to the target database (this process refers to step 1041).

[0084] It is worth noting that in the above situation, when the database business is relatively busy, the lsn_time_mapping table will get stuck and will not be updated. This will cause the query on the table using the method in step 1031 to be judged as not having incremental DML logs, and thus the DDL logs will be sent directly, resulting in the log order being disordered.

[0085] To address the aforementioned issue, a DML log entry can be manually pushed to determine whether the current situation is stuck or if there is indeed no incremental DML log entry available. In one embodiment, such as... Figure 6 As shown, when the DML cache queue is empty and the DDL cache queue is not empty, the method further includes: Step 201: Create a Flush table and enable the CDC function, then insert a unique record into the Flush table.

[0086] The log distribution thread checks if a Flush table exists in the source database. Its structure is simple, requiring only a single unique field (e.g., flush_id). If the table doesn't exist, it's created automatically; otherwise, it doesn't need to be created again.

[0087] Next, check if table-level Change Detection (CDC) is enabled on the Flush table. If not, enable it using the relevant system functions; if it is already enabled, proceed to the next step. This is to ensure that changes to the Flush table (such as inserted records) can be captured by CDC, thereby generating a corresponding Change Detection (CT) table for easier subsequent verification. Finally, insert a unique, non-repeating record into the Flush table. For example, set flush_id to the current timestamp plus a random number, so that each record used for verification can be clearly identified and will not be confused with previous historical records.

[0088] Step 202: Query the CT table corresponding to the Flush table. If no unique record is found or the found record has no corresponding LSN in the lsn_time_mapping table, it is determined that there are unprocessed incremental DML logs in the DML cache queue, and we need to wait.

[0089] In one embodiment, the log delivery thread performs two verification steps to determine whether the lsn_time_mapping table is truly stuck: The first step is to query the CT table corresponding to the Flush table. This involves accessing the change table generated by CDC for the Flush table and checking if the record exists based on the flush_id inserted in step 201. If it's not found, it means CDC hasn't captured the insert operation in the Flush table yet. This indirectly indicates that the lsn_time_mapping table might be stuck due to database busyness, causing DML logs, including those inserted in the Flush table, to not be synchronized. In this case, it's determined that there are unprocessed incremental DML logs in the DML cache queue. The log delivery thread needs to enter a waiting state, continuously monitoring the DML cache queue until the DML log reading thread stores the unprocessed logs into the DML cache queue.

[0090] The second step is to query the LSN mapping in the `lsn_time_mapping` table. If a flush record is found in the `CT` table, then the `lsn_time_mapping` table is queried. Based on the `start_lsn` of the flush record obtained from the `CT` table, it is checked whether there is a corresponding LSN record in the `lsn_time_mapping` table. If not, it means that although CDC captured the flush record, the `lsn_time_mapping` table has not updated the mapping relationship between the LSN and the transaction, and the table is still stuck. This is also considered as there are still unprocessed incremental DML logs, and the process continues to wait.

[0091] Step 203: If a unique record is found and the corresponding LSN exists in the lsn_time_mapping table, then it is determined that there are no unprocessed incremental DML logs.

[0092] If, after two verifications in step 202—that is, if the unique record is found in both the CT table of the Flush table and the corresponding LSN in the lsn_time_mapping table—it indicates that the CDC system is working normally, the lsn_time_mapping table is not stuck, and the current empty DML cache queue is indeed due to the lack of incremental DML logs, not a system problem. In this case, the log delivery thread no longer needs to wait; it directly retrieves the first log entry from the DDL cache queue (i.e., the log with the smallest LSN in the DDL cache queue) and delivers it to the target database. This ensures timely synchronization of DDL logs and avoids log order disorder caused by misjudging table stuckness, effectively preventing execution conflicts in the target database.

[0093] Example 2: In Embodiment 1, a database incremental DDL synchronization method is provided. In this embodiment, a database incremental DDL synchronization device is proposed, which includes a processor and a memory for storing processor-executable instructions; wherein the processor is configured to execute the database incremental DDL synchronization method described in Embodiment 1.

[0094] like Figure 7 As shown, the database incremental DDL synchronization device includes a processor 21 and a memory 22, wherein the processor 21 and the memory 22 can be connected by a bus or other means.

[0095] Processor 21 can be a Central Processing Unit (CPU). Processor 21 can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, or combinations of the above types of chips.

[0096] The memory 22, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the database incremental DDL synchronization method in Embodiment 1 of this invention. The processor executes various functional applications and training processes by running the non-transitory software programs, instructions, and modules stored in the memory.

[0097] The memory 22 may include a program storage area and a training storage area. The program storage area may store the operating system and applications required for at least one function; the training storage area may store training data created by the processor. Furthermore, the memory may include high-speed random access memory and non-transitory memory, such as at least one disk storage device, flash memory, or other non-transitory solid-state storage device. In some embodiments, the memory 22 may optionally include memory remotely located relative to the processor, which can be connected to the processor via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof. The one or more modules stored in the memory 22, when executed by the processor 21, perform functions such as... Figure 1The incremental DDL synchronization method for the database shown in Example 1 is illustrated. For specific details regarding the above incremental DDL synchronization method, please refer to the relevant documentation. Figure 1 , Figure 2 and Figure 3 The relevant descriptions and effects in the embodiments shown are for reference only and will not be repeated here.

[0098] This embodiment also provides a computer storage medium storing a computer program that can be executed by a processor to perform the database incremental DDL synchronization method described in Embodiment 1.

[0099] The computer storage medium stores computer-executable instructions, which can execute the database incremental DDL synchronization method in any of the above method embodiments. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), random access memory (RAM), flash memory, hard disk drive (HDD), or solid-state drive (SSD), etc.; the storage medium may also include combinations of the above types of memory.

[0100] The specific steps of the incremental DDL synchronization method for the database are described in Example 1, and will not be repeated in this example.

[0101] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A database incremental DDL synchronization method, characterized in that, include: Enable extended events and CDC, and read the relevant parameters of the starting LSN to determine the first starting position of the DDL log reading thread capturing the DDL operation log and the second starting position of the DML log reading thread capturing the DML operation log, respectively. Start an independent DDL log reading thread, capture the DDL operation log of the source database according to the first starting position, and cache the DDL operation log to the DDL cache queue; Start a separate DML log reading thread, capture the DML operation logs of the source database according to the second starting position, and cache the DML operation logs to the DML cache queue; Start the log delivery thread, retrieve the corresponding incremental logs from the DDL cache queue and the DML cache queue respectively, and deliver them to the destination database in LSN order.

2. The database incremental DDL synchronization method according to claim 1, characterized in that, The method further includes: Start the SQL Server Agent service and enable database-level CDC and table-level CDC for the source tables to be synchronized in order to capture DML logs; Enable extended events to capture DDL operations of type CREATE, DROP, and ALTER TABLE from the source database; Query the save path of the extended event XML file, and read the maximum transaction number in the extended event through the save path of the extended event XML file, and use the maximum transaction number as the first starting position; Query the largest start_lsn record in the lsn_time_mapping table under CDC mode where tran_id is not 0, and use the largest start_lsn record as the second starting position.

3. The database incremental DDL synchronization method according to claim 1, characterized in that, The DDL log reading thread performs the following steps: Periodically query the current maximum transaction number in the extended event and compare it with the first starting position to determine whether there is an incremental DDL log; If incremental DDL logs exist, read the incremental DDL logs and convert the log execution time into the corresponding LSN using a preset system function; The incremental DDL logs are sorted according to their corresponding LSNs. When the LSNs are the same, they are sorted by transaction number. The sorted incremental DDL logs are then stored in the DDL cache queue.

4. The database incremental DDL synchronization method according to claim 3, characterized in that, The process of converting log execution time into corresponding LSNs using a preset system function specifically includes: The default system function sys.fn_cdc_map_time_to_lsn is used to query the lsn_time_mapping table for the largest start_lsn whose execution time is less than the corresponding incremental DDL log time, and use it as the corresponding LSN of the incremental DDL log.

5. The database incremental DDL synchronization method according to claim 1, characterized in that, The DML log reading thread performs the following steps: Periodically query the current maximum start_lsn of the lsn_time_mapping table under CDC mode, and compare the current maximum start_lsn with the second starting position to determine whether there is an incremental DML log; If incremental DML logs exist, the corresponding incremental DML logs are read from the CT table according to the range between the second starting position and the current maximum start_lsn, and all incremental DML logs are sorted by LSN and then stored in the DML cache queue.

6. The database incremental DDL synchronization method according to claim 1, characterized in that, The log delivery thread performs the following steps: When both the DDL cache queue and the DML cache queue are not empty, the first element in the two cache queues is obtained, and an incremental log with a smaller LSN is sent to the target database. When both cache queues are empty, it is determined that there are currently no incremental DDL logs or incremental DML logs, and the system waits for the next round of queries.

7. The database incremental DDL synchronization method according to claim 1, characterized in that, The method further includes: When one cache queue is not empty and the other cache queue is empty, check whether there are unprocessed incremental logs in the log reading thread corresponding to the empty cache queue. If not, directly send the logs to the non-empty cache queue. If they exist, wait for the corresponding logs to be processed, compare the LSNs, and send the smaller one to the target database.

8. The database incremental DDL synchronization method according to claim 7, characterized in that, When the DML cache queue is empty and the DDL cache queue is not empty, the method further includes: Create a Flush table and enable the CDC function, then insert a unique record into the Flush table; If no unique record is found in the CT table corresponding to the Flush table, or if the found record has no corresponding LSN in the lsn_time_mapping table, it is determined that there are unprocessed incremental DML logs in the DML cache queue, and we need to wait. If a unique record is found and the corresponding LSN exists in the lsn_time_mapping table, it is determined that there are no unprocessed incremental DML logs.

9. A database incremental DDL synchronization device, characterized in that, The database incremental DDL synchronization device includes: a processor and a memory for storing processor-executable instructions; The processor is configured to execute the database incremental DDL synchronization method according to any one of claims 1-8.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions, which are executed by one or more processors to perform the database incremental DDL synchronization method according to any one of claims 1-8.

Images