Method and apparatus for processing transaction log

Abstract

Embodiments of the present specification provide a method and device for processing a transaction log. The method comprises: executing a transaction for a distributed database; triggering generation of a third structured query language (SQL) included in the SQL of the transaction corresponding to a change operation; changing the change operation corresponding to the third SQL into a first operation and a second operation to move data in a first partition table to a second partition table; the first operation is a changed delete operation, and the second operation is a changed insert operation; carrying a first sequence number corresponding to the first operation in a first log corresponding to the first operation, and carrying a second sequence number in a second log corresponding to the second operation, the first sequence number being earlier than the second sequence number; and writing the first log carrying the first sequence number and the second log carrying the second sequence number into two log streams in the distributed database respectively. According to the present application, the order between each operation in the transaction can be obtained according to the logs in the log stream, so that the transaction data of the transaction can be correctly obtained.

Description

Technical Field

[0001] This specification relates to the field of computer technology, and more particularly to a method and apparatus for processing transaction logs. Background Technology

[0002] Databases record every insert, update, and delete operation in a log. To ensure the atomicity and durability of data operations in the database system, a transaction's log is persisted to a log stream. Subsequently, by parsing the log, the complete data change history of the database can be obtained, thereby achieving data synchronization. For traditional single-machine databases, such as MySQL, there is only one global log stream; therefore, the transaction commit history can be reconstructed by sequentially obtaining and parsing the log stream.

[0003] Currently, distributed databases that employ multiple log streams, such as OceanBase, have emerged. In a distributed database using multiple log streams, all logs corresponding to a transaction are written to multiple log streams within the distributed database, and these different log streams may be distributed across multiple machine nodes.

[0004] In many scenarios, the operations within a transaction have a specific order. Therefore, when obtaining the transaction data, this order must be considered to ensure correct data synchronization. However, there is currently no effective solution. Therefore, how to determine the order of operations within a transaction from the log stream, and then correctly obtain the transaction data based on this order, is a problem that urgently needs to be solved. Summary of the Invention

[0005] This specification provides one or more embodiments of a method and apparatus for processing transaction logs, which can obtain the sequential order of various operations in a transaction based on the logs in the log stream, thereby correctly obtaining the transaction data of the transaction.

[0006] Based on the first aspect, a method for processing transaction logs is proposed, wherein a transaction is a sequence of database operations that access and / or manipulate data; the method is applied to distributed databases employing multiple log streams.

[0007] One data table in the distributed database includes a first partition table and a second partition table;

[0008] The method includes:

[0009] A transaction is executed for the distributed database; wherein the Structured Query Language (SQL) that triggers the generation of the transaction includes a third SQL statement corresponding to the change operation;

[0010] When executing the transaction, the change operation corresponding to the third SQL is changed to a first operation and a second operation, so as to move the data in the first partition table to the second partition table; wherein, the first operation is a deletion operation and the second operation is an insert operation.

[0011] Generate the first log corresponding to the first operation and the second log corresponding to the second operation;

[0012] The first log corresponding to the first operation carries the first sequence number corresponding to the first operation, and the second log corresponding to the second operation carries the second sequence number corresponding to the second operation, with the first sequence number preceding the second sequence number;

[0013] The first log, carrying the first sequence number, and the second log, carrying the second sequence number, are written into two log streams in the distributed database, respectively.

[0014] The method further includes:

[0015] A transaction is executed for the distributed database; multiple SQL statements trigger the generation of the transaction; each SQL statement corresponds to at least one operation in the transaction; a log is generated for each operation.

[0016] For the multiple SQL statements that triggered the generation of the transaction, a unique statement sequence number is generated for each of the multiple SQL statements within the transaction, based on the execution order among them.

[0017] For each SQL statement and all operations within that transaction, the statement sequence number corresponding to that SQL statement is carried in all logs corresponding to all operations.

[0018] For all operations corresponding to the multiple SQL statements that triggered the generation of the transaction, all logs corresponding to all operations, each carrying a statement sequence number, are written to multiple log streams of the distributed database; wherein, the logs corresponding to all operations of a single SQL statement are written to different log streams.

[0019] The execution of the transaction further includes: first scheduling the execution of the first operation, and then scheduling the execution of the second operation;

[0020] Setting the first sequence number first and the second sequence number later includes: setting the first sequence number according to the time of executing the first operation, and setting the second sequence number according to the time of executing the second operation.

[0021] The first partition table and the second partition table are located on the same machine node;

[0022] Setting the first sequence number according to the time of executing the first operation includes: using the local time of the machine node when executing the first operation as the first sequence number;

[0023] Setting the second sequence number based on the time of performing the second operation includes: using the local time of the machine node when performing the second operation as the second sequence number.

[0024] The first partition table and the second partition table are located in different first machine nodes and second machine nodes, respectively.

[0025] Setting the first sequence number according to the time of executing the first operation includes: using the local time of the first machine node when executing the first operation as the first sequence number;

[0026] The method further includes: sending the local time of the first machine node when performing the first operation to the second machine node;

[0027] Setting the second sequence number based on the time of performing the second operation includes:

[0028] Based on the local time received by the second machine node from the first machine node, the logical time in the second machine node is increased; and

[0029] The logic time after the second machine node pushes up when performing the second operation is used as the second sequence number.

[0030] Among them, each SQL that triggers the generation of each transaction includes multiple third SQL statements, and the first operation and the second operation modified according to each third SQL statement are used to move data from one partition table to another partition table;

[0031] The step of scheduling the execution of the first operation before scheduling the execution of the second operation includes: scheduling the first machine node to execute all the first operations resulting from all the changes made by the third SQL in this node; and then scheduling the second machine node to execute all the second operations resulting from all the changes made by the third SQL in this node.

[0032] Accordingly, sending the local time of the first machine node when performing the first operation to the second machine node includes:

[0033] Obtain all local times when the first machine node performs all the first operations resulting from the changes;

[0034] Select the largest local time from all these local times;

