A method and device for log parsing synchronous transaction storage

Abstract

The present application relates to a kind of log analysis synchronous transaction storage method and equipment.Its method part mainly includes: deploying data synchronization service in target end;When receiving the next log operation sent by source end, judge whether current all operation memory occupation is greater than the memory occupation value set, if greater, trigger checkpoint save operation;Using the LSN of last received log operation, set the LSN as current checkpoint LSN, store transaction operation received;After next checkpoint trigger, continue to use the way of checkpoint save operation to store transaction operation received, and so on, form a checkpoint transaction table linked list in the order of checkpoint LSN size, to complete the storage of transaction information and transaction operation of target end data synchronization service.The present application can avoid the situation that source end database archive log is accumulated in large quantities when the performance of target end database is insufficient, so as to affect the safety of source end database operation.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method and apparatus for log parsing and synchronous transaction storage. Background Technology

[0002] Currently, heterogeneous database replication technology based on database log parsing is widely used. This technology captures incremental data from the source database and sends it to the target database. On the target database, the incremental data is applied through a common database access interface, thus achieving data replication. Because this technology uses a common database interface, it supports replication of heterogeneous database systems and heterogeneous operating system environments. Furthermore, the target standby database system is readable and writable, making it a "dual-active" system.

[0003] When real-time synchronization based on database log parsing is running, a common problem arises where the source database log parsing component is blocked from sending transaction encapsulation message packets due to slow execution of transactions in the destination database. Simultaneously, when the source database log parsing component's transaction encapsulation message sending module fails to send, the source database archive logs cannot be cleaned up, leading to the source database consuming a large amount of disk space. In the most severe cases, this can cause the disk space to become full, preventing the source database from providing services normally. Therefore, how to efficiently store these parsed transaction operations in the destination data synchronization service has become a crucial technical problem that urgently needs to be solved in the industry.

[0004] Therefore, overcoming the shortcomings of existing technologies and solving the aforementioned technical problems is a difficult problem to be solved in this technical field. Summary of the Invention

[0005] To address the shortcomings or improvement needs of existing technologies: When synchronizing transactions on the target end, the synchronization operations received by the target end data synchronization service cannot be entered into the database in a timely manner due to the impact of the target end database synchronization performance. Therefore, it is necessary to store these operations that cannot be entered into the database in a timely manner on the disk to avoid a large accumulation of archive logs in the source end database when the target end database synchronization performance is insufficient, thereby affecting the security of the source end database operation.

[0006] This invention provides a method and device for log parsing and synchronous transaction storage. In data synchronization, data is synchronized on a transaction-by-transaction basis. Therefore, when storing transactions, the target-end data synchronization service needs to organize the received log operations on a transaction-by-transaction basis. Committed transactions (transactions that have received commit operations) are stored in the committed transaction list in order of their commit LSN size, while uncommitted transactions are stored in the active transaction list in order of their initial LSN size. The transaction information and transaction operations organized on a transaction-by-transaction basis contain information in a fixed format, such as log LSN, transaction ID, operation number, etc. Transaction information needs to be stored in the order of the commit LSN, while transaction operations need to be stored in the order of the operation number. This storage characteristic is very consistent with the data storage characteristics of relational databases. Therefore, based on the checkpoint LSN, the transaction operations and transaction information in the committed transaction list (where the commit operation LSN is less than or equal to the checkpoint LSN) are stored in the relational database table on a transaction-by-transaction basis. Then, the transaction operations and transaction information in the active transaction list are stored in the relational database table to complete the storage of the operations.

[0007] The embodiments of the present invention adopt the following technical solutions: In a first aspect, the present invention provides a method for log parsing and synchronous transaction storage, comprising: Deploy a data synchronization service on the target side, initialize each linked list, and set a memory usage value to trigger a checkpoint save operation; When receiving the next log operation from the source, determine whether the memory usage of all current operations is greater than the set memory usage value. If it is greater, trigger the checkpoint save operation: use the LSN of the most recently received log operation, set the LSN as the current checkpoint LSN, and store the received transaction operation. After the next checkpoint is triggered, the received transaction operations are stored using the checkpoint save operation. This process is repeated to form a linked list of checkpoint transaction tables in order of checkpoint LSN size, so as to complete the storage of transaction information and transaction operations by the target data synchronization service.

[0008] Furthermore, the step of deploying a data synchronization service on the target end, initializing various linked lists, and setting a trigger operation to save the memory usage value of the checkpoint specifically includes initializing the following information: The committed transaction list is used to store transactions that have received commit operations. These transactions are stored in order according to the LSN size of the commit operations. The active transaction list is used to store transactions that have not yet received a commit operation. These transactions are stored in order according to the LSN size of the first operation of the transaction. Set a memory usage value for a trigger operation to save checkpoints. When the memory usage of received operations reaches this value, a checkpoint is initiated to save the current transaction operations in memory. Create two identical active transaction tables in the database to store active transaction information filtered by the storage checkpoint LSN.

