A method for batch warehousing of multi-threaded data synchronization between databases

Abstract

The present invention proposes a multi-threaded data synchronization and batch storage method between databases, comprising: creating at least two sub-threads for reading transaction logs, and reading different content blocks of the source database transaction log in parallel; each sub-thread generates SQL execution statements and creates SQL files in parallel based on the content blocks read by the sub-threads, and writes the file names of the SQL files into a sequence of files to be executed; each sub-thread writes the generated SQL execution statements in parallel to the corresponding SQL files in the current list of files to be executed; the main thread sequentially reads the written files in the list of files to be executed, calls the SQL service, and executes the SQL files identified as syncable to complete data synchronization with the target end. The present invention creates multiple threads, each of which reads different content blocks in the transaction log according to a specific algorithm, and each thread performs parallel parsing of the content blocks and writes the SQL files created by each thread in parallel, thereby doubling the parsing efficiency and significantly improving data synchronization efficiency.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method for synchronous batch warehousing of multi-threaded data between databases. Background Art

[0002] The main implementation steps of existing database synchronization technology are: reading the source transaction log, sending it to the message queue, reading it one by one from the message queue, parsing the message content, assembling it into SQL statements according to the SQL syntax rules of the target side, connecting to the target database to execute SQL statements one by one, and disconnecting the target side link.

[0003] However, with the aforementioned synchronization technology, the source database sends transaction logs to a message queue. The synchronization tool then reads the messages, parses the message content, and assembles SQL statements according to the target database's syntax. Once SQL statements are assembled, the tool directly connects to the target database and executes the SQL statements. After execution, the connection to the target database is closed. During this process, transaction logs are generated quickly in the source database, but reading and parsing the logs is time-consuming, creating a bottleneck in data synchronization speed and preventing further improvements in data synchronization efficiency. Summary of the Invention

[0004] The technical problem to be solved by the present invention is how to further improve the synchronization efficiency between databases. In view of this, the present invention provides a multi-threaded data synchronization batch warehousing method between databases.

[0005] The technical solution adopted by the present invention is a method for synchronous batch storage of multi-threaded data between databases, comprising:

[0006] Step S1: creating at least two child threads for reading transaction logs based on the server's performance;

[0007] Step S2: Each of the sub-threads sequentially reads a configuration file of the source database transaction log, and based on the configuration files read by different sub-threads, each of the sub-threads reads different content blocks in the database transaction log in parallel;

[0008] Step S3, based on the content blocks read by each of the sub-threads, and according to the SQL rules of the target database, SQL execution statements are generated in parallel;

[0009] Step S4: Each of the child threads creates an SQL file in parallel and writes the file name of the SQL file into the sequence of files to be executed, wherein the SQL file is correspondingly configured with a flag bit, and the flag bit can be configured with different values ​​to represent different file states of the SQL file, and the file states include writable, written, and synchronization completed;

[0010] Step S5: Each sub-thread writes the generated SQL execution statement into the corresponding SQL file in the current to-be-executed list in parallel;

[0011] In step S6, the main thread sequentially reads the SQL files in the to-be-executed list. When the file status of the SQL file is written, the SQL service of the target database is called to execute the SQL files identified as syncable to complete data synchronization to the target.

[0012] In one embodiment, step S2 includes:

[0013] The child threads sequentially obtain the latest read position in the current configuration file;

[0014] Based on a preconfigured read block size, reading block contents of the configuration file in parallel starting from the latest read position;

[0015] Based on the block content read by the child threads, the latest read position of the configuration file is updated; wherein, when one of the child threads reads the configuration file, the configuration file is locked, that is, the other child threads cannot read or write the configuration file.

[0016] In one embodiment, in step S3, generating an SQL execution statement according to the SQL rules of the target database includes:

[0017] Based on the SQL rules of the target database, the parsed content of the transaction log is replaced and / or supplemented to generate standard SQL execution statements.

[0018] In one embodiment, in step S4, the file name of the created SQL file is constructed according to a preset format, wherein the preset format includes the source library number, the source library transaction log file name, the transaction log content start and end numbers and the flag bit, and the file status represented by the flag bit is writable.