[0035] The largest selected local time is sent to the second machine node.

[0036] According to the second aspect, a method for processing transaction logs is provided, characterized in that a transaction is a sequence of database operations that access and / or manipulate data; the method is applied to a distributed database employing multiple log streams;

[0037] One data table in the distributed database includes a first partition table and a second partition table;

[0038] The method includes:

[0039] Obtain at least two log streams from the distributed database;

[0040] Obtain a first log and a second log from any two log streams; wherein the first log carries a first sequence number and the second log carries a second sequence number, with the first sequence number preceding the second sequence number;

[0041] Based on the first sequence number carried in the first log and the second sequence number carried in the second log, the execution order between the first operation corresponding to the first log and the second operation corresponding to the second log is obtained; wherein, the first operation is a delete operation modified from a modification operation corresponding to the third structured query language SQL, and the second operation is an insert operation modified from a modification operation corresponding to the third SQL; the third SQL is used to move data from the first partition table to the second partition table;

[0042] Based on the execution order of the first and second operations, the transaction data corresponding to the transactions to which the first and second operations belong is obtained.

[0043] The sequence number includes a time value.

[0044] According to a third aspect, an apparatus for processing transaction logs is provided, wherein a transaction is a sequence of database operations that access and / or manipulate data; the apparatus is applied to a distributed database employing multiple log streams; a data table in the distributed database includes a first partition table and a second partition table;

[0045] The device includes:

[0046] The transaction execution module is configured to execute a transaction for the distributed database; wherein the structured query language (SQL) that triggers the generation of the transaction includes a third SQL corresponding to the change operation; when executing the transaction, the change operation corresponding to the third SQL is changed to a first operation and a second operation, so as to move the data in the first partition table to the second partition table; wherein the first operation is a deletion operation resulting from the change, and the second operation is an insertion operation resulting from the change;

[0047] The sequence number determination module is configured to determine a first sequence number corresponding to the first operation and a second sequence number corresponding to the second operation, and set the first sequence number first and the second sequence number last.

[0048] The log generation module is configured to generate a first log corresponding to a first operation and a second log corresponding to a second operation; the first log corresponding to the first operation carries a first sequence number corresponding to the first operation, and the second log corresponding to the second operation carries a second sequence number corresponding to the second operation;

[0049] The log writing module is configured to write the first log with the first sequence number and the second log with the second sequence number into two log streams in the distributed database, respectively.

[0050] According to the fourth aspect, an apparatus for processing transaction logs is provided, wherein a transaction is a sequence of database operations that access and / or manipulate data; the apparatus is applied to a distributed database employing multiple log streams; a data table in the distributed database includes a first partition table and a second partition table;

[0051] The device includes:

[0052] The log stream acquisition module is configured to acquire at least two log streams from the distributed database.

[0053] The log acquisition module is configured to obtain a first log and a second log from any two log streams; wherein the first log carries a first sequence number and the second log carries a second sequence number, with the first sequence number preceding the second sequence number;

[0054] The operation order determination module is configured to determine the execution order between the first operation corresponding to the first log and the second operation corresponding to the second log based on the first sequence number carried in the first log and the second sequence number carried in the second log; wherein, the first operation is a delete operation modified from the change operation corresponding to the third structured query language SQL, and the second operation is an insert operation modified from the change operation corresponding to the third SQL; the third SQL is used to move data from the first partition table to the second partition table;

[0055] The synchronization processing module is configured to obtain the transaction data corresponding to the transactions to which the first operation and the second operation belong, based on the execution order between the first operation and the second operation.

[0056] According to a fifth aspect, embodiments of this specification provide a computer-readable storage medium having a computer program stored thereon, which, when executed in a computer, causes the computer to perform the method described above.

[0057] According to a sixth aspect, embodiments of this specification provide a computing device including a memory and a processor, wherein the memory stores executable code, and the processor executes the executable code to implement the method described above.

[0058] As can be seen from the above technical solutions, the combination of one or more embodiments of this specification has at least the following advantages:

[0059] 1. The logs corresponding to the operations carry sequence numbers. The order of these sequence numbers corresponds to the execution order of the operations in each log. In other words, even if the logs of the various operations of a transaction are written to different log streams, the execution order of the operations in each log can still be determined by the sequence numbers. This allows the transaction data to be assembled in this order, ensuring the correctness of the transaction data and guaranteeing the correctness of data synchronization.

[0060] 2. For situations where there are sequential requirements among multiple SQL statements within a transaction, the statement sequence number can be carried in the log. This ensures that the order of operations of the SQL statements executed earlier in a transaction takes precedence over the order of operations of the SQL statements executed later in the same transaction. As a result, the transaction data can be obtained based on the correct execution order of the SQL statements, ensuring the correctness of data synchronization.

[0061] 3. For situations where there is a sequential requirement between deletion and insertion operations resulting from a change operation, the operation sequence number, such as a time value, can be carried in the log to ensure that the order of the deletion operations precedes the order of the insertion operations. This allows the transaction data to be obtained based on the correct operation execution order, ensuring the correctness of data synchronization.

[0062] 4. When the two partitioned tables are located on different machine nodes, local time and logical time can be used to push up the time value in order to obtain the sequence number carried in the log. This ensures that even when the deletion and insertion operations are performed on different machine nodes, the time value (sequence number) of the deletion operation will precede the time value (sequence number) of the insertion operation.

[0063] 5. All deletion operations that have been changed on a single machine node can be executed centrally, so that only one of the maximum time values ​​is sent to another machine node, instead of sending multiple local times for executing the multiple deletion operations, thus saving system processing resources.

[0064] 6. Each distributed transaction supports output according to strict statement order, and the overall external performance is consistent with that of a single-machine database, with good compatibility and understandability. Attached Figure Description

[0065] 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 some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0066] Figure 1 An exemplary system architecture diagram is shown that can be applied to embodiments of this specification;

[0067] Figure 2 A flowchart illustrating a method for processing transaction logs according to one embodiment of this specification;