[0009] Furthermore, the column definitions of the active transaction table specifically include: Active transaction table A: CREATE TABLE A_LSN value(TRXID BIGINT, LSN BIGINT, OP_TABLEIDINT, OP_SAVEID BIGINT); its column definitions are explained below: TRXID: Transaction ID of the active transaction; LSN: Starting LSN of the active transaction; OP_TABLEID: Table ID of the operation table in the database for the active transaction operation. This table ID can be used to find the operation table in the database where the operation is stored; OP_SAVEID: Storage number of the active transaction in the operation table. It and the operation number in the transaction operation can be combined into a key to identify its position in the operation table.

[0010] Furthermore, the checkpoint saving operation: using the LSN of the most recently received log operation, setting this LSN as the current checkpoint LSN, and storing the received transaction operations specifically includes: Create a checkpoint transaction information table and a checkpoint transaction operation table in the database, naming them with the checkpoint LSN value as a suffix to enable fast sorting of the tables; Collect the set of active transactions and assign operation table IDs and transaction save IDs; collect the set of committed transactions and assign operation table IDs and transaction save IDs. Save committed transaction operations to the transaction operation table; save active transaction operations to the transaction operation table; Save committed transaction information to the transaction information table; save active transaction information to the active transaction table; Rename the current active transaction table using the checkpoint LSN to complete the storage action for this checkpoint.

[0011] Furthermore, the column definitions of the checkpoint transaction information table and the checkpoint transaction operation table specifically include: Checkpoint transaction information table C: CREATE TABLE C_LSN value(COMMIT_LSN BIGINT CLUSTERPRIMARY KEY, TRXID BIGINT, OP_TABLEID INT, OP_SAVEID BIGINT); its column definitions are explained below: COMMIT_LSN: The transaction's commit LSN, which serves as the primary key to ensure that transactions are saved in ascending order of their commit LSNs; TRXID: The transaction ID; OP_TABLEID: The table ID of the operation table in the database, which can be used to find the operation table where the operation is stored; OP_SAVEID: The storage number of the transaction in the operation table, which, together with the operation number in the transaction, can be combined into a key to identify its position in the operation table; The checkpoint transaction operation table D: CREATE TABLE D_LSN value(OP_SAVEID BIGINT, OP_IDBIGINT, OP BLOB, CLUSTER PRIMARY KEY(OP_SAVEID, OP_ID)); its column definitions are explained as follows: OP_SAVEID: The storage number of the transaction in the operation table. It and the operation number OP_ID in the transaction operation can be combined into a key to identify its position in the operation table; OP_ID: The operation number in the transaction operation.

[0012] Furthermore, the process of collecting the set of active transactions and assigning operation table IDs and transaction save IDs, and collecting the set of committed transactions and assigning operation table IDs and transaction save IDs, specifically includes: Collect active transactions by traversing the active transaction list using the checkpoint LSN, and collecting all transactions whose starting LSN is less than or equal to the checkpoint LSN into the active transaction set; traverse the committed transaction list using the checkpoint LSN, and collect all transactions whose starting LSN is less than or equal to the checkpoint LSN and whose committed LSN is greater than the checkpoint LSN into the active transaction set; after collection, deduplicate transactions in the active transaction set are removed. Assign a unique OP_SAVEID and the table ID of the current transaction operation table D in the database to OP_TABLEID for each transaction in the active transaction set. OP_SAVEID will be used to identify the position of the transaction operation in the transaction operation table D, while OP_TABLEID is a pointer to the transaction operation table where the current transaction operation is stored. Collect committed transactions by traversing the committed transaction list using the checkpoint LSN and collecting transactions whose commit LSN is less than or equal to the checkpoint LSN into the committed transaction set. Assign a unique OP_SAVEID and the table ID of the current transaction operation table D in the database to each transaction in the committed transaction set to OP_TABLEID. OP_SAVEID will be used to identify the position of the transaction operation in the transaction operation table D, while OP_TABLEID is a pointer to the transaction operation table where the current transaction operation is stored.

[0013] Furthermore, the step of saving committed transaction operations to the transaction operation table, and saving active transaction operations to the transaction operation table specifically includes: Retrieve transactions sequentially from the committed transaction set, determine the transaction operation table where the transaction operations are stored from the transaction's OP_TABLEID, store the transaction operations into the transaction operation table, and immediately release the memory space occupied by these operations. Transactions are extracted sequentially from the active transaction set. The transaction operation table where the transaction operations are stored is determined from the transaction's OP_TABLEID. Operations in the transaction whose LSN is less than or equal to the checkpoint LSN are stored in the transaction operation table, and the memory space occupied by these operations is immediately released.

[0014] Furthermore, saving committed transaction information to the transaction information table; saving active transaction information to the active transaction table specifically includes: Save committed transaction information to checkpoint transaction information table C: According to the definition of checkpoint transaction information table, the information saved for a transaction includes transaction commit LSN, transaction ID, the transaction's specified checkpoint operation table ID, and the transaction's OP_SAVEID in the transaction operation table. The information is saved in order of transaction commit LSN size. Save active transaction information to the active transaction table: Select the table with the smaller checkpoint LSN from the two active transaction tables A as the table to be saved this time. First, clear the historical records in the table, and then save the active transactions filtered by this checkpoint to the table.