[0019] In one embodiment, in step S5, after writing the generated SQL execution statement into the created SQL file in the current to-be-executed list, the method further includes:

[0020] The flag of the SQL file is modified so that the file status represented by it is changed from writable to written.

[0021] In one embodiment, step S6 includes:

[0022] The main thread reads the file names in the SQL files in the to-be-executed list in sequence, reading one file name at a time, and obtains the SQL file name with the same name in the sqlfile directory;

[0023] When the flag file status of the obtained SQL file name is written, the SQL service of the target database is called to execute the SQL file identified as syncable to complete data synchronization to the target end.

[0024] In one embodiment, the method further includes: updating the status of the SQL file to synchronization completed based on the flag bit after synchronization is completed, and regularly clearing the SQL file after synchronization is completed based on the flag bit.

[0025] Another aspect of the present invention provides an electronic device, comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor. When the computer program is executed by the processor, the steps of the method for batch warehousing of multi-threaded data synchronization between databases as described in any one of the above items are implemented.

[0026] Another aspect of the present invention further provides a computer storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method for multi-threaded data synchronization and batch warehousing between databases as described in any one of the above items.

[0027] Compared with the prior art, the present invention has at least the following advantages:

[0028] The method provided by the present invention creates multiple threads, each of which reads different content blocks in the transaction log according to a certain algorithm, parses the content blocks in parallel, and writes SQL files created by each thread in parallel, thereby doubling the parsing efficiency and significantly improving data synchronization efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Figure 1 Flowchart of a method for batch warehousing of multi-threaded data synchronization between databases according to an embodiment of the present invention;

[0030] Figure 2 A schematic diagram of a basic flow chart of an application example according to an embodiment of the present invention;

[0031] Figure 3 A detailed flowchart of an application example according to an embodiment of the present invention;

[0032] Figure 4 FIG. 1 is a schematic diagram of an electronic device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0033] In order to further illustrate the technical means and effects adopted by the present invention to achieve the predetermined purpose, the present invention is described in detail below with reference to the accompanying drawings and preferred embodiments.

[0034] In the accompanying drawings, the thickness, size and shape of objects have been slightly exaggerated for ease of explanation. The accompanying drawings are only examples and are not drawn strictly to scale.

[0035] It should also be understood that the terms "comprises," "including," "having," "includes," and / or "comprising," when used in this specification, indicate the presence of the stated features, integers, steps, operations, elements, and / or parts, but do not preclude the presence or addition of one or more other features, integers, steps, operations, elements, parts, and / or combinations thereof. In addition, when expressions such as "at least one of..." appear after a list of listed features, they modify the entire listed features rather than modifying the individual elements in the list. In addition, when describing embodiments of the present application, "may" is used to mean "one or more embodiments of the present application." And, the term "exemplary" is intended to refer to an example or illustration.

[0036] As used herein, the terms "substantially," "approximately," and similar terms are used as terms of approximation, not degree, and are intended to account for the inherent variations in measurements or calculations that would be recognized by those having ordinary skill in the art.

[0037] Unless otherwise defined, all terms used herein (including technical and scientific terms) have the same meaning as commonly understood by those skilled in the art to which this application belongs. It should also be understood that terms (such as those defined in commonly used dictionaries) should be interpreted as having a meaning consistent with their meaning in the context of the relevant technology and will not be interpreted in an idealized or overly formal sense unless expressly defined as such herein.

[0038] It should be noted that, in the absence of conflict, the embodiments and features of the embodiments in this application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0039] The description of the method flow in the specification and the steps in the flowcharts in the drawings of the specification do not necessarily need to be strictly executed according to the step numbers. The method steps may be executed in a different order. Furthermore, some steps may be omitted, multiple steps may be combined into one step, and / or one step may be decomposed into multiple steps.

[0040] The first embodiment of the present invention is a method for synchronizing batch storage of multi-threaded data between databases, such as Figure 1 As shown, the following specific steps are included:

[0041] Step S1: creating at least two child threads for reading transaction logs based on the server's performance;

[0042] Step S2: Each sub-thread sequentially reads a configuration file of the source database transaction log, and based on the configuration files read by different sub-threads, each sub-thread reads different content blocks in the database transaction log in parallel;