[0068] Figure 3 This is a schematic diagram of the SQL and its corresponding operations and logs in the embodiments of this specification;

[0069] Figure 4 This is a schematic diagram illustrating how each log is written to multiple log streams in the embodiments of this specification;

[0070] Figure 5 This is a schematic diagram illustrating the writing of logs carrying statement sequence numbers to a log stream in an embodiment of this specification;

[0071] Figure 6 This is a schematic diagram illustrating the writing of the log of a deletion operation carrying an operation number and the log of an insertion operation carrying an operation number into a log stream in an embodiment of this specification.

[0072] Figure 7 A flowchart illustrating a method for processing transaction logs provided in another embodiment of this specification;

[0073] Figure 8 This is a structural diagram of an apparatus for processing transaction logs according to one embodiment of this specification.

[0074] Figure 9 This is a structural diagram of an apparatus for processing transaction logs, provided for another embodiment of this specification. Detailed Implementation

[0075] First, the concept of transactions involved in the embodiments of this specification will be explained.

[0076] A transaction is a sequence of database operations that access and / or manipulate data. In computer terminology, it refers to a unit of program execution that accesses and may modify data items in a database. A transaction consists of all operations performed from its start to its end; all these operations must complete successfully, otherwise all changes made in each operation will be undone. For example, a money transfer transaction might consist of increasing the balance in one account and decreasing the balance in another account.

[0077] A transaction often corresponds to multiple operations, and these operations have a specific order. For example, within a transaction, operation 11, which deducts 100 yuan from account A, can only be executed after operation 12, which adds 100 yuan to account B. Logs corresponding to different operations may be written to different log streams. However, in a distributed database with multiple log streams, the order in which logs are written to the log streams is random, and there is no global time order among the multiple log streams. For instance, log 12 of operation 12 might be written to log stream 12 in machine node 12 first, and then log 11 of operation 11 might be written to log stream 11 in machine node 11. Subsequently, during data synchronization, log 11 of operation 11 can be retrieved from log stream 11, and log 12 of operation 12 can be retrieved from log stream 12, but the order of operations corresponding to log 11 and log 12 cannot be determined. Therefore, the synchronization result might be that, within the transaction, operation 12 corresponding to log 12 occurs first, and operation 11 corresponding to log 11 occurs later, leading to an error.

[0078] The solution provided in this specification will now be described with reference to the accompanying drawings.

[0079] The terminology used in the embodiments of this invention is for the purpose of describing particular embodiments only and is not intended to limit the invention. The singular forms “a,” “the,” and “the” as used in the embodiments of this invention and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise.

[0080] Figure 1 An exemplary system architecture applicable to embodiments of this specification is illustrated. The system primarily includes: a transaction executor, a distributed database, and a data synchronization device.

[0081] Distributed databases employ multiple log streams. Furthermore, multiple machine nodes can be used as instances to run the distributed database software.

[0082] A transaction executor is a device that operates on a distributed database, such as performing insert, delete, and update operations. It writes all logs corresponding to each operation in a transaction to at least two log streams in the distributed database and carries a sequence number in each log using the transaction log processing method provided in the embodiments of this specification.

[0083] The data synchronization device can pull log streams from a distributed database and process them using the transaction log processing method provided in the embodiments of this specification to obtain transaction data within the transaction corresponding to the correct operation order, thereby synchronizing data based on the dependencies or order between various operations in the transaction.

[0084] Transaction executors, data synchronization devices, and distributed databases can interact via a network, which can include various connection types, such as wired, wireless communication links, or fiber optic cables.

[0085] The transaction executor or data synchronization device can be a single server, a server cluster consisting of multiple servers, or a cloud server. A cloud server, also known as a cloud computing server or cloud host, is a host product within the cloud computing service system, designed to address the management difficulties and weak service scalability inherent in traditional physical hosts and Virtual Private Servers (VPs). Alternatively, it can be a computer terminal with strong computing capabilities.

[0086] It should be understood that Figure 1 The number of transaction executors, distributed databases, machine nodes, and data synchronization devices shown is merely illustrative. Depending on implementation needs, any number of distributed databases, machine nodes, and data synchronization devices can be included.

[0087] As can be seen, the embodiments in this specification involve a method for processing transaction logs executed by a transaction executor, including: performing a transaction operation on distributed data and writing the logs of the transaction to multiple log streams. Simultaneously, a method for processing transaction logs executed by a data synchronization device also relates to a data synchronization device method, including: obtaining the complete data change history of the database based on the logs carried in the log streams, thereby achieving data synchronization. These will be described below through different embodiments.

[0088] Figure 2This is a flowchart illustrating a method for processing transaction logs provided in an embodiment of this specification. The execution entity of this method is the aforementioned transaction executor, and this method is applied to a distributed database employing multiple log streams. It is understood that this method can also be executed by any device, equipment, platform, or device cluster with computing and processing capabilities. See also... Figure 2 The method includes:

[0089] Step 201: Execute a transaction for the distributed database; wherein the transaction includes a first operation and a second operation.

[0090] Step 203: Determine the first sequence number and the second sequence number; wherein the order between the first sequence number and the second sequence number is the same as the execution order between the first operation and the second operation.

[0091] Step 205: Generate the first log corresponding to the first operation and the second log corresponding to the second operation.

[0092] Step 207: Carry the first sequence number in the first log and the second sequence number in the second log.

[0093] Step 209: Write the first log and the second log into the two log streams of the distributed database respectively.

[0094] It can be seen that in the above Figure 2 In the process shown, the logs corresponding to the operations carry sequence numbers. Because the order of the sequence numbers is the same as the execution order of the operations corresponding to each log, even if the logs of the various operations of a transaction are written to different log streams, the execution order of the operations corresponding to the logs can still be determined based on the sequence numbers. In this way, the transaction data can be assembled according to this order, so as to correctly obtain the transaction data and ensure the correctness of data synchronization.