[0015] Furthermore, when the target data synchronization service fails and restarts: Load two active transaction tables and sort them by the LSN value in the active transaction table name. At this point, the active transaction table with the larger LSN is the last valid checkpoint, and its corresponding LSN is used as the recovery point. After reading the active transaction information from the active transaction table with the largest LSN and restoring it to the active transaction linked list, the system continues to receive transaction operations sent by the source end. It compares the LSN of the operation with the LSN of the recovery point and only accepts operations whose LSN is greater than the LSN of the recovery point, thus realizing the fault recovery function.

[0016] On the other hand, the present invention provides a log parsing synchronous transaction storage device, specifically comprising at least one processor and a memory, wherein the at least one processor and the memory are connected via a data bus, the memory stores instructions that can be executed by the at least one processor, and the instructions, after being executed by the processor, are used to complete the log parsing synchronous transaction storage method in the first aspect.

[0017] Compared with the prior art, the beneficial effect of the present invention is that when the synchronization operation received by the target data synchronization service cannot be entered into the database in a timely manner, these operations that cannot be entered into the database in a timely manner can be efficiently and accurately stored on the disk, so as to avoid a large accumulation of archive logs in the source database when the synchronization performance of the target database is insufficient, thereby affecting the security of the source database operation.

[0018] In addition, after collecting information on active and committed transactions, a saved checkpoint operation table and OP_SAVEID in the operation table are assigned to each transaction. This ensures that the operation of each transaction will not cross multiple checkpoint transaction operation tables when it is saved, and the order between adjacent operations of a transaction can be accomplished with the help of the storage database function, which simplifies the complexity of checkpoint storage.

[0019] Secondly, saving transaction operations to the checkpoint operation table in units of transactions and in order of operation number allows adjacent operations within the same transaction to be stored more closely in the database, improving the data page hit rate when reading transaction operations. Furthermore, since the database storing transaction operations has its own cache, the memory occupied by a transaction operation can be released immediately after it is saved, and the freed-up memory can be used immediately to cache new transaction operations, improving memory utilization efficiency.

[0020] Furthermore, the active transaction table, checkpoint transaction table, and checkpoint transaction operation table are all named with the checkpoint LSN as a suffix. The received transactions can be quickly sorted by table name, reducing the complexity of fault recovery. Attached Figure Description

[0021] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments of the present invention will be briefly described below. Obviously, the drawings described below are merely some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without any creative effort.

[0022] Figure 1 This is a flowchart of a method for log parsing and synchronous transaction storage provided in Embodiment 1 of the present invention; Figure 2 This is a detailed flowchart of step 200 provided in Embodiment 1 of the present invention; Figure 3 This is a detailed flowchart of step 220 provided in Embodiment 1 of the present invention; Figure 4 This is a detailed flowchart of step 230 provided in Embodiment 1 of the present invention; Figure 5 This is a detailed flowchart of step 240 provided in Embodiment 1 of the present invention; Figure 6This is a flowchart illustrating the implementation of fault recovery provided in Embodiment 1 of the present invention; Figure 7 This is a schematic diagram of a device structure for log parsing and synchronous transaction storage provided in Embodiment 3 of the present invention. Detailed Implementation

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

[0024] This invention is an architecture of a specific functional system. Therefore, the specific embodiments mainly describe the functional logic relationship of each structural module, and do not limit the specific software and hardware implementation methods.

[0025] Furthermore, the technical features involved in the various embodiments of the present invention described below can be combined with each other as long as they do not conflict with each other. The present invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0026] Example 1: This invention compares the LSN of a transaction operation with the LSN of a checkpoint, and saves operations with an LSN less than or equal to the checkpoint LSN to the corresponding relational database table as a unit of transaction. Furthermore, it distinguishes between committed transactions and active transactions during the saving process, because committed transactions are used for synchronous execution, while active transactions are used for fault recovery.

[0027] like Figure 1 As shown, based on the above-mentioned reality, this embodiment of the invention provides a method for log parsing and synchronous transaction storage, the specific steps of which are as follows.

[0028] Step 100: Deploy the data synchronization service on the target end, initialize each linked list, and set a memory usage value to trigger a checkpoint save operation.

[0029] Step 200: When receiving the next log operation from the source, determine if the memory usage of all current operations exceeds the set memory usage value. If it does, trigger a checkpoint saving operation: Use the LSN of the most recently received log operation, set this LSN as the current checkpoint LSN, and store the received transaction operations. In this step, when receiving the next log operation from the source, categorize it by transaction ID to its corresponding transaction, and then determine if the memory usage of all current operations exceeds the set memory usage value N. If it does, trigger a checkpoint saving operation; otherwise, continue receiving the next log operation.

[0030] Step 300: After the next checkpoint is triggered, the received transaction operations are stored using the checkpoint save operation method. This process is repeated to form a linked list of checkpoint transaction tables in order of checkpoint LSN size, so as to complete the storage of transaction information and transaction operations by the target data synchronization service.