[0043] Step S3, based on the content blocks read by each of the sub-threads, and according to the SQL rules of the target database, SQL execution statements are generated in parallel;

[0044] Step S4: Each of the child threads creates an SQL file in parallel and writes the file name of the SQL file into the sequence of files to be executed, wherein the SQL file is correspondingly configured with a flag bit, and the flag bit can be configured with different values ​​to represent different file states of the SQL file, and the file states include writable, written, and synchronization completed;

[0045] Step S5: Each sub-thread writes the generated SQL execution statement into the corresponding SQL file in the current to-be-executed list in parallel;

[0046] In step S6, the main thread sequentially reads the SQL files in the to-be-executed list. When the file status of the SQL file is written, the SQL service of the target database is called to execute the SQL files identified as syncable to complete data synchronization to the target.

[0047] In this embodiment, step S2 may further include:

[0048] Step S201: The child threads sequentially obtain the latest read positions in the current configuration file;

[0049] Step S202, based on a pre-configured read block size, reading block contents of the configuration file in parallel starting from the latest read position;

[0050] Step S203: updating the latest reading position of the configuration file based on the block content read by the child thread.

[0051] It should be noted that when one of the child threads reads the configuration file, the configuration file is locked, that is, the other child threads cannot read or write the configuration file.

[0052] In one embodiment, in step S3, generating SQL execution statements according to the SQL rules of the target database specifically includes: generating standard SQL execution statements by replacing and / or supplementing the parsed content of the transaction log based on the SQL rules of the target database.

[0053] In this embodiment, the file name of the SQL file created in step S4 is constructed according to a preset format, wherein the preset format includes the source library number, the source library transaction log file name, the transaction log content start and end numbers and the flag bit, and the file status represented by the flag bit is writable.

[0054] In this embodiment, after writing the generated SQL execution statement into the created SQL file in the current to-be-executed list in step S5, the method further includes: modifying the flag of the SQL file so that the file status represented by it is changed from writable to written.

[0055] In this embodiment, step S6 includes:

[0056] Step S601: The main thread reads the file names of the SQL files in the to-be-executed list in sequence, reading one file name at a time, and obtains the SQL file name with the same name in the sqlfile directory;

[0057] Step S602: When the flag file status of the acquired SQL file name is written, the SQL service of the target database is called to execute the SQL file identified as synchronizable to complete data synchronization to the target.

[0058] In this embodiment, after the synchronization is completed, the status of the SQL file may be updated to synchronization completed based on the flag bit, and the SQL file after synchronization is completed may be regularly cleared based on the flag bit.

[0059] In summary, compared with the prior art, this embodiment has at least the following advantages:

[0060] 1) This embodiment uses multi-threaded parallel reading of transaction logs. As the number of threads increases, the efficiency of reading transaction logs increases exponentially.

[0061] 2) In this embodiment, each thread parses the content it reads at the same time, and the parsing efficiency is also doubled;

[0062] 3) In this embodiment, multiple threads write SQL files in parallel. Each thread manages its own created SQL file, avoiding file resource competition. The writing efficiency will be multiplied as the number of threads increases.

[0063] 4) Compared with the existing multi-threaded technology implementation, this embodiment is more direct in reading transaction logs, generating SQL file names, and executing SQL file processing logic, and has improved efficiency and stability.

[0064] The second embodiment of the present invention is an application example based on the above embodiment. Figure 2 as well as Figure 3 As shown, the specific process steps are as follows:

[0065] Step 1: Create a certain number of child threads based on server performance. The default number is 3.

[0066] Step 2: Each thread reads the value of the "latest read position" from the configuration file. When a thread reads the configuration file, the file is locked, preventing other threads from reading or writing.

[0067] Step 3: Calculate the starting and ending rows of the content block to be read. Use "Latest Read Position" + 1 as the starting number of the content block to be read, and "Latest Read Position" + 5000 as the ending number of the content block to be read. Use the calculated ending number to update the value of "Latest Read Position" in the configuration file.

[0068] Step 4, read the content block between the start and end numbers calculated in step 3;