[0095] The following is about Figure 2 Each step shown is explained separately.

[0096] First, for step 201: Execute a transaction for the distributed database, wherein the transaction includes a first operation and a second operation.

[0097] As mentioned earlier, a transaction is a sequence of database operations that access and / or manipulate data. Transactions are triggered by SQL (Structured Query Language). SQL operations can include: data insertion, querying, modification, or deletion; database schema creation and modification; and data access control. When an SQL statement is initiated against a distributed database, it triggers the creation of a transaction within the distributed database; one SQL statement corresponds to one or more operations.

[0098] In one embodiment of this specification, the method for processing transaction logs can be applied to the following two business scenarios:

[0099] Business Scenario A: There are requirements for the execution order of the SQL statements corresponding to a transaction, and data synchronization needs to be performed according to the execution order of the SQL statements.

[0100] Business Scenario B: The distributed database has enabled row movement. There is a requirement for the execution order of delete and insert operations resulting from update operations. Data synchronization needs to be performed according to the execution order of the delete and insert operations.

[0101] Each business scenario will be explained separately below.

[0102] For business scenario A: There are requirements for the execution order of the SQL statements corresponding to a transaction, and data synchronization needs to be performed according to the execution order of the SQL statements.

[0103] For example, in a distributed database, three SQL statements are initiated: SQL1, SQL2, and SQL3. These three SQL statements trigger the creation of a transaction tx1 in the distributed database. In practice, the execution order of these three SQL statements is SQL1, SQL2, and SQL3. Figure 3 As shown, for example, SQL1 corresponds to three operations in transaction tx1: operation 1 (e.g., deleting the first row of data in the data table), operation 2 (e.g., deleting the second row of data in the data table), and operation 3 (e.g., deleting the third row of data in the data table); SQL2 corresponds to three operations in transaction tx1: operation 4, operation 5, and operation 6; and SQL3 corresponds to three operations in transaction tx1: operation 7, operation 8, and operation 9.

[0104] like Figure 3As shown, in step 201, when executing transaction tx1, a log is generated for each of operations 1 to 9. For example, the log for operation 1 is recorded as ROW1, the log for operation 2 as ROW2, and so on, until the log for operation 9 is recorded as ROW9. All logs for transaction tx1 are often written to multiple log streams, as shown in [reference needed]. Figure 4 Assume that in the subsequent process, the logs of each operation in transaction tx1 will be written to three log streams LS1, LS2, and LS3. Specifically, log stream LS1 will write three rows of data, namely the three logs of the three operations: ROW1, ROW4, and ROW7; log stream LS2 will write the three logs of the three operations: ROW2, ROW5, and ROW8; and log stream LS3 will write the three logs of the three operations: ROW3, ​​ROW6, and ROW9.

[0105] During subsequent data synchronization, when aggregating all data from LS1, LS2, and LS3 for this distributed transaction tx1, it would be natural to output the data in the order of the participants' log stream rows. For example, the output sequence M for LS1, LS2, and LS3 could be: ROW1, ROW4, ROW7, ROW2, ROW5, ROW8, ROW3, ​​ROW6, ROW9. As mentioned earlier, the execution order of the SQL statements corresponding to transaction tx1 is: SQL1, SQL2, SQL3. This means that ROW1, ROW2, and ROW3 corresponding to SQL1 should be output first; then ROW4, ROW5, and ROW6 corresponding to SQL2; and finally, ROW7, ROW8, and ROW9 corresponding to SQL3. Therefore, if the above sequence M is output according to the order of the row data in LS1, LS2, and LS3, it will result in a failure to correspond to the execution order of the SQL statements in transaction tx1: SQL1, SQL2, SQL3, thus disrupting external consistency in downstream business systems.

[0106] As can be seen, data synchronization needs to be performed based on the execution order of the SQL statements. Therefore, the execution order information of the SQL statements needs to be included in the logs.

[0107] In business scenario A, the sequence number carried in the log is the SQL statement sequence number. Furthermore, the statement sequence number needs to be carried in the log for each operation. Therefore, in step 201, the first operation and the second operation can be any operation corresponding to different SQL statements.

[0108] Next, for business scenario B: The distributed database has enabled row movement. There is a requirement for the execution order between the delete and insert operations resulting from the update operation. Data synchronization needs to be performed according to the execution order of the delete and insert operations.

[0109] In a distributed database, a data table can include at least two partitioned tables. For example, sales data for a year might be stored in 12 partitioned tables, each storing sales data for one of the 12 months of the year. Different partitioned tables can be stored on the same machine node or on different machine nodes. In business scenario B, when row movement is enabled, a SQL statement needs to update a row in one of the partitioned tables, for example, the table corresponding to January. Specifically, it needs to move that row from the January partitioned table to the February partitioned table. However, during transaction execution, this update operation can be split into a delete operation and an insert operation. That is, first, the row in the January partitioned table is deleted, and then the row in the February partitioned table is inserted. As can be seen, there is a specific execution order between the delete and insert operations. If subsequent data synchronization does not follow this order, but instead executes the insert operations first and then the delete operations, it often leads to data synchronization errors. Therefore, it is necessary to include information about the execution order of the delete and insert operations in the log.

[0110] In business scenario B, the sequence number carried in the log is actually the operation sequence number. It is necessary to carry the operation sequence number in the log corresponding to the deleted operation and the operation sequence number in the log corresponding to the inserted operation. Therefore, in step 201, the first operation and the second operation are the deleted operation and the inserted operation, respectively.

[0111] Next, for steps 203 to 207: determine the first sequence number and determine the second sequence number; wherein, the order between the first sequence number and the second sequence number is the same as the execution order between the first operation and the second operation; generate the first log corresponding to the first operation and the second log corresponding to the second operation; carry the first sequence number in the first log and carry the second sequence number in the second log.