[0031] Specifically, in one embodiment of this preferred embodiment, the target-end data synchronization service deployed in step 100 is responsible for receiving log operations sent from the source end. Each log operation contains information such as the operation's LSN and transaction ID. The target-end data synchronization service needs to initialize the following information: The committed transaction list is used to store transactions that have received commit operations. These transactions are stored in order according to the LSN size of the commit operations. The active transaction list is used to store transactions that have not yet received a commit operation. These transactions are stored in order according to the LSN size of the first operation of the transaction. Set a memory usage value N for triggering operation to save checkpoints. When the memory usage of received operations reaches this memory usage value N, a checkpoint is initiated to save the current transaction operations in memory. Create two identical active transaction tables (A) in the database to store active transaction information filtered by the storage checkpoint LSN: Active transaction table A: CREATE TABLE A_LSN value(TRXID BIGINT, LSN BIGINT, OP_TABLEIDINT, OP_SAVEID BIGINT); its column definitions are explained below: TRXID: Transaction ID of the active transaction; LSN: Starting LSN of the active transaction; OP_TABLEID: Table ID of the operation table in the database for the active transaction operation. This table ID can be used to find the operation table in the database where the operation is stored; OP_SAVEID: Storage number of the active transaction in the operation table. It and the operation number in the transaction operation can be combined into a key to identify its position in the operation table.

[0032] Creating two active transaction tables A with the same structure is to allow for alternating saving during checkpoint storage, ensuring that the activity of the current checkpoint is saved without corrupting the active transaction information of the previous checkpoint.

[0033] like Figure 2 As shown, in one embodiment of this preferred embodiment, the checkpoint saving operation in step 200, which uses the LSN of the most recently received log operation and sets that LSN as the current checkpoint LSN, specifically includes the following steps: Step 210: Create a checkpoint transaction information table and a checkpoint transaction operation table in the database. Name them with the checkpoint LSN value as a suffix to enable fast sorting of the tables.

[0034] Step 220: Collect the active transaction set and assign operation table IDs and transaction save IDs; collect the committed transaction set and assign operation table IDs and transaction save IDs.

[0035] Step 230: Save committed transaction operations to the transaction operation table; save active transaction operations to the transaction operation table.

[0036] Step 240: Save committed transaction information to the transaction information table; save active transaction information to the active transaction table.

[0037] Step 250: Rename the current active transaction table using the checkpoint LSN to complete the storage action for this checkpoint. Using the checkpoint LSN value to name the active transaction table facilitates rapid sorting of the active transaction tables; the order can be determined simply by comparing the LSN values ​​in the two active transaction tables.

[0038] In one embodiment of this preferred embodiment, the column definitions of the checkpoint transaction information table and the checkpoint transaction operation table in step 210 specifically include: Checkpoint transaction information table C: CREATE TABLE C_LSN value(COMMIT_LSN BIGINT CLUSTERPRIMARY KEY, TRXID BIGINT, OP_TABLEID INT, OP_SAVEID BIGINT); its column definitions are explained below: COMMIT_LSN: The transaction's commit LSN, which serves as the primary key to ensure that transactions are saved in ascending order of their commit LSNs; TRXID: The transaction ID of the active transaction; OP_TABLEID: The table ID of the operation table in the database for the active transaction's operations, which can be used to find the operation table where the operation is stored; OP_SAVEID: The storage number of the active transaction in the operation table, which, together with the operation number in the transaction, can be combined into a key to identify its position in the operation table; The checkpoint transaction operation table D: CREATE TABLE D_LSN value(OP_SAVEID BIGINT, OP_IDBIGINT, OP BLOB, CLUSTER PRIMARY KEY(OP_SAVEID, OP_ID)); its column definitions are explained as follows: OP_SAVEID: The storage number of the transaction in the operation table. It and the operation number OP_ID in the transaction operation can be combined into a key to identify its position in the operation table; OP_ID: The operation number in the transaction operation.

[0039] like Figure 3 As shown, in one embodiment of this preferred embodiment, step 220, which involves collecting the set of active transactions and assigning operation table IDs and transaction save IDs, specifically includes the following steps: Step 221: Collect active transactions. Traverse the active transaction list using the checkpoint LSN and collect all transactions whose starting LSN is less than or equal to the checkpoint LSN into the active transaction set. Traverse the committed transaction list using the checkpoint LSN and collect all transactions whose starting LSN is less than or equal to the checkpoint LSN and whose committed LSN is greater than the checkpoint LSN into the active transaction set. After collection, deduplicate the transactions in the active transaction set.

[0040] The reason for deduplication in this step is that after collecting the active transaction list, these active transactions will be added to the committed transaction list after receiving commit messages during subsequent execution. This can lead to duplicate collection of the same transactions when collecting active transactions from the committed transaction list later. Furthermore, collection must begin with the active transaction list. If collection starts with the committed transaction list, and then switches to the active transaction list after collecting the committed transaction list, some transactions might be moved to the committed transaction list due to commit operations, resulting in omissions.

[0041] Step 222: Assign a unique OP_SAVEID and the table ID of the current transaction operation table D in the database to each transaction in the active transaction set to OP_TABLEID. OP_SAVEID will be used to identify the position of the transaction operation in the transaction operation table D, while OP_TABLEID points to the transaction operation table where the current transaction operation is stored.

[0042] Once a transaction's OP_SAVEID and OP_TABLEID are assigned, they will remain unchanged. Therefore, if an active transaction has already assigned OP_SAVEID or OP_TABLEID at the previous checkpoint, it will not be assigned again.

