Database increment DDL synchronization method and device

Abstract

The invention relates to the technical field of databases, in particular to a database incremental DDL synchronization method and device. The independent reading threads are respectively started for the DDL operation log and the DML operation log, decoupling of the DDL operation log and the DML operation log is achieved in the aspect of architecture, the module coupling degree is low, the corresponding DDL capture module and the DML capture module can be independently developed, tested, operated and maintained, and more importantly, the system has the advantages of being simple in structure and convenient to use. A user can flexibly select whether the DDL log needs to be captured or not according to an actual service scene, a DDL log reading thread can be simply closed under the scene that only data synchronization is needed and structure synchronization is not needed, no influence is generated on synchronization of DML operation, the method is adaptive to the scene of DDL flexible synchronization, and incremental synchronization is achieved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a database incremental DDL synchronization method and device. BACKGROUND

[0002] SQL Server as an enterprise-level relational database, occupies an important position in the current various application business scenarios. Data Definition Language (Data Definition Language, for short DDL) operation as the cornerstone of the database system, through the CREATE, ALTER, DROP and other instructions to complete the construction and adjustment of the library structure, in the intelligent economy rapid development trend, the demand and use of DDL operation in the application is increasingly frequent, to realize its efficient synchronization has gradually become the core demand of enterprise data management. Incremental DDL synchronization refers to the structural change operation in the source database, which can synchronize these operations to other target databases, so as to ensure the consistency of the source database and the target database structure.

[0003] The current DDL synchronization implementation method either only considers DDL synchronization, or combines DDL and Data Manipulation Language (Data Manipulation Language, for short DML) in a thread processing, without separating the DDL log capture processing module and the DML log capture processing module, the two influence each other, the coupling degree between modules is high, and the flexibility is poor.

[0004] Therefore, it is urgent to overcome the defects of the prior art in the technical field. SUMMARY

[0005] The technical problem to be solved by the present application is to provide an incremental synchronization method for DDL and DML independent processing logs, to realize independent processing of DDL and DML without affecting each other.

[0006] The present application adopts the following technical scheme: In a first aspect, an incremental DDL synchronization method for a database is provided, comprising: Starting an extended event and CDC (English full name: Change Data Capture), and reading the starting LSN related parameters to determine the first starting position of the DDL operation log captured by the DDL log reading thread and the second starting position of the DML operation log captured by the DML log reading thread, respectively; Starting an independent DDL log reading thread, and capturing the DDL operation log of the source database according to the first starting position, and caching the DDL operation log to the DDL cache queue; starting an independent DML log reading thread, and capturing DML operation logs of the source database according to the second starting position, and caching the DML operation logs to a DML cache queue; starting a log issuing thread, obtaining corresponding incremental logs from the DDL cache queue and the DML cache queue respectively, and issuing the incremental logs to the target database in LSN order.

[0007] Preferably, the method further comprises: starting a SQL Server agent service, starting a database-level CDC and a table-level CDC of the source data table to be synchronized, to capture DML logs; starting an extended event to capture CREATE, DROP and ALTER TABLE type DDL operations of the source database; querying a storage path of the extended event XML file, reading a maximum transaction number in the extended event through the storage path of the extended event XML file, and taking the maximum transaction number as the first starting position; querying a maximum start_lsn record with a tran_id of 0 in the lsn_time_mapping table in the CDC mode, and taking the maximum start_lsn record as the second starting position.

[0008] Preferably, the DDL log reading thread performs the following steps: periodically querying a current maximum transaction number in the extended event, and comparing the current maximum transaction number with the first starting position to determine whether there is incremental DDL log; if there is incremental DDL log, reading the incremental DDL log and converting log execution time into corresponding LSN through a preset system function; sorting the incremental DDL log in corresponding LSN order, sorting in transaction number order when the LSN is the same, and storing the sorted incremental DDL log in the DDL cache queue.