[0112] In step 203, the first sequence number is used to characterize the execution order of the first operation, and the second sequence number is used to characterize the execution order of the second operation. Therefore, in the subsequent step 207, the first sequence number will be carried in the first log corresponding to the first operation, and the second sequence number will be carried in the second log corresponding to the second operation.

[0113] First, let me explain the implementation process of step 203 when it corresponds to business scenario A.

[0114] When applied to business scenario A, the first operation is any operation corresponding to the first SQL, and the second operation is any operation corresponding to the second SQL; the execution order of the first operation and the second operation is equal to the execution order of the first SQL and the second SQL.

[0115] As mentioned earlier, a sequence number is actually the statement sequence number of the related SQL. The execution order of operations corresponding to different SQL statements is equal to the execution order of the SQL statements. For example, the execution order of SQL1 to SQL3 is: SQL1, SQL2, SQL3. Therefore, the operation order should be: operation 1 to operation 3, operation 4 to operation 6, operation 7 to operation 9.

[0116] Corresponding to business scenario A, one implementation process of step 203 includes:

[0117] Generate a unique first statement sequence number for the first SQL statement within the transaction, and generate a unique second statement sequence number for the second SQL statement within the same transaction; wherein the order of the first statement sequence number and the second statement sequence number is the same as the execution order of the first SQL statement and the second SQL statement; and

[0118] Use the first statement number as the first sequence number and the second statement number as the second sequence number.

[0119] Using steps 203 to 207 as an example, for transaction tx1, the statement number corresponding to SQL1 is generated, denoted as SQL_NO1; the statement number corresponding to SQL2 is generated, denoted as SQL_NO2; and the statement number corresponding to SQL3 is generated, denoted as SQL_NO3. The order of the statement numbers SQL_NO1, SQL_NO2, and SQL_NO3 is the same as the execution order of SQL1, SQL2, and SQL3.

[0120] Because the logs for each operation corresponding to SQL1 are ROW1, ROW2, and ROW3, ​​the sequence number carried in ROW1, ROW2, and ROW3 is SQL_NO1; because the logs for each operation corresponding to SQL2 are ROW4, ROW5, and ROW6, the sequence number carried in ROW4, ROW5, and ROW6 is SQL_NO2; because the logs for each operation corresponding to SQL3 are ROW7, ROW8, and ROW9, the sequence number carried in ROW7, ROW8, and ROW9 is SQL_NO3.

[0121] The following describes the implementation process of step 203 corresponding to business scenario B.

[0122] As mentioned earlier, when applied to business scenario B, a sequence number is actually the operation sequence number of the modified delete operation and the operation sequence number of the modified insert operation. One implementation process of step 203 includes executing step 2031: setting the first sequence number first and the second sequence number later.

[0123] In one embodiment of this specification, in business scenario B, when executing a transaction, the first operation can be scheduled to be executed first, and then the second operation can be scheduled to be executed. Thus, one implementation process of step 2031 includes:

[0124] Step 20311: Set the first sequence number according to the time of the execution of the first operation, for example, the first sequence number is recorded as Ltime_NO1; and set the second sequence number according to the time of the execution of the second operation, for example, the second sequence number is recorded as Ltime_NO2, so as to ensure that the first sequence number comes first and the second sequence number comes later.

[0125] In business scenario B, we can actually distinguish the following two situations:

[0126] Scenario 1: Partition movement is performed on the same machine node, meaning that the first partition table and the second partition table are located on the same machine node.

[0127] In scenario 1, local time can be used as the sequence number. In this case, a specific implementation of step 20311 includes: setting the first sequence number to the local time of the machine node when executing the first operation; and setting the second sequence number to the local time of the machine node when executing the second operation. For example, since scheduling already ensures that the first operation (i.e., the deleted operation) is scheduled first, followed by the second operation (i.e., the inserted operation), the local time of executing the first operation comes first, and the local time of executing the second operation comes later. Therefore, using the local time of executing the first operation as the first sequence number and the local time of executing the second operation as the second sequence number ensures that the first sequence number comes first, and the second sequence number comes later.

[0128] Scenario 2: The partition movement is carried out on different machine nodes, that is, the first partition table and the second partition table are located on different machine nodes.

[0129] In scenario 2, both local time and logical time can be used as the sequence number. In this case, a specific implementation of step 20311 includes:

[0130] Step 203111: Set the first sequence number to the local time of the first machine node when performing the first operation;

[0131] Step 203113: Send the local time of the first machine node when it performs the first operation to the second machine node;

[0132] Step 203115: The second machine node receives the local time from the first machine node when the first operation was performed, and increments the logical time in the second machine node based on the received local time from the first machine node; and

[0133] Step 203117: Set the second sequence number to the logic time after the second machine node performs the second operation.

[0134] For example, when machine node 1 performs the first operation locally, its local time is time value 1: 10:00 AM on September 1, 2022. It uses this time value 1 as the first sequence number and sends it to machine node 2. Machine node 2 then increments the logical time from time value 1 to 10:01 AM on September 1, 2022, and uses this incremented logical time as the second sequence number.

[0135] Regarding scenario 2, in one embodiment of this specification, the SQL that triggers the generation of each transaction includes multiple of the aforementioned third SQL statements. To further improve the efficiency of processing transaction logs, multiple delete operations executed on one machine node can be processed together to batch increase the local time, and multiple insert operations executed on another machine node can be processed together to batch and gradually increase the logical time, thereby saving processing resources. At this time,

[0136] First, schedule the first machine node to execute all the first operations resulting from all the third SQL changes in this node;

[0137] Then, the above step 203111 is executed. The specific implementation process includes: using the local time of the first machine node when executing each first operation as the first sequence number corresponding to the first operation.

[0138] Then, the above step 203113 is executed. The specific implementation process includes: obtaining all local times when the first machine node performs all the first operations that have been changed; selecting the largest local time from all the local times; and sending the selected largest local time to the second machine node.

[0139] Then, the second machine node is scheduled to execute all the second operations resulting from all the third SQL changes in this node;