[0043] Step 223: Collect committed transactions. Use the checkpoint LSN to traverse the committed transaction list and collect transactions whose commit LSN is less than or equal to the checkpoint LSN into the committed transaction set.

[0044] Step 224: Assign a unique OP_SAVEID and the table ID of the current transaction operation table D in the database to each transaction in the committed transaction set to OP_TABLEID. OP_SAVEID will be used to identify the position of the transaction operation in the transaction operation table D, while OP_TABLEID points to the transaction operation table where the current transaction operation is stored.

[0045] Once a transaction's OP_SAVEID and OP_TABLEID are assigned, they will remain unchanged. Therefore, if an active transaction has already assigned OP_SAVEID or OP_TABLEID at the previous checkpoint, it will not be assigned again.

[0046] like Figure 4 As shown, in one embodiment of this preferred embodiment, step 230, which involves saving committed transaction operations to the transaction operation table, specifically includes the following steps: Step 231: Extract transactions sequentially from the committed transaction set, determine the transaction operation table where the transaction operations are stored from the transaction's OP_TABLEID, store the transaction operations into the transaction operation table, and immediately release the memory space occupied by these operations.

[0047] When saving a transaction to the transaction operation table, each operation needs to be inserted into the transaction operation table using the transaction's OP_SAVEID and operation number OP_ID as a composite primary key. This way, when retrieving the transaction, all operations of the transaction can be traversed by using the transaction's OP_SAVEID in the transaction operation table.

[0048] Step 232: Extract transactions sequentially from the active transaction set, determine the transaction operation table where the transaction operations are stored from the transaction's OP_TABLEID, store the operations in the transaction whose LSN is less than or equal to the checkpoint LSN into the transaction operation table, and immediately release the memory space occupied by these operations.

[0049] The difference between storing operations in active transactions and operations in committed transactions is that active transactions may contain operations whose LSN is greater than the checkpoint LSN. These operations can be received again during fault recovery because their LSN is greater than the checkpoint LSN, so they do not need to be saved at this checkpoint. Committed transactions do not need to make this judgment because all their operations are less than or equal to the checkpoint LSN.

[0050] like Figure 5 As shown, in one embodiment of this preferred embodiment, step 240, which involves saving committed transaction information to the transaction information table, specifically includes the following steps: Step 241: Save the committed transaction information to the checkpoint transaction information table C: According to the definition of the checkpoint transaction information table, the transaction information that needs to be saved includes the transaction commit LSN, transaction ID, the transaction's specified checkpoint operation table ID, and the transaction's OP_SAVEID in the transaction operation table. Furthermore, the information needs to be saved in order of the transaction's commit LSN size.

[0051] Step 242: Save active transaction information to the active transaction table: Select the table with the smaller checkpoint LSN from the two active transaction tables A as the table to be saved this time. First, clear the historical records in the table, and then save the active transactions filtered out by this checkpoint to the table.

[0052] Based on the above steps, in one embodiment of this preferred embodiment, after the next checkpoint is triggered, step 300 generates a transaction information table C1 in the manner of step 200, and so on, forming a linked list of checkpoint transaction tables in order of checkpoint LSN size, thus completing the storage of transaction information and transaction operations by the target end data synchronization service.

[0053] like Figure 6 As shown, in one embodiment of this preferred embodiment, when the target data synchronization service fails and restarts, the fault recovery specifically includes the following steps: Step 401: Load two active transaction tables and sort them by the LSN value in the active transaction table name. At this point, the active transaction table with the larger LSN is the last valid checkpoint, and its corresponding LSN is used as the recovery point.

[0054] Step 402: After reading the active transaction information from the active transaction table with the largest LSN and restoring it to the active transaction linked list, continue to receive transaction operations sent by the source end. Compare the LSN of the operation with the LSN of the recovery point, and only accept operations whose LSN is greater than the LSN of the recovery point to realize the fault recovery function.

[0055] When storing transactions using the above scheme, committed transaction information within the LSN interval of two adjacent checkpoints is stored in the same checkpoint transaction information table. This way, when transactions are synchronized and entered into the database, they are loaded into the database sequentially according to the size order of the LSN in the checkpoint transaction information table, ensuring the order of transaction entry and data consistency.

[0056] In summary, this embodiment can efficiently and accurately store the synchronization operations received by the target database synchronization service into the disk when they cannot be entered into the database in a timely manner. This avoids the accumulation of a large number of archive logs in the source database when the target database synchronization performance is insufficient, thereby affecting the security of the source database operation.

[0057] In addition, after collecting information on active and committed transactions, a saved checkpoint operation table and OP_SAVEID in the operation table are assigned to each transaction. This ensures that the operation of each transaction will not cross multiple checkpoint transaction operation tables when it is saved, and the order between adjacent operations of a transaction can be accomplished with the help of the storage database function, which simplifies the complexity of checkpoint storage.

[0058] Secondly, saving transaction operations to the checkpoint operation table in units of transactions and in order of operation number allows adjacent operations within the same transaction to be stored more closely in the database, improving the data page hit rate when reading transaction operations. Furthermore, since the database storing transaction operations has its own cache, the memory occupied by a transaction operation can be released immediately after it is saved, and the freed-up memory can be used immediately to cache new transaction operations, improving memory utilization efficiency.