[0009] Preferably, the converting log execution time into corresponding LSN through the preset system function specifically comprises: using a preset system function sys.fn_cdc_map_time_to_lsn to query a maximum start_lsn in the lsn_time_mapping table with execution time less than the time of the corresponding incremental DDL log as the corresponding LSN of the corresponding incremental DDL log.

[0010] Preferably, the DML log reading thread performs the following steps: query the current maximum start_lsn of the lsn_time_mapping table in the periodic query CDC mode, and compare the current maximum start_lsn with the second start position to determine whether there is incremental DML log; If there is incremental DML log, read the corresponding incremental DML log from the CT table according to the range between the second start position and the current maximum start_lsn, and store all the incremental DML logs in the DML cache queue after sorting them according to LSN.

[0011] Preferably, the log issuing thread performs the following steps: When the DDL cache queue and the DML cache queue are both not empty, get the first element in the two cache queues, and issue the incremental log with smaller LSN to the target database. When the two cache queues are both empty, it is determined that there is no incremental DML log and incremental DDL log at present, and the next round of query is waited.

[0012] Preferably, the method further comprises: When one of the cache queues is not empty and the other is empty, it is determined whether there is unprocessed incremental log in the log reading thread corresponding to the empty cache queue, and if not, the log of the non-empty cache queue is directly issued, and if so, the smaller one is issued to the target database after comparing LSN and waiting for the corresponding log processing to be completed.

[0013] Preferably, when the DML cache queue is empty and the DDL cache queue is not empty, the method further comprises: Create a Flush table and start the CDC function, and insert a unique record into the Flush table; Query the CT table corresponding to the Flush table, and if no unique record is queried or the queried record has no corresponding LSN in the lsn_time_mapping table, it is determined that there is unprocessed incremental DML log in the DML cache queue, and waiting is required; If the unique record is queried and there is corresponding LSN in the lsn_time_mapping table, it is determined that there is no unprocessed incremental DML log.

[0014] In a second aspect, a database incremental DDL synchronization device is provided, which comprises a processor and a memory for storing processor executable instructions; The processor is configured to execute the database incremental DDL synchronization method.

[0015] In a third aspect, a non-transitory computer storage medium is provided, which stores computer executable instructions, and the computer executable instructions are executed by one or more processors to implement the database incremental DDL synchronization method in the first aspect.

[0016] In a fourth aspect, a chip is provided, which comprises a processor and an interface, and is used to call and run a computer program stored in a memory to implement the database incremental DDL synchronization method in the first aspect.

[0017] In a fifth aspect, a computer program product is provided, which comprises instructions, and when the instructions are executed on a computer or a processor, the computer or the processor is caused to implement the database incremental DDL synchronization method in the first aspect to the fourth aspect and any one of the aspects.

[0018] Compared with the prior art, the present application has the beneficial effects that: The present application starts independent reading threads for the DDL operation log and the DML operation log respectively, realizes decoupling of the two from the architecture, and has low module coupling, which makes the corresponding DDL capture module and the DML capture module can be independently developed, tested, run and maintained, and more importantly, the user can flexibly select whether to capture the DDL log according to the actual business scenario, in the scenario of only needing data synchronization but not structure synchronization, the DDL log reading thread can be simply closed, and there is no any influence on the synchronization of the DML operation, which adapts to the scenario of flexible DDL synchronization and realizes incremental synchronization. BRIEF DESCRIPTION OF DRAWINGS

[0019] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings needed to be used in the embodiments or the prior art description. Obviously, the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can also be obtained without creative labor based on these drawings.

[0020] Figure 1 is a flowchart of a database incremental DDL synchronization method provided by an embodiment of the present application; Figure 2 is a flowchart of obtaining a first starting position and a second starting position provided by an embodiment of the present application; Figure 3 is a working flowchart of a DDL log reading thread provided by an embodiment of the present application; Figure 4 is a working flowchart of a DML log reading thread provided by an embodiment of the present application; 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 from the two cache queues and send the incremental log with the 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, The method comprises the following steps: starting an extended event and CDC, and reading a starting LSN related parameter to determine a first starting position of a DDL operation log captured by a DDL log reading thread and a second starting position of a DML operation log captured by a DML log reading thread, respectively; starting an independent DDL log reading thread, and capturing a DDL operation log of a source database according to the first starting position, and caching the DDL operation log to a DDL cache queue; starting an independent DML log reading thread, and capturing a DML operation log of the source database according to the second starting position, and caching the DML operation log to a DML cache queue; starting a log issuing thread, obtaining corresponding incremental logs from the DDL cache queue and the DML cache queue, respectively, and issuing the incremental logs to a target database in an LSN order.