[0140] Then, execute step 203115 above. The specific implementation process includes: pushing up the logical time based on the maximum local time of the first machine node received by the second machine node, and gradually increasing the second sequence number corresponding to each second operation based on the pushed-up logical time.

[0141] For example, machine node 1 has 100 delete operations derived from modification operations that need to be executed, and machine node 2 has 100 insert operations derived from modification operations that need to be executed. First, machine node 1 is scheduled to execute the 100 delete operations derived from modification operations. These 100 delete operations derived from modification operations have 100 local times at the time of execution. These 100 local times are used as the sequence numbers carried in the log of the corresponding delete operation. The maximum value is selected from the 100 local times of machine node 1 and sent to machine node 2. Then, machine node 2 is scheduled to execute the 100 insert operations derived from modification operations. Based on the received maximum value, the local logical time is gradually increased. For each insert operation derived from modification operations, machine node 2 continues to gradually increase the logical time. These 100 progressively increasing logical times are used as the sequence numbers carried in the log of the corresponding insert operation. As can be seen, in the above process, it is not necessary to send all 100 local times of the 100 deletion operations performed in machine node 1 to machine node 2. Instead, for a batch of deletion operations, only one local time is sent, and only the maximum value of one local time is sent, which greatly saves the system's processing resources.

[0142] Next, execute step 209: Write the first log and the second log into the two log streams of the distributed database respectively.

[0143] For example, for business scenario A above, the process from steps 203 to 209 can be found in [reference needed]. Figure 5 As shown.

[0144] For example, for business scenario B above, the process from steps 203 to 209 can be found in [reference needed]. Figure 6As shown. For example, SQL1 is a statement involving partition movement. The log corresponding to the changed delete operation is ROW1, and the log corresponding to the changed insert operation is ROW2. Then, ROW1 carries the local time as the first sequence number, denoted as Ltime_NO1. Furthermore, the logical time obtained by pushing up from Ltime_NO1 is denoted as Ltime_NO2, and ROW2 carries the logical time Ltime_NO2 as the second sequence number.

[0145] The above explains the method for handling transaction logs executed in the transaction executor.

[0146] The following describes the method for processing transaction logs executed in the data synchronization device.

[0147] Figure 7 This is a flowchart illustrating a method for processing transaction logs provided in an embodiment of this specification. The execution entity of this method is... Figure 1 The data synchronization device shown is applied to a distributed database employing multiple log streams. It can be understood that this method can also be executed by any device, equipment, platform, or cluster of devices with computing and processing capabilities. See also Figure 7 The method includes:

[0148] Step 701: Obtain at least two log streams from the distributed database.

[0149] Step 703: Obtain the first log and the second log from any two log streams; wherein the first log carries the first sequence number and the second log carries the second sequence number.

[0150] Step 705: Based on the first sequence number carried in the first log and the second sequence number carried in the second log, obtain the execution order between the first operation corresponding to the first log and the second operation corresponding to the second log; wherein, the order between the first sequence number and the second sequence number is the same as the execution order between the first operation and the second operation.

[0151] Step 707: Based on the execution order of the first operation and the second operation, obtain the transaction data corresponding to the transactions to which the first operation and the second operation belong.

[0152] Regarding the above Figure 7 For an understanding of the process shown, please refer to the above. Figures 2 to 6 The relevant content described.

[0153] When the method described in the embodiments of this specification is applied to business scenario A above, the sequence number carried in the log includes: the SQL statement sequence number. See also Figure 5Based on the statement sequence number carried in each log, since the statement sequence numbers are ordered as SQL_NO1, SQL_NO2, SQL_NO3, it can be determined that the execution order of the three operations corresponding to ROW1, ROW2, and ROW3 in logs carrying SQL_NO1 is first, followed by the three operations corresponding to ROW4, ROW5, and ROW6 in logs carrying SQL_NO2, and finally, the three operations corresponding to ROW7, ROW8, and ROW9 in logs carrying SQL_NO3 are last. Figure 7 In the process shown, for any two different SQL statements corresponding to any two operations, their execution order can be determined, thereby obtaining the transaction data of the correct execution order of transaction tx1.

[0154] When the method described in the embodiments of this specification is applied to the above-described business scenario B, the sequence number carried in the log includes: the operation sequence number, which can specifically be a time value. See also Figure 6 Based on the time values ​​carried in logs ROW1 and ROW2, since the time values ​​are sorted as Ltime_NO1 and Ltime_NO2, it can be determined that the execution order of operation 1 (the deletion operation) corresponding to log ROW1 carrying Ltime_NO1 is first, and the execution order of operation 2 (the insertion operation) corresponding to log ROW2 carrying Ltime_NO2 is later. Thus, in the above... Figure 7 As shown in the process, the execution order between operation 1 and operation 2 can be determined, thereby obtaining the transaction data of transaction tx1 with the correct execution order.

[0155] In one embodiment of this specification, an apparatus for processing transaction logs is provided, see [link to relevant documentation]. Figure 8 This device is applied to transaction executors for distributed databases employing multiple log streams, including:

[0156] The transaction execution module 801 is configured to execute a transaction for the distributed database; the transaction includes a first operation and a second operation.

[0157] The sequence number determination module 802 is configured to determine a first sequence number and a second sequence number; wherein the order between the first sequence number and the second sequence number is the same as the execution order between the first operation and the second operation.

[0158] Log generation module 803 is configured to generate a first log corresponding to the first operation and a second log corresponding to the second operation;

[0159] The log writing module 804 is configured to carry the first sequence number in the first log and the second sequence number in the second log; the first log and the second log are written to two log streams in the distributed database respectively.

[0160] exist Figure 8 In one embodiment of the apparatus shown in this specification, the Structured Query Language (SQL) that triggers the generation of the transaction includes a first SQL and a second SQL; each SQL corresponds to at least one operation in the transaction;

[0161] The first operation is any operation corresponding to the first SQL, and the second operation is any operation corresponding to the second SQL; the execution order of the first operation and the second operation is equal to the execution order of the first SQL and the second SQL.