[0059] Furthermore, the active transaction table, checkpoint transaction table, and checkpoint transaction operation table are all named with the checkpoint LSN as a suffix. The received transactions can be quickly sorted by table name, reducing the complexity of fault recovery.

[0060] Example 2: Based on the log parsing and synchronous transaction storage method provided in Embodiment 1, this Embodiment 2 will provide a more detailed description of the present invention through a specific application scenario.

[0061] Examples of the above solutions are as follows: Initialize two active transaction tables, A_0 and A_1, in the database.

[0062] The source database has a table T1 (ID VARCHAR). Three transactions on the source end perform the following operations on table T1: TRX1:INSERT INTO T1(ID) VALUES('TRX1_1'); TRX2:INSERT INTO T1(ID) VALUES('TRX2_1'); TRX2:COMMIT; TRX1:INSERT INTO T1(ID) VALUES('TRX1_2'); TRX3:INSERT INTO T1(ID) VALUES('TRX3_1'); TRX3:COMMIT; TRX1:COMMIT; The sequence of the above operations will result in the following numbered table after the destination log receiving thread receives the logs:

[0063] The storage checkpoint procedure is as follows: 1. After receiving and saving the operation with LSN 3, a checkpoint is triggered. At this time, the checkpoint LSN is set to 3. There is transaction {TRX1} in the active transaction chain and transaction {TRX2} in the committed transaction chain.

[0064] 2. Create a checkpoint transaction information table C_3 and a checkpoint transaction operation table D_3 in the stored database. Set the table ID of the transaction operation table D_3 to 1003.

[0065] CREATE TABLE C_3(COMMIT_LSN BIGINT PRIMARY KEY, TRXID BIGINT, OP_TABLEID INT, OP_SAVEID BIGINT); CREATE TABLE D_3(OP_SAVEID BIGINT, OP_ID BIGINT, OP BLOB, CLUSTERPRIMARY KEY(OP_SAVEID, OP_ID)).

[0066] 3. Select transactions with a starting LSN less than or equal to 3 from the active transaction list and the committed transaction list and store them in the active transaction set. TRX1 meets the condition. Assign transaction operation table ID and OP_SAVEID to TRX1, TRX1=>{OP_TABLEID=1003, OP_SAVEID=1}.

[0067] 4. Filter the committed transaction list and select transactions with a commit LSN less than or equal to 3, then store them in the committed transaction set. TRX2 meets this condition. Allocate the transaction operation table ID and OP_SAVEID for TRX2: TRX2=>{OP_TABLEID=1003, OP_SAVEID=2}.

[0068] 5. Extract transactions sequentially from the committed transaction set and save the transaction operations of transaction TRX2 to the transaction operation table D_3.

[0069]

[0070] 6. Extract transactions sequentially from the active transaction set and save the transaction operations of transaction TRX1 to the transaction operation table D_3.

[0071]

[0072] 7. Save the committed transaction information to the checkpoint transaction information table C_3. The committed transaction set contains TRX2.

[0073]

[0074] 8. Save the active transaction information to the active transaction table A_0, where the active transaction set contains TRX1.

[0075]

[0076] 9. Rename the active transaction table A_0 to A_3 with the checkpoint value as the suffix to complete this checkpoint storage action.

[0077] 10. After receiving and saving the operation with LSN 6, a checkpoint is triggered. At this time, the checkpoint LSN is set to 6. There is transaction {TRX1} in the active transaction list and transaction {TRX3} in the committed transaction list.

[0078] 11. Create a checkpoint transaction information table C_6 and a checkpoint transaction operation table D_6 in the stored database. Set the table ID of the transaction operation table D_6 to 1005.

[0079] 12. Select transactions with a starting LSN less than or equal to 6 from the active transaction list and the committed transaction list, and store them in the active transaction set. TRX1 meets the condition. Allocate the transaction operation table ID and OP_SAVEID for TRX1. Since TRX1 has already allocated this information in the previous checkpoint, it remains unchanged in this checkpoint: TRX1=>{OP_TABLEID=1003, OP_SAVEID=1}.

[0080] 13. Filter the committed transaction list and select transactions with a commit LSN less than or equal to 6, then store them in the committed transaction set. TRX3 meets this condition. Allocate the transaction operation table ID and OP_SAVEID for TRX3: TRX3=>{OP_TABLEID=1005, OP_SAVEID=3}.

[0081] 14. Extract transactions sequentially from the committed transaction set and save the transaction operations of transaction TRX3 to the transaction operation table D_6.

[0082]

[0083] 15. Extract transactions sequentially from the active transaction set and save the transaction operations of transaction TRX1 to the transaction operation table D_3.

[0084]

[0085] 16. Save the committed transaction information to the checkpoint transaction information table C_6. The committed transaction set contains TRX3.

[0086]

[0087] 17. Take the active transaction table A_1 with the smaller LSN, save the active transaction information to the active transaction table A_1, and the active transaction set contains TRX1.

[0088]

[0089] 18. Rename the active transaction table A_1 to A_6 with the checkpoint value as the suffix to complete this checkpoint storage action.