[0069] In step 5, when each thread parses the content block it has read, there is no need for threads to wait in line. Multiple threads perform parsing in parallel and obtain an executable SQL statement through replacement, supplementation, etc. according to the SQL rules of the target database;

[0070] Step 6: Multiple threads create their own SQL files in parallel without waiting in line. The file name is generated according to a certain rule, the flag bit (the last bit of the file name is the flag bit) is set to "0", and the file name is written to the "file sequence to be executed" and sorted according to the file name;

[0071] Step 7: Write the SQL statement obtained after parsing in step 5 into the SQL file created in step 6. When writing, each thread does not need to wait in line and executes the SQL file created by itself in parallel. After writing is completed, change the flag bit of the SQL file from "0" to "1";

[0072] Step 8: The main thread reads file names from the "file sequence to be executed" one at a time. Based on the read file names, it obtains the SQL file name with the same name in the sqlfile directory (ignoring the difference in the file name flag).

[0073] Step 9: When the file name flag of the SQL file obtained in step 8 is "1", call the SQL service of the target database and execute the SQL file with the status bit "1" in step 7 to complete data synchronization to the target end.

[0074] Step 10: When the task of executing the SQL file in step 9 is completed, the flag of the SQL file that has been executed is changed to "2".

[0075] Step 11: Regularly clean up SQL files with the flag set to "2" according to the settings of the synchronization platform.

[0076] Each sub-thread executes steps 2 to 7 in parallel and in a loop.

[0077] It should be noted that when creating an SQL file, the SQL file name must be composed in the following format:

[0078] Source database number + source database transaction log file name + transaction log content start_end number + flag.sql

[0079] For ease of understanding, the components are described as follows:

[0080] Source database number: represents the number of the database to be synchronized as agreed upon in the present invention, that is, generated by the synchronization platform.

[0081] Source database transaction log file name: The database transaction log consists of multiple files. Extract the file name (including the suffix) of the transaction log file being synchronized as the value of this part.

[0082] Transaction log content start and end numbering: Multiple child threads read the log file content in parallel. Each thread starts reading from the line immediately following the most recently read position. By default, 5000 lines are read at a time, with the end number being the start number + 5000. If the calculated end number is greater than the total number of log file lines, the last line number is used. The start and end numbers are separated by a slash (_).

[0083] Flag: This part is mainly used to distinguish the status of the SQL file: 0 represents writing, 1 represents writing completed, and 2 represents execution completed. For example: DB01_0000_132_962_0.sql

[0084] Sorting rules for SQL file names in the "File sequence to be executed":

[0085] 1) Search for multiple file name records in the "File Sequence to be Executed" that match the "source database number + source database transaction log file name" of the newly generated SQL file name;

[0086] 2) From the multiple file name records obtained in 1), find a unique file name record whose "end number" + 1 is equal to the "start number" of the newly generated SQL file name.

[0087] 3) Insert the newly generated SQL file name after the unique file name record obtained in 2).

[0088] A third embodiment of the present invention is an electronic device, such as Figure 4 As shown, it can be understood as a physical device, including a processor and a memory storing processor-executable instructions. When the instructions are executed by the processor, the following operations are performed:

[0089] Step S1: creating at least two child threads for reading transaction logs based on the server's performance;

[0090] Step S2: Each sub-thread sequentially reads a configuration file of the source database transaction log, and based on the configuration files read by different sub-threads, each sub-thread reads different content blocks in the database transaction log in parallel;

[0091] Step S3, based on the content blocks read by each of the sub-threads, and according to the SQL rules of the target database, SQL execution statements are generated in parallel;

[0092] Step S4: Each of the child threads creates an SQL file in parallel and writes the file name of the SQL file into the sequence of files to be executed, wherein the SQL file is correspondingly configured with a flag bit, and the flag bit can be configured with different values ​​to represent different file states of the SQL file, and the file states include writable, written, and synchronization completed;

[0093] Step S5: Each sub-thread writes the generated SQL execution statement into the corresponding SQL file in the current to-be-executed list in parallel;

[0094] In step S6, the main thread sequentially reads the SQL files in the to-be-executed list. When the file status of the SQL file is written, the SQL service of the target database is called to execute the SQL files identified as syncable to complete data synchronization to the target.