[0162] Module 802, for determining the sequence number, is configured to execute:

[0163] Generate a first statement sequence number corresponding to the first SQL statement unique within the transaction, and generate a second statement sequence number corresponding to the second SQL statement unique within the transaction; wherein the order of the first statement sequence number and the second statement sequence number is the same as the execution order of the first SQL statement and the second SQL statement; and

[0164] Use the first statement number as the first sequence number and the second statement number as the second sequence number.

[0165] exist Figure 8 In one embodiment of the device shown in this specification,

[0166] The Structured Query Language (SQL) that triggers the generation of the transaction includes a third SQL statement corresponding to the update operation;

[0167] One of the data tables in the distributed database includes a first partition table and a second partition table;

[0168] The transaction execution module 801 is configured to: when executing the transaction, change the modification operation corresponding to the third SQL to the first operation and the second operation, so as to move the data in the first partition table to the second partition table; wherein, the first operation is the modified delete operation, and the second operation is the modified insert operation;

[0169] The sequence number determination module 802 is configured to execute: set the first sequence number first and the second sequence number last.

[0170] exist Figure 8 In one embodiment of the device shown in this specification,

[0171] The transaction execution module 801 is configured to: when executing the transaction, first schedule the execution of the first operation, and then schedule the execution of the second operation;

[0172] The sequence number determination module 802 is configured to perform: setting the first sequence number according to the time of performing the first operation, and setting the second sequence number according to the time of performing the second operation.

[0173] exist Figure 8 In one embodiment of the apparatus shown in this specification, the first partition table and the second partition table are located in the same machine node;

[0174] The sequence number determination module 802 is configured to perform the following: use the local time of the machine node when performing the first operation as the first sequence number; and use the local time of the machine node when performing the second operation as the second sequence number.

[0175] exist Figure 8 In one embodiment of the device shown in this specification, the first partition table and the second partition table are located in different first machine nodes and second machine nodes, respectively;

[0176] The sequence number determination module 802 is configured to perform: using the local time of the first machine node when performing the first operation as the first sequence number; sending the local time of the first machine node when performing the first operation to the second machine node; causing the second machine node to increment the logical time in the second machine node based on the local time received from the first machine node; and using the logical time of the second machine node when performing the second operation as the second sequence number.

[0177] exist Figure 8 In one embodiment of the device shown in this specification, the SQL that triggers the generation of each transaction includes a plurality of the third SQL statements; the first operation and the second operation modified according to each third SQL statement are used to move data from one partition table to another partition table;

[0178] The transaction execution module 801 is configured to: schedule the first machine node to execute all first operations resulting from all third SQL changes in this node; and then schedule the second machine node to execute all second operations resulting from all third SQL changes in this node;

[0179] The sequence number determination module 802 is configured to execute:

[0180] Obtain all local times when the first machine node performs all the first operations resulting from the changes;

[0181] Select the largest local time from all these local times;

[0182] The largest selected local time is sent to the second machine node.

[0183] In one embodiment of this specification, see [reference needed]. Figure 9 A device for processing transaction logs is proposed, which is applied to data synchronization devices and suitable for distributed databases employing multiple log streams. The device includes:

[0184] Log stream acquisition module 901 is configured to acquire at least two log streams from the distributed database;

[0185] The log acquisition module 902 is configured to obtain a first log and a second log from any two log streams; wherein the first log carries a first sequence number and the second log carries a second sequence number;

[0186] The operation sequence determination module 903 is configured to determine the execution order between the first operation corresponding to the first log and the second operation corresponding to the second log based on the first sequence number carried in the first log and the second sequence number carried in the second log; wherein, the order between the first sequence number and the second sequence number is the same as the execution order between the first operation and the second operation.

[0187] The synchronization processing module 904 is configured to obtain the transaction data corresponding to the transactions to which the first operation and the second operation belong, based on the execution order between the first operation and the second operation.

[0188] exist Figure 9 In one embodiment of the device shown in this specification, the sequence number includes: the SQL statement sequence number; the first operation and the second operation correspond to different SQL statements.

[0189] exist Figure 9 In one embodiment of the device shown in this specification, the serial numbers include: time values.

[0190] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the apparatus embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0191] Those skilled in the art will recognize that, in one or more of the examples above, the functions described in this invention can be implemented using hardware, software, firmware, or any combination thereof. When implemented in software, these functions can be stored in a computer-readable medium or transmitted as one or more instructions or code on a computer-readable medium.

[0192] This specification also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method described in any of the foregoing method embodiments.

[0193] As can be seen from the above description of the embodiments, those skilled in the art can clearly understand that the embodiments of this specification can be implemented by means of software plus necessary general-purpose hardware platforms. Based on this understanding, the technical solutions of the embodiments of this specification, or the parts that contribute to the prior art, can be embodied in the form of a computer program product. This computer program product can be stored in a storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments of this specification.

[0194] The above specific embodiments further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made on the basis of the technical solution of the present invention should be included within the scope of protection of the present invention.

Claims

1. A method for processing transaction logs, characterized in that, in, A transaction is a sequence of database operations that access and / or manipulate data; this method is applied to distributed databases that employ multiple log streams. One data table in the distributed database includes a first partition table and a second partition table; The method includes: A transaction is executed for the distributed database; wherein the Structured Query Language (SQL) that triggers the generation of the transaction includes a third SQL statement corresponding to the change operation; When executing the transaction, the change operation corresponding to the third SQL is changed to a first operation and a second operation, so as to move the data in the first partition table to the second partition table; wherein, the first operation is a deletion operation and the second operation is an insert operation. Generate the first log corresponding to the first operation and the second log corresponding to the second operation; The first log corresponding to the first operation carries the first sequence number corresponding to the first operation, and the second log corresponding to the second operation carries the second sequence number corresponding to the second operation, with the first sequence number preceding the second sequence number; The first log, carrying the first sequence number, and the second log, carrying the second sequence number, are written into two log streams in the distributed database, respectively.