[0090] 19. After the second checkpoint is completed, restart and restore, load the active transaction tables A_3 and A_6 and sort them according to the size of the checkpoint LSN to form the order {A_3, A_6}.

[0091] 20. Perform fault recovery on the active transaction table with checkpoint LSN of 6.

[0092] 21. Load the active transaction information from the active transaction table A_6 and restore TRX1 to the active transaction linked list.

[0093] 22. Only receive operation logs with LSN greater than 6 sent by the source end. When a TRX1 transaction commit operation with LSN of 7 is received, move transaction TRX1 to the committed transaction list to complete the fault recovery.

[0094] Example 3: Based on the log parsing and synchronous transaction storage method provided in Embodiments 1 and 2 above, the present invention also provides a device for implementing the above method, such as... Figure 7 The diagram shown is a schematic representation of the device architecture according to an embodiment of the present invention. The device for log parsing and synchronous transaction storage in this embodiment includes one or more processors 21 and a memory 22. Figure 7 Take a processor 21 as an example.

[0095] Processor 21 and memory 22 can be connected via a bus or other means. Figure 7 Taking the example of a connection between China and Israel via a bus.

[0096] The memory 22, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules, such as the log parsing synchronous transaction storage method and system in Embodiments 1 and 2. The processor 21 executes various functional applications and data processing of the log parsing synchronous transaction storage device by running the non-volatile software programs, instructions, and modules stored in the memory 22, thereby implementing the log parsing synchronous transaction storage method of Embodiments 1 and 2.

[0097] Memory 22 may include high-speed random access memory, and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some embodiments, memory 22 may optionally include memory remotely located relative to processor 21, which can be connected to processor 21 via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0098] The program instructions / modules are stored in memory 22. When executed by one or more processors 21, they perform the log parsing and synchronous transaction storage method described in Embodiments 1 and 2 above, for example, executing the method described above. Figures 1 to 6 The steps shown.

[0099] Those skilled in the art will understand that all or part of the steps in the various methods of the embodiments can be implemented by a program instructing related hardware. The program can be stored in a computer-readable storage medium, which may include: read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

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

Claims

1. A method for log parsing and synchronous transaction storage, characterized in that, include: Deploy a data synchronization service on the target side, initialize each linked list, and set a memory usage value to trigger a checkpoint save operation; This includes initializing the following information: a linked list of committed transactions, which stores transactions that have received commit operations, and these transactions are stored in order according to the LSN size of the commit operations; The active transaction list is used to store transactions that have not yet received a commit operation. These transactions are stored in order according to the LSN size of the first operation of the transaction. Create two identical active transaction tables in the database to store active transaction information filtered by the storage checkpoint LSN; When receiving the next log operation from the source, determine whether the memory usage of all current operations is greater than the set memory usage value. If it is greater, trigger the checkpoint save operation: use the LSN of the most recently received log operation, set the LSN as the current checkpoint LSN, and store the received transaction operation. After the next checkpoint is triggered, the received transaction operations are stored using the checkpoint save operation. This process is repeated to form a linked list of checkpoint transaction tables in order of checkpoint LSN size, so as to complete the storage of transaction information and transaction operations by the target data synchronization service.

2. The method for log parsing and synchronous transaction storage according to claim 1, characterized in that, The column definitions of the active transaction table specifically include: Active transaction table A: Create a table named A_LSN_values, with columns including TRXID, LSN, OP_TABLEID, and OP_SAVEID. The corresponding SQL statement is: CREATE TABLE A_LSN_values(TRXID BIGINT, LSN BIGINT, OP_TABLEID BIGINT, OP_SAVEID BIGINT); the column definitions are explained below: TRXID: Transaction ID of the active transaction; LSN: Starting LSN of the active transaction; OP_TABLEID: Table ID of the operation table in the database. This table ID is used to find the operation table in the database where the operation is stored; OP_SAVEID: Storage number of the active transaction in the operation table. It is combined with the operation number in the transaction operation to form a KEY to identify its position in the operation table.

3. The method for log parsing and synchronous transaction storage according to claim 2, characterized in that, The checkpoint saving operation involves using the LSN of the most recently received log operation, setting this LSN as the current checkpoint LSN, and storing the received transaction operations. Specifically, this includes: Create a checkpoint transaction information table and a checkpoint transaction operation table in the database, naming them with the checkpoint LSN value as a suffix to enable fast sorting of the tables; Collect the set of active transactions and assign operation table IDs and transaction save IDs; collect the set of committed transactions and assign operation table IDs and transaction save IDs. Save committed transaction operations to the transaction operation table; save active transaction operations to the transaction operation table; Save committed transaction information to the transaction information table; save active transaction information to the active transaction table; Rename the current active transaction table using the checkpoint LSN to complete the storage action for this checkpoint.