[0095] The fourth embodiment of the present invention provides a method for batch warehousing of multi-threaded data synchronization between databases. The process is the same as that of the first, second, or third embodiments. The difference is that, in terms of engineering implementation, this embodiment can be implemented by means of software plus a necessary general-purpose hardware platform. Of course, it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the method of the present invention can be embodied in the form of a computer software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for enabling a device to execute the method described in the embodiment of the present invention.

[0096] Through the description of the specific implementation methods, a deeper and more specific understanding of the technical means and effects adopted by the present invention to achieve the intended purpose should be obtained. However, the accompanying drawings are only for reference and illustration purposes and are not intended to limit the present invention.

Claims

1. A method for synchronizing batch data storage between databases through multiple threads, characterized in that: include: Step S1: creating at least two child threads for reading transaction logs based on the server's performance; Step S2: Each of the sub-threads sequentially reads a configuration file of the source database transaction log, and based on the configuration files read by different sub-threads, each of the sub-threads reads different content blocks in the database transaction log in parallel; Step S3, based on the content blocks read by each of the sub-threads, and according to the SQL rules of the target database, SQL execution statements are generated in parallel; Step S4: Each of the child threads creates an SQL file in parallel and writes the file name of the SQL file into the sequence of files to be executed, wherein the SQL file is correspondingly configured with a flag bit, and the flag bit can be configured with different values ​​to represent different file states of the SQL file, and the file states include writable, written, and synchronization completed; Step S5: Each sub-thread writes the generated SQL execution statement into the corresponding SQL file in the current to-be-executed list in parallel; In step S6, the main thread sequentially reads the SQL files in the to-be-executed list. When the file status of the SQL file is written, the SQL service of the target database is called to execute the SQL files identified as syncable to complete data synchronization to the target.

2. The method for synchronizing batch data in databases through multiple threads according to claim 1, wherein: The step S2 comprises: The child threads sequentially obtain the latest read position in the current configuration file; Based on a preconfigured read block size, reading block contents of the configuration file in parallel starting from the latest read position; Based on the block content read by the child threads, the latest read position of the configuration file is updated; wherein, when one of the child threads reads the configuration file, the configuration file is locked, that is, the other child threads cannot read or write the configuration file.

3. The method for synchronizing batch data between databases using multiple threads according to claim 1, wherein: In step S3, generating an SQL execution statement according to the SQL rules of the target database includes: Based on the SQL rules of the target database, the parsed content of the transaction log is replaced and / or supplemented to generate standard SQL execution statements.

4. The method for synchronous batch storage of multi-threaded data between databases according to claim 1, characterized in that: In step S4, the file name of the created SQL file is constructed according to a preset format, wherein the preset format includes the source library number, the source library transaction log file name, the transaction log content start and end numbers and the flag bit, and the file status represented by the flag bit is writable.

5. The method for synchronizing batch data between databases using multiple threads according to claim 4, wherein: In step S5, after writing the generated SQL execution statement into the corresponding SQL file in the current to-be-executed list, the method further includes: The flag of the SQL file is modified so that the file status represented by it is changed from writable to written.

6. The method for synchronous batch storage of multi-threaded data between databases according to claim 1, characterized in that: The step S6 comprises: The main thread reads the file names in the SQL files in the to-be-executed list in sequence, reading one file name at a time, and obtains the SQL file name with the same name in the sqlfile directory; When the flag file status of the obtained SQL file name is written, the SQL service of the target database is called to execute the SQL file identified as syncable to complete data synchronization to the target end.

7. The method for synchronizing batch data between databases through multi-threading as claimed in claim 1, characterized in that: The method further includes: updating the status of the SQL file to synchronization completion based on the flag bit after synchronization is completed, and regularly clearing the SQL file after synchronization is completed based on the flag bit.

8. An electronic device, characterized in that: The method comprises a processor and a memory, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the steps of the method for batch warehousing of multi-threaded data synchronization between databases as described in any one of claims 1 to 7 are implemented.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, which, when executed by a processor, implements the steps of the method for batch warehousing of multi-threaded data synchronization between databases according to any one of claims 1 to 7.

Images