2. The database incremental DDL synchronization method of claim 1, wherein, The method further comprises the following steps: starting a SQL Server agent service, starting a database level CDC and a table level CDC of a source data table to be synchronized, to capture a DML log; starting an extended event to capture a DDL operation of a CREATE, DROP and ALTER TABLE type of the source database; querying a storage path of an extended event XML file, reading a maximum transaction number in the extended event through the storage path of the extended event XML file, and taking the maximum transaction number as the first starting position; querying a maximum start_lsn record of a tran_id not being 0 in an lsn_time_mapping table in a CDC mode, and taking the maximum start_lsn record as the second starting position.

3. The database incremental DDL synchronization method of claim 1, wherein, The DDL log reading thread performs the following steps: periodically querying a current maximum transaction number in the extended event, and comparing the current maximum transaction number with the first starting position to determine whether there is incremental DDL log; if there is incremental DDL log, reading the incremental DDL log and converting a log execution time into a corresponding LSN through a preset system function; sorting the incremental DDL log according to the corresponding LSN in a sequence, sorting the incremental DDL log according to a transaction number when the LSN is the same, and storing the sorted incremental DDL log in the DDL cache queue.

4. The database incremental DDL synchronization method of claim 3, wherein, The converting of the log execution time into the corresponding LSN through the preset system function specifically comprises the following steps: using a preset system function sys.fn_cdc_map_time_to_lsn to query a maximum start_lsn in the lsn_time_mapping table, which is smaller than a time corresponding to the incremental DDL log, as a corresponding LSN of the incremental DDL log.

5. The database incremental DDL synchronization method of claim 1, wherein, The DML log reading thread performs the following steps: periodically querying a current maximum start_lsn of the lsn_time_mapping table in the CDC mode, and comparing the current maximum start_lsn with the second starting position to determine whether there is incremental DML log; If there is incremental DML log, read corresponding incremental DML log from the CT table according to the range between the second starting position and the current maximum start_lsn, and store all incremental DML logs in the DML cache queue in order of LSN.

6. The database incremental DDL synchronization method of claim 1, wherein, The log issuing thread performs the following steps: When both the DDL cache queue and the DML cache queue are not empty, get the first element in the two cache queues, and issue the incremental log with smaller LSN to the target database. When both the DDL cache queue and the DML cache queue are empty, it is determined that there is no incremental DDL log and incremental DML log at present, and the next round of query is waited.

7. The database incremental DDL synchronization method of claim 1, wherein, The method further comprises: When one of the cache queues is not empty and the other is empty, it is determined whether the log reading thread corresponding to the empty cache queue has unprocessed incremental log, and if not, the log of the non-empty cache queue is directly issued, and if so, the smaller one is issued to the target database after the corresponding log processing is completed.

8. The database incremental DDL synchronization method of claim 7, wherein, When the DML cache queue is empty and the DDL cache queue is not empty, the method further comprises: Create a Flush table and start the CDC function, and insert a unique record into the Flush table; Query the CT table corresponding to the Flush table, and if no unique record is found or the record has no corresponding LSN in the lsn_time_mapping table, it is determined that there is unprocessed incremental DML log in the DML cache queue, which needs to be waited; If the unique record is found and there is a corresponding LSN in the lsn_time_mapping table, it is determined that there is no unprocessed incremental DML log.

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

10. A non-transitory computer storage medium, comprising, The computer storage medium stores computer executable instructions executed by one or more processors to complete the database incremental DDL synchronization method of any one of claims 1-8.

Images