4. The method for log parsing and synchronous transaction storage according to claim 3, characterized in that, The column definitions of the checkpoint transaction information table and the checkpoint transaction operation table specifically include: Checkpoint Transaction Information Table C: Create a table named C_LSN_values, with columns including COMMIT_LSN, TRXID, OP_TABLEID, and OP_SAVEID. The corresponding SQL statement is: CREATE TABLE C_LSN_values(COMMIT_LSN BIGINTCLUSTER PRIMARY KEY, TRXID BIGINT, OP_TABLEID BIGINT, OP_SAVEID BIGINT); the column definitions are explained below: COMMIT_LSN: The transaction's commit LSN, which serves as the primary key to ensure that transactions are saved in ascending order of their commit LSNs; TRXID: The transaction ID of the active transaction; OP_TABLEID: The table ID of the operation table in the database, used to locate the operation table where the operation is stored; OP_SAVEID: The storage number of the active transaction in the operation table, which, together with the operation number in the transaction, forms a key to identify its position in the operation table; Checkpoint transaction operation table D: Create a table named D_LSN values, with columns including OP_SAVEID, OP_ID, and OP, and a clustered composite primary key of (OP_SAVEID, OP_ID); the corresponding SQL statement is: CREATE TABLE D_LSN values(OP_SAVEIDBIGINT, OP_ID BIGINT, OP BLOB, CLUSTER PRIMARY KEY(OP_SAVEID, OP_ID)); its column definitions are explained below: OP_SAVEID: The storage number of the transaction in the operation table. It is combined with the operation number OP_ID in the transaction operation to form a key to identify its position in the operation table; OP_ID: The operation number in the transaction operation; OP: The specific log operation content corresponding to the transaction; Among them, CLUSTER PRIMARY KEY(OP_SAVEID, OP_ID) is a clustered composite primary key: OP_SAVEID and OP_ID are used as composite primary keys, and physical storage is sorted according to this composite primary key.

5. The method for log parsing and synchronous transaction storage according to claim 4, characterized in that, The collection of active transactions is then assigned an operation table ID and a transaction save ID. Collect the set of committed transactions and assign operation table IDs and transaction save IDs, specifically including: Collect active transactions by traversing the active transaction list using the checkpoint LSN, and collecting all transactions whose starting LSN is less than or equal to the checkpoint LSN into the active transaction set; traverse the committed transaction list using the checkpoint LSN, and collect all transactions whose starting LSN is less than or equal to the checkpoint LSN and whose committed LSN is greater than the checkpoint LSN into the active transaction set; after collection, deduplicate transactions in the active transaction set are removed. Assign a unique OP_SAVEID and the table ID of the current transaction operation table D in the database to OP_TABLEID for each transaction in the active transaction set. OP_SAVEID will be used to identify the position of the transaction operation in the transaction operation table D, while OP_TABLEID is a pointer to the transaction operation table where the current transaction operation is stored. Collect committed transactions by traversing the committed transaction list using the checkpoint LSN and collecting transactions whose commit LSN is less than or equal to the checkpoint LSN into the committed transaction set. Assign a unique OP_SAVEID and the table ID of the current transaction operation table D in the database to each transaction in the committed transaction set to OP_TABLEID. OP_SAVEID will be used to identify the position of the transaction operation in the transaction operation table D, while OP_TABLEID is a pointer to the transaction operation table where the current transaction operation is stored.

6. The method for log parsing and synchronous transaction storage according to claim 5, characterized in that, The committed transaction operations are saved to the transaction operation table; Saving active transaction operations to the transaction operation table specifically includes: Retrieve transactions sequentially from the committed transaction set, determine the transaction operation table where the transaction operations are stored from the transaction's OP_TABLEID, store the transaction operations into the transaction operation table, and immediately release the memory space occupied by these operations. Transactions are extracted sequentially from the active transaction set. The transaction operation table where the transaction operations are stored is determined from the transaction's OP_TABLEID. Operations in the transaction whose LSN is less than or equal to the checkpoint LSN are stored in the transaction operation table, and the memory space occupied by these operations is immediately released.

7. The method for log parsing and synchronous transaction storage according to claim 6, characterized in that, The committed transaction information is saved to the transaction information table; Saving active transaction information to the active transaction table specifically includes: Save committed transaction information to checkpoint transaction information table C: According to the definition of checkpoint transaction information table, the information saved for a transaction includes transaction commit LSN, transaction ID, the transaction's specified checkpoint operation table ID, and the transaction's OP_SAVEID in the transaction operation table. The information is saved in order of transaction commit LSN size. Save active transaction information to the active transaction table: Select the table with the smaller checkpoint LSN from the two active transaction tables A as the table to be saved this time. First, clear the historical records in the table, and then save the active transactions filtered by this checkpoint to the table.

8. The method for log parsing and synchronous transaction storage according to claim 7, characterized in that, When the target data synchronization service fails and restarts: Load two active transaction tables and sort them by the LSN value in the active transaction table name. At this point, the active transaction table with the larger LSN is the last valid checkpoint, and its corresponding LSN is used as the recovery point. After reading the active transaction information from the active transaction table with the largest LSN and restoring it to the active transaction linked list, the system continues to receive transaction operations sent by the source end. It compares the LSN of the operation with the LSN of the recovery point and only accepts operations whose LSN is greater than the LSN of the recovery point, thus realizing the fault recovery function.

9. A device for log parsing and synchronous transaction storage, characterized in that: The method includes at least one processor and a memory, which are connected via a data bus. The memory stores instructions that can be executed by the at least one processor. After being executed by the processor, the instructions are used to complete the log parsing synchronization transaction storage method according to any one of claims 1-8.

Images