2. The method according to claim 1, further comprising: A transaction is executed for the distributed database; multiple SQL statements trigger the generation of the transaction; each SQL statement corresponds to at least one operation in the transaction; a log is generated for each operation. For the multiple SQL statements that triggered the generation of the transaction, a unique statement sequence number is generated for each of the multiple SQL statements within the transaction, based on the execution order among them. For each SQL statement and all operations within that transaction, the statement sequence number corresponding to that SQL statement is carried in all logs corresponding to all operations. For all operations corresponding to the multiple SQL statements that triggered the generation of the transaction, all logs corresponding to all operations, each carrying a statement sequence number, are written to multiple log streams of the distributed database; wherein, the logs corresponding to all operations of a single SQL statement are written to different log streams.

3. The method according to claim 1, wherein, When executing the transaction, the process further includes: first scheduling the execution of the first operation, and then scheduling the execution of the second operation; Setting the first sequence number first and the second sequence number later includes: setting the first sequence number according to the time of executing the first operation, and setting the second sequence number according to the time of executing the second operation.

4. The method according to claim 3, wherein, The first partition table and the second partition table are located on the same machine node; Setting the first sequence number according to the time of executing the first operation includes: using the local time of the machine node when executing the first operation as the first sequence number; Setting the second sequence number based on the time of performing the second operation includes: using the local time of the machine node when performing the second operation as the second sequence number.

5. The method according to claim 3, wherein, The first partition table and the second partition table are located in different first machine nodes and second machine nodes, respectively; Setting the first sequence number according to the time of executing the first operation includes: using the local time of the first machine node when executing the first operation as the first sequence number; The method further includes: sending the local time of the first machine node when performing the first operation to the second machine node; Setting the second sequence number based on the time of performing the second operation includes: Based on the local time received by the second machine node from the first machine node, the logical time in the second machine node is increased; and The logic time after the second machine node pushes up when performing the second operation is used as the second sequence number.

6. The method according to claim 5, wherein, Each SQL that triggers the generation of each transaction includes multiple third SQL statements. The first operation and the second operation modified according to each third SQL statement are used to move data from one partition table to another partition table. The step of scheduling the execution of the first operation before scheduling the execution of the second operation includes: scheduling the first machine node to execute all the first operations resulting from all the changes made by the third SQL in this node; and then scheduling the second machine node to execute all the second operations resulting from all the changes made by the third SQL in this node. Accordingly, sending the local time of the first machine node when performing the first operation to the second machine node includes: Obtain all local times when the first machine node performs all the first operations resulting from the changes; Select the largest local time from all these local times; The largest selected local time is sent to the second machine node.

7. A method for processing transaction logs, characterized in that, in, A transaction is a sequence of database operations that access and / or manipulate data; this method is applied to distributed databases that employ multiple log streams. One data table in the distributed database includes a first partition table and a second partition table; The method includes: Obtain at least two log streams from the distributed database; Obtain a first log and a second log from any two log streams; wherein the first log carries a first sequence number and the second log carries a second sequence number, with the first sequence number preceding the second sequence number; Based on the first sequence number carried in the first log and the second sequence number carried in the second log, the execution order between the first operation corresponding to the first log and the second operation corresponding to the second log is obtained; wherein, the first operation is a delete operation modified from a modification operation corresponding to the third structured query language SQL, and the second operation is an insert operation modified from a modification operation corresponding to the third SQL; the third SQL is used to move data from the first partition table to the second partition table; Based on the execution order of the first and second operations, the transaction data corresponding to the transactions to which the first and second operations belong is obtained.

8. A device for processing transaction logs, wherein, A transaction is a sequence of database operations that access and / or manipulate data; this device is used in distributed databases that employ multiple log streams. One data table in the distributed database includes a first partition table and a second partition table; The device includes: The transaction execution module is configured to execute a transaction for the distributed database; wherein the structured query language (SQL) that triggers the generation of the transaction includes a third SQL corresponding to the change operation; when executing the transaction, the change operation corresponding to the third SQL is changed to a first operation and a second operation, so as to move the data in the first partition table to the second partition table; wherein the first operation is a deletion operation resulting from the change, and the second operation is an insertion operation resulting from the change; The sequence number determination module is configured to determine a first sequence number corresponding to the first operation and a second sequence number corresponding to the second operation, and set the first sequence number first and the second sequence number last. The log generation module is configured to generate a first log corresponding to a first operation and a second log corresponding to a second operation; the first log corresponding to the first operation carries a first sequence number corresponding to the first operation, and the second log corresponding to the second operation carries a second sequence number corresponding to the second operation; The log writing module is configured to write the first log with the first sequence number and the second log with the second sequence number into two log streams in the distributed database, respectively.

9. A device for processing transaction logs, wherein, A transaction is a sequence of database operations that access and / or manipulate data; this device is used in distributed databases that employ multiple log streams. One data table in the distributed database includes a first partition table and a second partition table; The device includes: The log stream acquisition module is configured to acquire at least two log streams from the distributed database. The log acquisition module is configured to obtain a first log and a second log from any two log streams; wherein the first log carries a first sequence number and the second log carries a second sequence number, with the first sequence number preceding the second sequence number; The operation order determination module is configured to determine the execution order between the first operation corresponding to the first log and the second operation corresponding to the second log based on the first sequence number carried in the first log and the second sequence number carried in the second log; wherein, the first operation is a delete operation modified from the change operation corresponding to the third structured query language SQL, and the second operation is an insert operation modified from the change operation corresponding to the third SQL; the third SQL is used to move data from the first partition table to the second partition table; The synchronization processing module is configured to obtain the transaction data corresponding to the transactions to which the first operation and the second operation belong, based on the execution order between the first operation and the second operation.

10. A computing device, comprising a memory and a processor, characterized in that, The memory stores executable code, and when the processor executes the executable code, it implements the method of any one of claims 1 to 7.

Images