A method, apparatus, and system for setting transaction operation dependencies based on transaction state.

Abstract

This invention relates to a method, apparatus, and system for setting transaction operation dependencies based on transaction status. The method mainly includes: after the target-side data synchronization service starts, it initializes a hash table Q for registering transaction information; after receiving a DML operation from the source side, the target-side data synchronization service extracts the transaction ID of the operation and registers it in hash table Q, extracts the table ID and its primary key, index, or custom selection column as the KEY, and then extracts the transaction ID of the operation as the TRXID. The KEY and TRXID are combined into a record and saved to the current file. If this record is the first record in the current file, the log LSN corresponding to the operation is used as the starting LSN of the current file; the conflicting TRXID and conflicting LSN of the operation are determined based on the KEY, TRXID, starting LSN, and the transaction status in hash table Q. This invention can effectively increase the concurrency of operation execution, thereby improving synchronization performance.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus and system for setting transaction operation dependencies based on transaction status. Background Technology

[0002] In a real-time database synchronization system based on log parsing architecture (supporting heterogeneous databases), the source-side data synchronization service is responsible for capturing the operation logs of the source database, parsing the logs to restore the corresponding database operations, and then sending them to the target-side data synchronization service. The target-side data synchronization service is responsible for executing these operations in the target database, thereby completing the data synchronization process.

[0003] When a target-side data synchronization service performs synchronization operations on a target database, it often needs to execute according to the transaction commit order. This is because if the previous transaction hasn't completed synchronization, the next transaction's operation cannot be executed concurrently with previous transactions and its data entry cannot proceed until all transactions preceding the current operation have completed their commits. This significantly impacts the concurrency of transaction data entry. Currently, an effective method is to determine whether parallel execution is possible by comparing the log LSN of the current operation with the LSN of a previously committed transaction. If the LSN of the currently executing transaction is smaller than the LSN of another currently executing transaction, then this operation can be executed concurrently with that transaction. While this method of parallel execution based on transaction commit order improves performance, the granularity of conflict detection is too large and not fine enough, resulting in low parallelism and a small performance improvement.

[0004] In view of this, how to overcome the shortcomings of existing technologies and how to improve the parallel execution of target-side data synchronization services to enhance data synchronization performance have become important technical problems that the industry urgently needs to solve. Summary of the Invention

[0005] Addressing the shortcomings or improvement needs of existing technologies, this invention aims to enhance the parallel execution of target-side data synchronization services to improve data synchronization performance. The invention proposes that by determining the dependencies between transaction operations and clearly identifying which transactions need to be waited for during synchronization, the parallelism of synchronization can be maximized. Based on this, the invention provides a method, apparatus, and system for setting transaction operation dependencies based on transaction states. In this method, the target-side data synchronization service receives synchronization transaction operations in the order of generation of the source database logs. The order of each received operation forms the basis for detecting conflicts. In this embodiment, based on the order of operation reception, the primary key, index, or custom selection column of each received operation is extracted as a KEY. It then checks whether this KEY, within a set range, is the same as the KEY of other previously received transaction operations. If so, the execution of this operation must wait for the conflicting transaction to commit before it can begin; otherwise, it can be executed concurrently with transactions that committed earlier, maximizing the parallelism of transaction execution and effectively improving synchronization performance.

[0006] The present invention adopts the following technical solution:

[0007] In a first aspect, the present invention provides a method for setting transaction operation dependencies based on transaction state, including:

[0008] After the target data synchronization service starts, it initializes a hash table Q for registering transaction information, which is used to store the status of all transactions within the scope of the receive collision detection LSN;

[0009] After the target data synchronization service receives the DML operation from the source, it extracts the transaction ID of the operation and registers it in the hash table Q. The transaction status is active. It also extracts the table ID and its primary key, index, or custom selection column as the KEY, and then extracts the transaction ID of the operation as the TRXID. The KEY and TRXID are combined into a record and saved to the current file. If the record is the first record in the current file, the log LSN corresponding to the operation is used as the starting LSN of the current file.

[0010] The conflicting TRXID and conflicting LSN of the operation are determined based on the KEY, TRXID, starting LSN, and the state of the transaction in hash table Q.

[0011] When the target data synchronization service receives a commit operation, it adds the corresponding transaction to the pending transaction queue and modifies the transaction status in hash table Q from active to committed. When the target data synchronization service receives a rollback operation, it discards the transaction and modifies the transaction status in hash table Q from active to rollback.

[0012] In some embodiments, after the target data synchronization service starts, it initializes a hash table R and initializes the record addresses in the hash entries of hash table R to be empty, which is used to store and locate the received operations; the target data synchronization service creates two files to store the keys in hash table R, the file names of each file are named in ascending order of creation, and these two files are added to the cache file linked list.

[0013] In some embodiments, determining the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID, and starting LSN specifically includes:

[0014] Construct a detection record X using the KEY of the current operation, and merge the detection record X into the hash table R;

[0015] During the merging process, conflict detection is performed. If a conflicting key is found, the TRXID value corresponding to the conflicting key is returned; otherwise, 0 is returned as the corresponding TRXID, indicating that there is no conflict.

[0016] Set the TRXID obtained from the conflict detection as the conflict TRXID, and write the starting LSN of the first file in the cache file linked list corresponding to the current hash table R into the current operation and set it as the conflict LSN.

[0017] In some embodiments, the conflict detection judgment specifically includes:

[0018] Calculate the hash value of the KEY of the current detection record X, and locate the hash entry using the hash value; determine whether the record address in the hash entry is empty. If so, set the record address in the hash entry to the next address space of the current detection record X, fill the hash entry with the address of the detection record X, and return 0 as the conflict TRXID, indicating that no conflict was detected; otherwise, proceed to the next conflict detection and judgment step.

[0019] Determine if the file ID at the address recorded in the hash entry is less than the ID value of the first file in the cache file list. If so, it means that this record Y has exceeded the inspection range. Set the address of the record in the hash entry to the next address space of the current detection record X, fill the hash entry with the address of the detection record X, and return 0 as the conflict TRXID, indicating that no conflict was detected. If not, proceed to the next conflict detection and judgment.

[0020] Load the corresponding record Y from the file based on the address recorded in the hash entry. Determine if the KEY of record Y is equal to the KEY of the currently detected record X and if the TRXID of record Y is not equal to the TRXID of the current operation. If so, it indicates that there is a conflict in the operation. Search the hash table Q for the detected TRXID to obtain the status of the conflicting transaction and return the conflicting TRXID based on the status of the conflicting transaction. Otherwise, return 0 as the conflicting TRXID to indicate that no conflict was detected.

[0021] Iterate through the next record of record Y and perform conflict detection and judgment until the record linked list ends.

[0022] In some embodiments, the step of searching the detected TRXID in hash table Q to obtain the status of the conflicting transaction, and returning the conflicting TRXID based on the status of the conflicting transaction, specifically includes:

[0023] If the conflicting transaction is active, return -1, indicating that it may conflict with transactions whose committed LSN is less than that of the current operation.

[0024] If the conflicting transaction is in a rollback state, ignore the transaction and continue searching for other conflicting transactions in the current hash entry. If a conflicting transaction is located, continue to process it according to its status; otherwise, return 0 as the conflicting TRXID to indicate that no conflict was detected.

[0025] If the conflicting transaction is in a committed state, the conflicting transaction ID is returned as the conflicting TRXID.

[0026] In some embodiments, the cached file list includes two files. When the first file is full, the process switches to the second file. When the second file is full, the first file in the cached file list is first removed, and then a new file is created and added to the cached file list by incrementing the file ID.

[0027] In some embodiments, when the target data synchronization service receives a partial rollback operation, it writes the conflicting TRXIDs in the rolled-back operation back to the hash table R according to the KEY saved in the operation.

[0028] Secondly, the present invention also provides an apparatus for setting transaction operation dependencies based on transaction state, the apparatus comprising:

[0029] At least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the processor for performing the method of setting transaction operation dependencies based on transaction state as described in the first aspect.

[0030] Thirdly, the present invention also provides a system for setting transaction operation dependencies based on transaction state, using the method for setting transaction operation dependencies based on transaction state as described in the first aspect. The system includes a transaction state module, an operation receiving module, a transaction state updating module, and a conflict information acquisition module, wherein:

[0031] The transaction status module is used to initialize a hash table Q for registering transaction information after the target data synchronization service is started, and to store the status of all transactions within the range of the received conflict detection LSN.

[0032] The operation receiving module is used to extract the transaction ID of the operation and register it in the hash table Q after the target data synchronization service receives the DML operation from the source end. The transaction status is active. The table ID and its primary key or index or custom selection column in the operation are extracted as KEY. The transaction ID in the operation is then extracted as TRXID. The KEY and TRXID are combined into a record and saved to the current file. If the record is the first record in the current file, the log LSN corresponding to the operation is used as the starting LSN of the current file.

[0033] The transaction status update module is used to add the corresponding transaction to the pending transaction queue and modify the transaction status in hash table Q from active to committed when the target data synchronization service receives a commit operation; and to discard the transaction and modify the transaction status in hash table Q from active to rolledback when the target data synchronization service receives a rollback operation.

[0034] The conflict information acquisition module is used to determine the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID, starting LSN, and the state of the transaction in hash table Q.

[0035] Fourthly, the present invention also provides a non-volatile computer storage medium storing computer-executable instructions that are executed by one or more processors to perform the method for setting transaction operation dependencies based on transaction state as described in the first aspect.

[0036] Compared with the prior art, the present invention provides a method, apparatus and system for setting transaction operation dependencies based on transaction state, the advantages of which are:

[0037] First, the target-side data synchronization service receives operations from the source side in a sequential manner. Conflicting operations are executed sequentially, with the first received operation being executed first, followed by the last. This order forms the basis for conflict detection. The currently received operation can be compared with previously received operations. If there are transactions with the same table and the same key, it indicates a conflict between the current operation and a previous transaction. When comparing the current operation with previously received operations, a range must be defined. A larger range provides higher accuracy but also consumes more resources. The size of the detection range needs to be determined based on the current hardware conditions. The method provided in this embodiment of the invention performs conflict detection on the currently received operation and previously received operations within a defined limited range. If the table ID and the key composed of the primary key, index, or custom selection column combination involved in the two operations are the same, it indicates that the current operation has a conflicting transaction within the defined range. The current operation must wait for the conflicting transaction to be executed before it can proceed.

[0038] Secondly, the target-side data synchronization service needs to maintain the state of each received transaction during conflict detection, marking the corresponding transactions. If a transaction is rolled back, it should be skipped during conflict detection because rolled-back transactions are discarded upon receipt and not executed; they are not registered during execution. Operations cannot depend on a rolled-back transaction; otherwise, subsequent transactions that depend on it will execute prematurely because they cannot find the dependent transaction. Operations that depend on active transactions need to be marked separately, indicating that the operation's rows are not unique, causing the operation to depend on a transaction with an uncertain state. In this case, execution can only begin after the transaction with a commit LSN less than the transaction to which the operation belongs has completed.

[0039] This invention maintains the state of each received transaction. Its main purpose is to skip transactions in a rollback state during operation conflict detection, ensuring that operations have explicit dependencies on other transactions. If the transaction state is not marked, even if an operation depends on a rollback transaction, that rollback transaction still needs to be placed in the execution queue. Since operations on rollback transactions are not executed, the release of a rollback transaction requires waiting for all transactions with a commit LSN less than the rollback LSN to complete, which impacts overall synchronization performance. Furthermore, if a scheme is adopted to collect TRXIDs of all operation dependencies in a rollback transaction, the transaction needs to be traversed for collection upon receiving a rollback operation. If there are many rolledback transactions, this affects the performance of the target data synchronization reception operation. This invention effectively solves the shortcomings of the above two methods. Upon receiving a rollback operation, it only needs to mark the transaction as a rollback transaction, and transactions in a rollback state are skipped during operation conflict detection, thereby improving synchronization performance. Attached Figure Description

[0040] 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.

[0041] Figure 1 This is a flowchart of a method for setting transaction operation dependencies based on transaction status, provided in Embodiment 1 of the present invention;

[0042] Figure 2 This is a flowchart of determining the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID and the starting LSN provided in Embodiment 1 of the present invention;

[0043] Figure 3 This is a flowchart of the conflict detection and judgment process provided in Embodiment 1 of the present invention;

[0044] Figure 4 A flowchart for returning conflicting TRXIDs based on the conflicting transaction status provided in Embodiment 1 of the present invention;

[0045] Figure 5 This is a schematic diagram of a system for setting transaction operation dependencies based on transaction status, as provided in Embodiment 1 of the present invention.

[0046] Figure 6 This is a schematic diagram of a device structure for setting transaction operation dependencies based on transaction status, as provided in Embodiment 3 of the present invention. Detailed Implementation

[0047] The present invention will now be described in detail with reference to specific embodiments. These embodiments will help those skilled in the art to further understand the present invention, but do not limit the invention in any way. It should be noted that those skilled in the art can make several modifications and improvements without departing from the concept of the present invention. These all fall within the protection scope of the present invention. It should be noted that, unless otherwise specified, the various features in the embodiments of the present invention can be combined with each other, all within the protection scope of this application. Furthermore, although functional modules may be divided in the device schematic diagram, and a logical order may be shown in the flowchart, in some cases, the steps shown or described may be executed differently from the module division in the device or the order in the flowchart.

[0048] Unless otherwise defined, all technical and scientific terms used in this specification have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains. The terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the invention. Furthermore, the technical features involved in the various embodiments of the invention described below can be combined with each other as long as they do not conflict with each other.

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

[0050] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application. The invention will now be described in detail with reference to the accompanying drawings and embodiments.

[0051] Example 1:

[0052] like Figure 1 As shown in the figure, this embodiment of the invention provides a method for setting transaction operation dependencies based on transaction status, the method including the following steps.

[0053] Step 100: After the target data synchronization service starts, it initializes a hash table Q to register transaction information, which is used to store the status of all transactions within the scope of the received conflict detection LSN. This hash table Q stores the transaction status, including active status, committed status, and rollback status. Active status: The transaction has not yet received a commit or rollback operation at the current detection time. Committed status: The transaction has received a commit operation at the current detection time. Rollback status: The transaction has received a rollback operation at the current detection time.

[0054] It should be noted that in this embodiment, LSN is the log sequence number; TRXID is the ID that identifies a transaction; DML refers to data insertion, update, and deletion operations; and KEY is the key that can be used to identify a specific row record in the table.

[0055] Step 200: After receiving the DML operation from the source end, the target data synchronization service extracts the transaction ID of the operation and registers it in the hash table Q. The transaction status is active. It also extracts the table ID and its primary key, index, or custom selection column as the KEY. Then, it extracts the transaction ID of the operation as the TRXID. The KEY and TRXID are combined into a record and saved to the current file. If this record is the first record in the current file, the log LSN corresponding to the operation is used as the starting LSN of the current file.

[0056] Step 300: Determine the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID, starting LSN, and the transaction status in hash table Q. Specifically, construct a detection record X using the KEY of the current operation, and merge the detection record X into hash table R; during the merging process, perform conflict detection and judgment. If there is a conflicting KEY, return the TRXID value corresponding to the conflicting KEY; otherwise, return 0 as the corresponding TRXID, indicating no conflict; set the TRXID obtained from the conflict detection judgment as the conflicting TRXID, and write the starting LSN of the first file in the cache file linked list corresponding to the current hash table R into the current operation, setting it as the conflicting LSN.

[0057] Step 400: When the target data synchronization service receives a commit operation, it adds the corresponding transaction to the pending transaction queue and modifies the transaction status in hash table Q from active to committed. When the target data synchronization service receives a rollback operation, it discards the transaction and modifies the transaction status in hash table Q from active to rollback.

[0058] In this embodiment of the invention, when receiving a transaction operation, the table ID of the operation is extracted, and the primary key, index, or custom selection column of the operation is used as the KEY. The same KEY is searched for in other previously received transaction operations within a set range to determine if there is a conflict. Conflicting operations need to wait for the conflicting transactions to be committed before they can start execution; non-conflicting transactions can be executed in parallel with the transactions committed before this operation was received, maximizing the performance of data insertion.

[0059] The following embodiment will provide a more detailed description of the above method.

[0060] In one embodiment, a synchronization system needs to be deployed in both the source and destination databases. The source database synchronization system reads logs from the source database, while the destination database synchronization system is responsible for classifying and collecting the synchronization operations sent from the source database by transaction and setting the dependency relationship between each received operation and any conflicting transactions received before it.

[0061] In one embodiment, after the target data synchronization service starts, it needs to initialize a hash table R of size N and initialize the record addresses in the hash entries to be empty, which is used to store and locate the received operations; and create two files of size M to store the keys in hash table R. The file names are named in ascending order of creation, and these two files are added to the cache file linked list.

[0062] For hash table R, the structure of a hash entry in its hash table is a 4-byte file ID, denoted as K; and a 4-byte file offset, denoted as F. K and F together form a hash entry in the hash table, as shown in the table below:

[0063]

[0064] This indicates that the first key in the hash entry is stored in the file with ID K, and its offset in that file is F.

[0065] In one embodiment, after the target data synchronization service starts, it also needs to initialize a hash table Q for registering transaction information, which is used to store the commit status of all transactions within the scope of the received conflict detection LSN. The hash table Q stores the transaction status in three ways: Active: The transaction has not yet received a commit or rollback operation at the current detection time. Committed: The transaction has received a commit operation at the current detection time. Rollback: The transaction has received a rollback operation at the current detection time.

[0066] In one embodiment, after the target data synchronization service receives the DML operation from the source, it extracts the transaction ID of the operation and registers it in hash table Q, and the transaction status is active.

[0067] In one embodiment, the target data synchronization service also extracts the table ID and its primary key, index, or custom selection column from the operation as the KEY, and then extracts the transaction ID from the operation as the TRXID. The KEY and TRXID are combined into a record and saved at offset F in the cache file K. If the record is the first record in the current file, the log LSN corresponding to the operation is marked on the file as the starting LSN of the file, and the LSN is saved in the file name. The complete cache file name format is "file ID_starting LSN.dat".

[0068] Since hash records need to store the address of the next record, the records saved to the file also need to reserve the address for storing the next record, as shown in the table below.

[0069]

[0070] refer to Figure 2As shown, in one embodiment, determining the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID, and starting LSN specifically includes the following steps.

[0071] Step 101: Construct a detection record X using the KEY of the current operation, and merge the detection record X into the hash table R.

[0072] Step 102: During the merging process, perform conflict detection and judgment. If there is a conflicting key, return the TRXID value corresponding to the conflicting key; otherwise, return 0 as the corresponding TRXID, indicating that there is no conflict.

[0073] Step 103: Set the TRXID obtained from the conflict detection as the conflict TRXID, and write the starting LSN of the first file in the cache file linked list corresponding to the current hash table R into the current operation, setting it as the conflict LSN. This obtains the conflict information for this operation. It should be noted that during execution, this operation searches among all transactions whose commit operation LSNs are less than the LSN of this operation's log for transactions involving the same table ID, and where the LSN of the currently executing commit operation is less than the conflict LSN of this operation (indicating that operations within this transaction with LSNs less than the conflict LSN are not within the scope of conflict checking, and it cannot be determined whether a conflict exists). If such a transaction exists, this operation must wait for that transaction to complete before execution; otherwise, it continues to search among these transactions for transactions with a transaction ID equal to the conflict TRXID. If such a transaction exists, this operation must wait for that transaction to complete before execution; otherwise, the operation can be executed immediately.

[0074] Based on the above steps, the target data synchronization service receives the next message and saves it to file K in the manner described above. The cached file list includes two files. When the first file is full (the written data exceeds the set size M), the system switches to the second file. When the second file is full, the first file in the list is first evicted, and then a new file is created and added to the list by incrementing the file ID in the system. When a record is saved to a new file, if it is the first record in that file, the operation LSN corresponding to that record is marked as the starting LSN of that file.

[0075] refer to Figure 3 As shown, in one embodiment, the conflict detection and judgment in step 102 above specifically includes the following steps.

[0076] Step 201: Calculate the hash value of the KEY of the current detection record X, and locate the hash item through the hash value; determine whether the record address in the hash item is empty. If so, set the record address in the hash item to the next address space of the current detection record X, fill the hash item with the address of the detection record X, and return 0 as the conflict TRXID, indicating that no conflict was detected; if not, proceed to the next conflict detection judgment.

[0077] Step 202: Determine whether the file ID K in the address recorded in the hash entry is less than the file ID value of the first file in the cache file list. If so, it means that this record Y has exceeded the inspection range. Set the record address in the hash entry to the next address space of the current detection record X, fill the hash entry with the address of the detection record X, and return 0 as the conflict TRXID, indicating that no conflict was detected. If not, proceed to the next conflict detection and judgment.

[0078] Step 203: Load the corresponding record Y from the file based on the address recorded in the hash item. Determine whether the KEY of record Y is equal to the KEY of the currently detected record X and whether the TRXID of record Y is not equal to the TRXID of the current operation. If so, it means that there is a conflict in the operation. Search the hash table Q for the detected conflict TRXID to obtain the status of the conflicting transaction and return the conflict TRXID according to the status of the conflicting transaction. Otherwise, return 0 as the conflict TRXID to indicate that no conflict was detected.

[0079] Step 204: Iterate through the next record of record Y and perform conflict detection and judgment until the record linked list ends.

[0080] refer to Figure 4 As shown, in one embodiment, step 203 above, which returns the conflicting TRXID based on the conflicting transaction status, specifically includes the following steps.

[0081] Step 301: If the conflicting transaction is active, return -1, indicating that it may conflict with any transaction whose committed LSN is less than the transaction to which the current operation belongs. It should be noted that when the conflicting transaction detected by the operation is in an active state, it means that duplicate data exists in the source database. In this case, the final state of the dependent active transaction is unknown; it could be a rollback transaction or a committed transaction. Therefore, the execution order of the current operation cannot be determined, and the operation must wait until all transactions with committed LSNs less than the transaction to which the current operation belongs have completed before it can begin execution.

[0082] Step 302: If the conflicting transaction is in a rollback state, ignore the transaction and continue searching for other conflicting transactions in the current hash item. If a conflicting transaction is located, continue to process it according to the conflicting transaction status; otherwise, return 0 as the conflicting TRXID, indicating that no conflict was detected, and continue to traverse the next record of record Y in sequence to perform conflict detection in the above manner until the record linked list ends.

[0083] Step 303: The conflicting transaction is in the committed state. Return the conflicting transaction ID as the conflicting TRXID.

[0084] In one embodiment, the target data synchronization service receives a commit operation, adds the transaction to the queue of pending transactions, and modifies the transaction status in hash table Q from active to committed. Since only committed transactions are executed and written to the database, and during operation conflict detection, the transactions that the operation depends on must also be committed, the transaction status needs to be explicitly set to committed in hash table Q after receiving the transaction commit operation.

[0085] In one embodiment, when the target data synchronization service receives a rollback operation, it discards the transaction and modifies the transaction status in hash table Q from active to rollback. It should be noted that when a received transaction is rolled back, since the rolled-back transaction operation is not executed during the database write-in process, no committed transaction operation is allowed to depend on a rolled-back transaction. Otherwise, during execution, the dependent conflicting transaction might not be found, leading to the assumption that there is no conflict and causing subsequent transactions to execute prematurely, resulting in errors. For example, if transactions A, B, and C all modify the same row in the same table, but transaction B rolls back, the target data synchronization service will establish a dependency order of B depending on A and C depending on B during the operation conflict detection process. Since transaction B is discarded due to the rollback, transaction C will not be able to find its dependent transaction B and will execute prematurely. Ultimately, transactions A and C will execute concurrently, leading to an error. Therefore, after a transaction receives a rollback operation, it needs to explicitly mark the transaction status as rollback in the hash table Q. If a subsequent operation is found to conflict with the rollback transaction during conflict detection, the rollback transaction should be ignored, and the search should continue for conflicting transactions with a committed status.

[0086] In one embodiment, when the target data synchronization service receives a partial rollback operation, it needs to write back the conflicting TRXIDs in the rolled-back operation to the hash table R based on the KEY saved in the operation.

[0087] The above describes the method for setting transaction operation dependencies based on transaction state. The following describes the workflow of the target data synchronization service after setting transaction operation dependencies.

[0088] In one embodiment, the target data synchronization service creates H worker threads to execute transactions in the order of their committed LSNs received, and then stores the transaction operations in the database. It should be noted that the target data synchronization service also needs to create a hash table P to register the currently executing transaction information (transaction ID) and a hash table G to register the table information and the starting LSN of the current transaction.

[0089] In one embodiment, a worker thread retrieves a transaction from the committed transaction list in the order of transaction commits, registers the transaction ID in hash table P, and registers the table information involved in the transaction and the starting LSN of the transaction in hash table G.

[0090] In one embodiment, a worker thread extracts an operation from a transaction, determines whether the current operation is a commit operation, and if so, commits the current transaction, wakes up the worker threads waiting for it, removes the registration information of the current transaction from hash table P and hash table G, and completes the execution of the current transaction; otherwise, the operation is a DML operation, and proceeds to the next step.

[0091] In one embodiment, the worker thread executes the operation using the conflicting TRXID of the current operation and by searching for a transaction with the conflicting TRXID in hash table P. In this step, if the conflicting TRXID is 0, it indicates that the operation has no conflict, and the process jumps to the next step of checking the LSN. If the conflicting TRXID is -1, it indicates that the operation depends on a transaction with an unknown transaction status. The execution of this operation needs to wait for transactions with a committed LSN less than the current transaction and whose operations involve the same table information to complete before it can proceed. The process then retrieves the table information involved in the operation, searches hash table G for transactions with a committed LSN less than the current transaction and whose operations involve the same table information. If found, the process is suspended, waiting for these transactions to complete before resuming execution. If the conflicting TRXID is any other value, the process searches hash table P for a transaction with the conflicting TRXID. If found, the current execution process is suspended, waiting for the conflicting transaction to complete and commit before resuming execution; otherwise, the process jumps to the next step of checking the LSN. It should be noted that if there is a conflicting TRXID transaction in hash table P, it means that the data involved in the current operation conflicts with the conflicting TRXID transaction. The current operation cannot be executed in parallel with the conflicting transaction and can only start execution after the conflicting transaction is committed.

[0092] In one embodiment, the worker thread uses the information from the current pending operation table to search in hash table G for a transaction that operates on the same table, where the commit LSN of this transaction is less than the current transaction's, and the starting LSN of the transaction is less than the conflict LSN of the current operation. If such a transaction exists, the current execution process is suspended, waiting for the conflicting transaction to complete and commit before waking up. Otherwise, it is determined that the conflict detection for the worker thread's current pending operation has not detected a conflict, so the current operation is executed, and then the next operation continues. It should be noted that this step mainly checks whether there are any concurrently executing transactions that exceed the conflict check LSN range. This is because the conflicting transaction information for the pending operation has a valid range: the conflicting transaction information is only valid within the range from the conflicting LSN saved in the operation to the current operation's LSN. The operation does not perform conflict detection on operations before the conflicting LSN. In other words, when there is a transaction whose commit LSN is less than the commit LSN of the transaction to which the current pending operation belongs, and whose starting LSN is less than the conflicting LSN saved in the current pending operation, it is unknown whether there is a conflict between the operations in this transaction and the current pending operation. Therefore, the current pending operation cannot be executed rashly and must wait for the transaction to commit before execution.

[0093] Based on the above process, this embodiment proposes a method for setting transaction operation dependencies based on transaction status. The main method is to perform conflict detection on the currently received operation and the previously received operation within a set limited range. If the table ID and the KEY composed of the primary key, index or custom selection column involved in the two operations are the same, it indicates that there is a conflicting transaction in the current operation within the limited range. The operation can only be executed after the conflicting transaction is executed.

[0094] refer to Figure 5As shown, this embodiment also provides a system for setting transaction operation dependencies based on transaction status. Applying the aforementioned method for setting transaction operation dependencies based on transaction status, the system includes a transaction status module, an operation receiving module, a transaction status updating module, and a conflict information acquisition module. Specifically: the transaction status module initializes a hash table Q for registering transaction information after the target-side data synchronization service starts, storing the status of all transactions within the received conflict detection LSN range; the operation receiving module, after the target-side data synchronization service receives a DML operation from the source end, extracts the transaction ID of the operation, registers it in the hash table Q, sets the transaction status to active, and extracts the table ID and its primary key, index, or custom selection column as the KEY, and then extracts the transaction information from the operation. The ID is used as the TRXID. The KEY and TRXID are combined into a record and saved to the current file. If this record is the first record in the current file, the log LSN corresponding to this operation is used as the starting LSN of the current file. The transaction status update module is used to add the corresponding transaction to the pending transaction queue and modify the transaction status in hash table Q from active to committed when the target data synchronization service receives a commit operation. When the target data synchronization service receives a rollback operation, the transaction is discarded and the transaction status in hash table Q is modified from active to rollback. The conflict information acquisition module is used to determine the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID, starting LSN, and the transaction status in hash table Q. The specific functions and principles of the system's functional modules correspond to the specific methods in the method section, and will not be elaborated here.

[0095] In summary, this embodiment proposes a method and system for setting transaction operation dependencies based on transaction status, which includes the following advantages: First, the target-end data synchronization service receives operations sent from the source end in a sequential manner. Conflicting operations are executed sequentially, with the earlier received operation being executed first, and the later received operation being executed later. This order is also the basis for conflict operation detection. The currently received operation can be compared with previously received operations. If there are transaction operations with the same table and the same key, it indicates that the current operation conflicts with a previous transaction. When comparing the current operation with previously received operations, a range must be set. The larger the range, the higher the accuracy, but the more resources are consumed. This needs to be determined based on the current hardware conditions. Using the method provided by this embodiment, the currently received operation is compared with previously received operations within a set limited range for conflict detection. If the table ID and the key composed of the primary key, index, or custom selection column combination involved in the two operations are the same, it indicates that the current operation has a conflicting transaction within the limited range. The current operation can only be executed after the conflicting transaction has been executed.

[0096] Secondly, the target-side data synchronization service needs to maintain the state of each received transaction during conflict detection, marking the corresponding transactions. If a transaction is rolled back, it should be skipped during conflict detection because rolled-back transactions are discarded upon receipt and not executed; they are not registered during execution. Operations cannot depend on a rolled-back transaction; otherwise, subsequent transactions that depend on it will execute prematurely because they cannot find the dependent transaction. Operations that depend on active transactions need to be marked separately, indicating that the operation's rows are not unique, causing the operation to depend on a transaction with an uncertain state. In this case, execution can only begin after the transaction with a commit LSN less than the transaction to which the operation belongs has completed.

[0097] This invention maintains the state of each received transaction. Its main purpose is to skip transactions in a rollback state during operation conflict detection, ensuring that operations have explicit dependencies on other transactions. If the transaction state is not marked, even if an operation depends on a rollback transaction, that rollback transaction still needs to be placed in the execution queue. Since operations on rollback transactions are not executed, the release of a rollback transaction requires waiting for all transactions with a commit LSN less than the rollback LSN to complete, which impacts overall synchronization performance. Furthermore, if a scheme is adopted to collect TRXIDs of all operation dependencies in a rollback transaction, the transaction needs to be traversed for collection upon receiving a rollback operation. If there are many rolledback transactions, this affects the performance of the target data synchronization reception operation. This invention effectively solves the shortcomings of the above two methods. Upon receiving a rollback operation, it only needs to mark the transaction as a rollback transaction, and transactions in a rollback state are skipped during operation conflict detection, thereby improving synchronization performance.

[0098] Example 2:

[0099] Based on the method for setting transaction operation dependencies based on transaction state provided in Embodiment 1 above, Embodiment 2 of the present invention provides a specific example for illustration.

[0100] For example: The source database has a table T (ID INT PRIMARY KEY, C1 INT);

[0101] The source application has three transactions performing the following operations on table T in the following order:

[0102] TRX1:INSERT INTO T (ID, C1) VALUES (1, 1);

[0103] TRX1:COMMIT;

[0104] TRX2:INSERT INTO T (ID, C1) VALUES (2, 2);

[0105] TRX2:UPDATE T SET C1=1 WHERE ID=1;

[0106] TRX2:ROLLBACK;

[0107] TRX3:UPDATE T SET C1=2 WHERE ID=1;

[0108] TRX3:COMMIT;

[0109] The order of the operations described above represents their order within the log stream, resulting in the following numbered table of the generated log stream:

[0110]

[0111] After the target data synchronization service starts, it needs to initialize a hash table R of size N and two cache files of size M. The cache files are numbered sequentially as 1 and 2.

[0112] The receiving process is as follows:

[0113] 1. The log receiving thread receives operations with LSN 1, extracts its table ID and primary key as KEY, assuming it is 1, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, expressed as [file ID (K): file offset (F)], and its offset in the file is [1:0].

[0114]

[0115] Since this KEY information is the first operation information of the current cached file, the LSN value of the operation should be set to the starting LSN of the file, which is set to 1.

[0116] Register the transaction with TRXID 1 in hash table Q and mark its status as active.

[0117] 2. Insert the KEY into hash table R. The returned TRXID is 0, which means there is no conflict. Write back the TRXID of 0 (which means there is no conflict) and the minimum starting LSN of the cached file (which is 1) to the operation.

[0118] 3. The log receiving thread receives commit operations with LSN of 2, marks the transaction with TRXID of 1 in hash table Q as committed, and adds the transaction to the pending execution queue.

[0119] 4. The log receiving thread receives the operation with LSN 3, extracts its table ID and primary key as KEY, assuming it is 2, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, with its offset in the file being [1:32].

[0120]

[0121] Register the transaction with TRXID 2 in hash table Q and mark its status as active.

[0122] 5. Insert the KEY into hash table R. The returned TRXID is 0, which means there is no conflict. Write back the TRXID of 0 (which means there is no conflict) and the minimum starting LSN of the cached file of 1 to the operation.

[0123] 6. The log receiving thread receives the operation with LSN 4, extracts its table ID and primary key as KEY, assuming it is 1, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, with its offset in the file being [1:64].

[0124]

[0125] 7. Insert the KEY into the hash table R. Since the operation information with KEY 1 already exists in the HASH, the returned conflicting TRXID is 1, which means that the current operation and the transaction with transaction ID 1 conflict.

[0126] 8. Determine the transaction status in hash table Q based on the conflicting TRXID of 1. The transaction status is committed. Write back the conflicting TRXID of 1 and the minimum starting LSN of the cached file of 1 to the operation.

[0127] 9. Set the address in hash table R with KEY 1 to the next record of the current KEY information, and get the following table.

[0128]

[0129] 10. The log receiving thread receives a rollback operation with LSN 5, marks the transaction with TRXID 2 in hash table Q as rollback, and then releases the transaction with TRXID 2.

[0130] 11. The log receiving thread receives the operation with LSN 6, extracts its table ID and primary key as KEY, assuming it is 3, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, with its offset in the file being [1:96].

[0131]

[0132] Register the transaction with TRXID 3 in hash table Q and mark its status as active.

[0133] 12. Insert the KEY into hash table R. The returned TRXID is 2, which means that the current operation conflicts with the transaction with transaction ID 2.

[0134] 13. Based on the conflicting TRXID of 2, determine the status of the transaction in hash table Q. The transaction status is rollback. We need to continue searching forward for the previous conflicting transaction. The returned TRXID is 1, which means that the current operation conflicts with the transaction with transaction ID 1.

[0135] 14. Determine the transaction status in hash table Q based on the conflicting TRXID of 1. The transaction status is committed. Write back the conflicting TRXID of 1 and the minimum starting LSN of the cached file of 1 to the operation.

[0136] 15. The log receiving thread receives a commit operation with LSN 7, marks the transaction with TRXID 3 in hash table Q as committed, and adds the transaction to the pending execution queue.

[0137] After all operation conflict detections are completed, the following detection results are generated.

[0138]

[0139] At this point, only TRX1 and TRX3 are in the queue to be executed. TRX2 was released directly because it was a rollback transaction.

[0140] 16. Create two worker threads and execute TRX1 and TRX3 respectively in the order of transaction commit.

[0141] 17. Worker thread 1 extracts the TRX1 transaction, registers the transaction information TRXID as 1 in hash table P, and registers the starting LSN of the transaction associated with table information T as 1 in hash table G.

[0142] 18. Worker thread 2 extracts transaction TRX3, registers the transaction information TRXID 3 in hash table P, and registers the starting LSN of the transaction associated with table information T, which is 6, in hash table G.

[0143] 19. Worker thread 1 extracts the insertion operations with LSN 1 on TRX1, obtains the conflict TRXID and conflict LSN of the operation, and performs conflict detection.

[0144] (1) The operation conflict TRXID is 0, indicating no conflict.

[0145] (2) Obtain the table information T and the conflict LSN of the operation is 1. Search in the hash table G for conflicting transactions that involve table T and have a commit LSN less than 2 and a minimum starting LSN of the transaction less than 1. If no conflicting transactions are found, the operation can be executed directly.

[0146] 20. Worker thread 2 extracts the update operation with LSN 6 on TRX3, obtains the conflicting TRXID and conflicting LSN of the operation, and performs conflict detection.

[0147] (1) The operation conflict TRXID is 1, which means that it conflicts with TRX1. The execution of the current working thread is suspended and it is woken up after TRX1 finishes execution.

[0148] As we can see here, TRX3 and TRX1 are separated by TRX2. However, since TRX2 itself is rolled back and will not be executed, TRX3 skips TRX2 directly during conflict detection and locates the conflicting transaction on the explicitly committed transaction TRX1. In this way, the update of TRX3 will not be executed concurrently with the insert operation of TRX1, ensuring the correctness of synchronization.

[0149] 21. After worker thread 1 executes the commit operation of TRX1, it deletes the current transaction information from hash table P and hash table G respectively, and then wakes up worker thread 2 to execute the TRX3 transaction.

[0150] 22. After worker thread 2 is awakened, it executes the update operation of TRX3, then executes the commit operation and deletes the current transaction information from hash table P and hash table G to complete the transaction execution.

[0151] As can be seen from the above process, since transaction TRX1 is received first, followed by the operation of transaction TRX2, TRX2 will find that its operation depends on transaction TRX1 during operation conflict detection. Although TRX2 finally receives a rollback operation, meaning that this transaction will not be executed, the operation on transaction TRX3, which is received last, depends on TRX2 instead of TRX1 during conflict detection. Therefore, TRX2 needs to be marked as a rollback transaction, and transactions in the rollback state should be skipped during conflict detection to ensure that TRX3 depends on TRX1 instead of the rollback TRX2 in the transaction dependency relationship, thereby ensuring the correctness of the synchronization order.

[0152] The above solution provides examples of activities and transactions as follows:

[0153] The source database has a table T (ID INT, C1 INT);

[0154] The source application has three transactions performing the following operations on table T in the following order:

[0155] TRX1:INSERT INTO T (ID, C1) VALUES (1, 1);

[0156] TRX1:COMMIT;

[0157] TRX2:INSERT INTO T (ID, C1) VALUES (1, 1);

[0158] TRX3:UPDATE T SET C1=2 WHERE ID=1;

[0159] TRX3:COMMIT;

[0160] TRX2:COMMIT;

[0161] The order of the operations described above represents their order within the log stream, resulting in the following numbered table of the generated log stream:

[0162]

[0163] After the target data synchronization service starts, it needs to initialize a hash table R of size N and two cache files of size M. The cache files are numbered sequentially as 1 and 2.

[0164] The receiving process is as follows:

[0165] 1. The log receiving thread receives operations with LSN of 1, extracts its table ID and ID column as KEY, assuming it is 1, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, with its offset in the file being [1:0].

[0166]

[0167] Since this KEY information is the first operation information of the current cached file, the LSN value of the operation should be set to the starting LSN of the file, which is set to 1.

[0168] Since table T has no primary key, the ID column is chosen as the column for calculating the key value, so unrelated rows may have key value conflicts.

[0169] Register the transaction with TRXID 1 in hash table Q and mark its status as active.

[0170] 2. Insert the KEY into hash table R. The returned TRXID is 0, which means there is no conflict. Write back the TRXID of 0 (which means there is no conflict) and the minimum starting LSN of the cached file (which is 1) to the operation.

[0171] 3. The log receiving thread receives commit operations with LSN of 2, marks the transaction with TRXID of 1 in hash table Q as committed, and adds the transaction to the pending execution queue.

[0172] 4. The log receiving thread receives the operation with LSN 3, extracts its table ID and ID column as KEY, assuming it is 1, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, with its offset in the file being [1:32].

[0173]

[0174] Register the transaction with TRXID 2 in hash table Q and mark its status as active.

[0175] 5. Insert the KEY into the hash table R. Since the operation information with KEY 1 already exists in the HASH, the returned conflicting TRXID is 1, which means that the current operation and the transaction with transaction ID 1 conflict.

[0176] 6. Determine the transaction status in hash table Q based on the conflicting TRXID of 1. The transaction status is committed. Write back the conflicting TRXID of 1 and the minimum starting LSN of the cached file of 1 to the operation.

[0177] 7. The log receiving thread receives the operation with LSN 4, extracts its table ID and ID column as KEY, assuming it is 1, and combines TRXID and the space of the next record to make the length 32. First, it stores its KEY and transaction ID in the first cache file, with its offset in the file being [1:64].

[0178]

[0179] Register the transaction with TRXID 3 in hash table Q and mark its status as active.

[0180] 8. Insert the KEY into the hash table R. Since the operation information with KEY 1 already exists in the HASH, the returned conflicting TRXID is 2, which means that the current operation and the transaction with transaction ID 2 conflict.

[0181] 9. Based on the conflicting TRXID of 2, determine the transaction status in hash table Q. The transaction status is active. At this point, TRX3 needs to be marked, and -1 should be returned as the conflicting TRXID, indicating that it must wait for all transactions with a commit LSN less than TRX3 to complete before it can begin execution. This is because TRX3 does not know whether its dependent TRX2 will eventually roll back or commit. If it commits, TRX2 might commit after TRX3, meaning the current operation would depend on a transaction that commits after it, thus violating the dependency logic.

[0182] 10. The log receiving thread receives a commit operation with LSN 5, marks the transaction with TRXID 3 in hash table Q as committed, and adds the transaction to the pending execution queue.

[0183] 11. The log receiving thread receives a commit operation with LSN 6, marks the transaction with TRXID 2 in hash table Q as committed, and adds the transaction to the pending execution queue.

[0184] After all operation conflict detections are completed, the following detection results are generated.

[0185]

[0186] 12. Create three worker threads and execute TRX1, TRX3 and TRX2 respectively in the order of transaction commit.

[0187] 13. Worker thread 1 extracts the TRX1 transaction, registers the transaction information TRXID as 1 in hash table P, and registers the starting LSN of the transaction associated with table information T as 1 in hash table G.

[0188] 14. Worker thread 2 extracts the TRX3 transaction, registers the transaction information TRXID 3 in hash table P, and registers the starting LSN of the transaction associated with table information T, which is 4, in hash table G.

[0189] 15. Worker thread 3 extracts the TRX2 transaction, registers the transaction information TRXID 2 in hash table P, and registers the starting LSN of the transaction associated with table information T, which is 3, in hash table G.

[0190] 16. Worker thread 1 extracts the insertion operations with LSN 1 on TRX1, obtains the conflicting TRXID and conflicting LSN of the operation, and performs conflict detection.

[0191] (1) The operation conflict TRXID is 0, indicating no conflict.

[0192] (2) Obtain the table information T1 and the conflict LSN is 1 in the operation. Search in the hash table G for conflicting transactions that involve table T1 and have a commit LSN less than 2 and a minimum starting LSN less than 1. If no conflicting transactions are found, the operation can be executed directly.

[0193] 17. Worker thread 2 retrieves the update operation with LSN 4 on TRX3, obtains the conflicting TRXID for this operation (which is -1), and needs to wait for all transactions with committed LSNs less than TRX3 to complete before it can begin execution. It suspends the execution of the current worker thread, waiting for TRX1 to finish before waking itself up.

[0194] As we can see here, the update operation of TRX3 relies on the active transaction TRX2 during conflict detection. However, since TRX2 is an active transaction during the detection, it is uncertain whether it will commit or rollback. Therefore, TRX3 needs to be marked to prevent it from depending on a transaction with an ambiguous state. It needs to wait for all transactions with a commit LSN less than TRX3 to complete before it can start execution. In this way, the update of TRX3 will not be executed concurrently with the insert operation of TRX1, ensuring the correctness of synchronization.

[0195] 18. Worker thread 3 extracts the insertion operation with LSN 3 on TRX2, obtains the conflicting TRXID of the operation, and the conflicting TRXID is 1. It needs to wait for worker thread 1 to finish executing TRX1 before being woken up.

[0196] 19. After worker thread 1 executes the commit operation of TRX1, it deletes the current transaction information from hash table P and hash table G respectively, and then wakes up worker thread 2 to execute the TRX3 transaction and worker thread 3 to execute the TRX2 transaction.

[0197] 20. After worker thread 2 is awakened, it executes the update operation of TRX3, then executes the commit operation and deletes the current transaction information from hash table P and hash table G to complete the transaction execution.

[0198] 21. After worker thread 3 is awakened, it executes the insert operation of TRX2, then executes the commit operation and deletes the current transaction information from hash table P and hash table G to complete the transaction execution.

[0199] As can be seen from the above process, transaction TRX1 is received first, followed by the operation of transaction TRX2. During the operation conflict detection, TRX2 will find that its operation depends on transaction TRX1. Next is the update operation of TRX3. Since there are duplicate rows in table T, the update operation of TRX3 will depend on TRX2 during the conflict detection. However, at this time, TRX2 cannot determine whether to commit or rollback. Therefore, the TRXID of the operation conflict of TRX3 is set as a special flag. During execution, it is ensured that all transactions with a commit LSN less than its have been executed before execution can begin, thus ensuring the correctness of the synchronization order.

[0200] Example 3:

[0201] It should be noted that for rollback transactions, since the rollback transaction will not be executed, there are three ways to handle the rollback transaction operation in the hash table R.

[0202] The first approach: This method does not roll back the impact of rollback transactions on operations dependent on them in hash table R, nor does it collect conflicting TRXIDs of rollback transaction dependencies. It directly releases all operations of the rollback transaction. This rollback transaction will be depended upon by other operations, and it will still be added to the pending execution list for execution to ensure the continuity of operation dependencies. When using this approach, the entire rollback transaction is released, which can improve the performance of handling rollback transactions. However, operations dependent on the rollback transaction can only execute after the rollback transaction is released in the pending execution list. Since conflicting transactions were not collected during the release operation, the rollback transaction can only be released after all transactions with commit LSNs or rollback LSNs less than its own rollback LSN have been executed, which affects synchronization efficiency. The corresponding approach is that when the target data synchronization service receives a rollback transaction, all operations on this transaction do not need to be executed; they are discarded directly, and only the retained transaction information is added to the pending execution queue. For example, when the target data synchronization service receives a rollback or partial rollback operation, it needs to locate the transaction based on the transaction ID of the operation, and then mark the transaction to indicate that some operations of the transaction have been rolled back. If the operation is a rollback operation, then all operations on this transaction do not need to be executed and are directly discarded. Only the retained transaction information is added to the queue to be executed. The transaction information of the rollback transaction cannot be discarded.

[0203] The second approach: While not reversing the impact of rollback transactions on operation dependencies in hash table R, it collects conflicting TRXIDs of rollback transaction dependencies, for example, by recording them in a conflicting TRXID dependency list L. This rollback transaction will be depended upon by other operations, and it will also be added to the pending execution list for execution to ensure the continuity of operation dependencies. When using this approach, conflicting TRXIDs in the operation must be collected before the rollback transaction is released. Although this reduces the processing performance of rollback transactions, it improves the release performance of rollback transactions during execution. Operations dependent on rollback transactions can only be executed after the rollback transaction is released in the pending execution list. However, since conflicting transactions that depend on the rollback transaction were collected during the release operation, the rollback transaction can be released only after these conflicting transactions have completed, without waiting for all transactions with commit LSNs or rollback LSNs less than its own rollback LSN to complete. Compared to the first approach, this partially improves synchronization efficiency. This approach corresponds to the following: when the target data synchronization service receives a rollback or partial rollback operation, it collects the conflicting TRXIDs saved in the rolled-back operation. Furthermore, for the rollback operation, the corresponding transaction cannot be discarded, but is added to the queue of pending transactions.

[0204] The third approach involves rolling back the impact of a transaction's operations on the dependencies of operations in hash table R. This rolled-back transaction will not be depended upon by other operations, and it does not need to be added to the pending execution list. When using this approach, after receiving a rollback operation, the transaction needs to roll back the impact of all operations within the transaction on the dependencies of operations in hash table R. Since the transaction size is uncontrollable, rolling back a large transaction will affect the progress of receiving operations, thus impacting synchronization efficiency. In this method, when the target data synchronization service receives a rollback or partial rollback operation, it writes the conflicting TRXIDs saved in the rolled-back operations back to hash table R.

[0205] The three schemes for handling transaction rollback each have their advantages and disadvantages. The first scheme is suitable for large-scale transaction rollbacks, the second scheme for medium-scale transaction rollbacks, and the third scheme for small-scale transaction rollbacks. Therefore, two parameters, W1 and W2, are set: W1 represents the minimum number of DML operations in a large-scale transaction, and W2 represents the minimum number of DML operations in a medium-scale transaction. Based on this, after receiving DML operations from the source end, the target-side data synchronization service needs to classify and manage the received DML operations by transaction and calculate the scale S of the DML operations received in each transaction (S is the number of DML operations received in a single transaction). When the target-side data synchronization service receives a rollback operation, it takes corresponding actions based on the scale S of the DML operations in the transaction.

[0206] When the size S of the rollback transaction is greater than or equal to W1, it corresponds to a large-scale transaction rollback in the first scheme. In this case, the impact of the rollback transaction's operations on the operation dependencies in the hash table R is not rolled back, and conflicting TRXIDs of the rollback transaction's operation dependencies are not collected. All operations of the rollback transaction are directly released. This rollback transaction will be depended on by other operations, and the rollback transaction will also be added to the pending execution list for execution to ensure the continuity of operation dependencies.

[0207] When the size S of the rollback transaction is greater than or equal to W2 and less than W1, it corresponds to a medium-sized transaction rollback in the second scheme. In this case, the operation of not rolling back the rollback transaction does not affect the operation dependencies in the hash table R. However, the conflicting TRXIDs of the rollback transaction operation dependencies are collected, and they can be recorded in the conflicting TRXID dependency list L. This rollback transaction will be depended on by other operations, and the rollback transaction should also be added to the pending execution list for execution to ensure the continuity of operation dependencies.

[0208] When the size S of the rollback transaction is less than W2, it corresponds to a small-scale transaction rollback in the third scheme. In this case, the rollback transaction operation does not affect the operation dependencies in the hash table R; the rollback transaction does not need to be added to the pending execution list and is directly released.

[0209] Furthermore, for partial rollback operations, due to the special nature of partial rollback, which inherently requires traversing all rollback operations, adding a process to collect conflicting TRXIDs from these operations does not significantly degrade the performance of handling partial rollbacks. Based on this, when the target data synchronization service receives a partial rollback operation, it needs to count the total number of rolled-back rows S1. Depending on the size of S1, the following processing is performed: When the number of rollback rows S1 is less than W2, the impact of the rollback operation on the operation dependencies in hash table R is rolled back, i.e., the conflicting TRXIDs saved in the rolled-back operations are written back to hash table R. When the number of rollback rows S1 is greater than or equal to W2, the impact of the rollback operation on the operation dependencies in hash table R is not rolled back, i.e., the conflicting TRXIDs saved in the rolled-back operations are not written back to hash table R, but the conflicting TRXIDs dependent on the rollback operation are collected and recorded in the conflicting TRXID dependency linked list L.

[0210] Based on the above settings, after the target data synchronization service creates a worker thread, the worker thread extracts a transaction from the committed transaction list in the order of transaction commits, and then determines whether the transaction is a rollback transaction. If the current transaction is a rollback transaction, it is necessary to determine whether the rollback transaction has a conflict with the TRXID list L.

[0211] If the rollback transaction does not conflict with the TRXID chain L, it needs to wait for all transactions with a commit LSN less than the current transaction to complete before waking up its waiting worker thread. Then, it removes the current transaction's registration information from hash tables P and G, completing the execution of the current transaction. This is because the operations on the rollback transaction do not need to be executed and are discarded, so the transaction information they depend on is unclear. Therefore, when committing, it must wait for all transactions that committed before it to commit to ensure the correctness of the transaction dependency order.

[0212] If a rollback transaction has a conflicting TRXID chain L, it needs to wait for all conflicting transactions in chain L to be committed before waking up its waiting worker thread. Then, it removes the current transaction's registration information from hash tables P and G, completing the execution of the current transaction. Specifically, it sequentially extracts a conflicting TRXID from chain L, checks if it exists in hash table P, and if it does, waits for that transaction to complete before waking up its own thread. After waking up, it extracts the next conflicting TRXID from chain L and performs the same check until all conflicting transactions in chain L are completed. This is because the operations on the rollback transaction do not need to be executed and are discarded. When discarding the rollback operations, the conflicting TRXIDs of the operations are collected, so their dependent transaction information is clear. Therefore, during release, it is only necessary to wait for these conflicting transactions to be committed to ensure the correctness of the transaction dependency order.

[0213] In summary, since rollback transactions are not executed, three conflict detection schemes arise, each with its own advantages. Appropriate values ​​need to be set to leverage the strengths of each scheme to improve overall synchronization performance. Furthermore, for partial rollback operations of committed transactions, specifically collecting the conflicting TRXIDs referenced during the rollback operation can effectively mitigate the performance loss caused by writing back to the conflict detection hash table R during partial rollback operations, thus improving overall synchronization performance. This embodiment handles rollback transactions and partial rollback operations according to a predetermined operational scale, seeking a balance between the costs of operation rollback and rollback transaction execution, thereby improving overall synchronization performance.

[0214] In some embodiments, the keys in hash table R can be stored either in files or in memory slices. Specifically, the difference lies in the storage method. While file storage reduces performance and impacts collision detection efficiency, the state of hash table R before the failure can be restored using the key values ​​in the stored files after a data synchronization failure. This ensures continuity in collision detection for received operations after the failure, preventing a precipitous drop in synchronization performance. Conversely, while memory slice storage significantly improves collision detection performance compared to file storage, it loses previously detected collision data after a failure. Recovery requires starting from the currently received LSN, leading to a discontinuity in subsequent collision detection and a sharp drop in synchronization performance at the failure point. When storage performance is high (e.g., using SSD fixed storage), the performance requirements for conflict detection can be met, and the efficiency of operation reception can be balanced with synchronous execution. In this case, storing the keys in hash table R through files can effectively avoid the loss of synchronization performance caused by failures. When storage performance affects the efficiency of conflict detection, the bottleneck of synchronization lies in operation reception. In this case, storing the keys in hash table R through memory slices can effectively improve the operation reception performance, thereby improving the overall synchronization performance. Embodiment 1 of this application is an example of storing the keys in hash table R through files.

[0215] In some embodiments, the MD5 value can be calculated at either the source or the target end as the key for the operation. Calculating the MD5 value for each operation by detecting conflicts in the source-end data synchronization service allows for more accurate conflict MD5 value calculation. However, if the calculation is performed by the target-end data synchronization service, the MD5 value calculated using the primary key information of the target database table will be inaccurate if the primary key structure or column order is inconsistent with the source table. Therefore, it is generally preferred to calculate the MD5 value at the source end as the key for the operation. After receiving the DML operation from the source end, the target-end data synchronization service directly extracts the MD5 value calculated at the source end as the key.

[0216] In some embodiments, when calculating the key for an operation, since the column order in the operation parsed from the log is not fixed, the columns in the operation must first be sorted by column ID, and then the key is calculated according to the fixed order of the primary key or unique index combination columns. After the source data synchronization service captures a DML log operation, it obtains the table ID in the log and uses the table ID to retrieve the primary key information and unique index of the table in the source database. After parsing the current DML log operation, the source data synchronization service determines whether the table has a primary key or unique index. If a primary key or unique index exists, it calculates an MD5 value by combining the table ID and the column values ​​of the primary key or unique index as the key for this operation, stores it in the current DML log operation, and sends it to the target synchronization service. It should be noted that when the table has both a primary key and a unique index, the MD5 value calculated by combining the table ID and the primary key is preferred as the key; when the table has no primary key but has multiple unique indexes, the MD5 value calculated by combining the table ID and any one of the unique indexes is selected. If there is no primary key or unique index, after parsing the current DML log operation, the source data synchronization service calculates an MD5 value by combining the table ID and the ROWID value or a custom selection column of the corresponding row. This MD5 value is then stored in the current DML log operation and sent to the target synchronization service. It should be noted that when calculating the key for a table without a primary key or unique constraint, if it is known that there are no duplicate values ​​in any combination of columns, the table's ROWID can be used directly to calculate the key, effectively reducing the cost of key calculation. If the table contains duplicate rows, then a custom selection column approach must be used to calculate the key. The selection column can be chosen based on its distribution, allowing the target synchronization service to prevent deadlocks caused by concurrent synchronization transactions operating on rows with duplicate values.

[0217] Example 4:

[0218] Based on the method for setting transaction operation dependencies based on transaction state provided in Embodiment 1 above, the present invention also provides an apparatus for setting transaction operation dependencies based on transaction state to implement the above method and system, such as... Figure 6 The diagram shown is a schematic representation of the device architecture according to an embodiment of the present invention. The device for setting transaction operation dependencies based on transaction state in this embodiment includes one or more processors 21 and a memory 22. Figure 6 Take a processor 21 as an example.

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

[0220] 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 method for setting transaction operation dependencies based on transaction state in Embodiment 1. The processor 21 executes various functional applications and data processing of the device for setting transaction operation dependencies based on transaction state by running the non-volatile software programs, instructions, and modules stored in the memory 22, thereby implementing the method for setting transaction operation dependencies based on transaction state in Embodiment 1.

[0221] 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.

[0222] The program instructions / modules are stored in memory 22. When executed by one or more processors 21, they execute the method for setting transaction operation dependencies based on transaction state in Embodiment 1 above, for example, executing the method described above. Figures 1-4 The steps shown.

[0223] The above-described product can perform the methods provided in the embodiments of this application, and has the corresponding functional modules and beneficial effects for performing the methods. Technical details not described in detail in this embodiment can be found in the methods provided in the embodiments of this application.

[0224] It should be noted that the device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0225] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software and a general-purpose hardware platform, or of course, using hardware. Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The program can be stored in a computer-readable storage medium, and when executed, it can include the processes of the embodiments of the above methods. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), or random access memory (RAM), etc.

[0226] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; under the concept of the present invention, the technical features of the above embodiments or different embodiments can also be combined, the steps can be implemented in any order, and there are many other variations of different aspects of the present invention as described above. For the sake of brevity, they are not provided in detail; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for setting transaction operation dependencies based on transaction state, characterized in that, include: After the target data synchronization service starts, it initializes a hash table Q for registering transaction information, which is used to store the status of all transactions within the scope of the receive collision detection LSN; After the target data synchronization service receives the DML operation from the source, it extracts the transaction ID of the operation and registers it in the hash table Q. The transaction status is active. It also extracts the table ID and its primary key, index, or custom selection column as the KEY, and then extracts the transaction ID of the operation as the TRXID. The KEY and TRXID are combined into a record and saved to the current file. If the record is the first record in the current file, the log LSN corresponding to the operation is used as the starting LSN of the current file. The conflicting TRXID and conflicting LSN of the operation are determined based on the KEY, TRXID, starting LSN, and the state of the transaction in hash table Q. When the target data synchronization service receives a commit operation, it adds the corresponding transaction to the pending transaction queue and modifies the transaction status in hash table Q from active to committed. When the target data synchronization service receives a rollback operation, it discards the transaction and modifies the transaction status in hash table Q from active to rollback.

2. The method for setting transaction operation dependencies based on transaction state according to claim 1, characterized in that, After the target data synchronization service starts, it initializes a hash table R and initializes the record addresses in the hash entries of hash table R to be empty, which is used to store and locate the received operations. The target data synchronization service creates two files to store the keys in hash table R. The file names of each file are named in ascending order of creation, and these two files are added to the cache file linked list.

3. The method for setting transaction operation dependencies based on transaction state according to claim 2, characterized in that, The determination of conflicting TRXIDs and conflicting LSNs based on KEY, TRXID, and starting LSN specifically includes: Construct a detection record X using the KEY of the current operation, and merge the detection record X into the hash table R; During the merging process, conflict detection is performed. If a conflicting key is found, the TRXID value corresponding to the conflicting key is returned; otherwise, 0 is returned as the corresponding TRXID, indicating that there is no conflict. Set the TRXID obtained from the conflict detection as the conflict TRXID, and write the starting LSN of the first file in the cache file linked list corresponding to the current hash table R into the current operation and set it as the conflict LSN.

4. The method for setting transaction operation dependencies based on transaction state according to claim 3, characterized in that, The conflict detection and judgment specifically includes: Calculate the hash value of the KEY of the current detection record X, and locate the hash entry using the hash value; determine whether the record address in the hash entry is empty. If so, set the record address in the hash entry to the next address space of the current detection record X, fill the hash entry with the address of the detection record X, and return 0 as the conflict TRXID, indicating that no conflict was detected; otherwise, proceed to the next conflict detection and judgment step. Determine if the file ID at the address recorded in the hash entry is less than the ID value of the first file in the cache file list. If so, it means that this record Y has exceeded the inspection range. Set the address of the record in the hash entry to the next address space of the current detection record X, fill the hash entry with the address of the detection record X, and return 0 as the conflict TRXID, indicating that no conflict was detected. If not, proceed to the next conflict detection and judgment. Load the corresponding record Y from the file based on the address recorded in the hash entry. Determine if the KEY of record Y is equal to the KEY of the currently detected record X and if the TRXID of record Y is not equal to the TRXID of the current operation. If so, it indicates that there is a conflict in the operation. Search the hash table Q for the detected TRXID to obtain the status of the conflicting transaction and return the conflicting TRXID based on the status of the conflicting transaction. Otherwise, return 0 as the conflicting TRXID to indicate that no conflict was detected. Iterate through the next record of record Y and perform conflict detection and judgment until the record linked list ends.

5. The method for setting transaction operation dependencies based on transaction state according to claim 4, characterized in that, The step of searching the detected TRXID in hash table Q to obtain the status of the conflicting transaction, and returning the conflicting TRXID based on the status of the conflicting transaction, specifically includes: If the conflicting transaction is active, return -1, indicating that it may conflict with transactions whose committed LSN is less than that of the current operation. If the conflicting transaction is in a rollback state, ignore the transaction and continue searching for other conflicting transactions in the current hash entry. If a conflicting transaction is located, continue to process it according to its status; otherwise, return 0 as the conflicting TRXID to indicate that no conflict was detected. If the conflicting transaction is in a committed state, the conflicting transaction ID is returned as the conflicting TRXID.

6. The method for setting transaction operation dependencies based on transaction state according to claim 2, characterized in that, The cached file list includes two files. When the first file is full, the process switches to the second file. When the second file is full, the first file in the cached file list is first evicted, and then a new file is created and added to the cached file list by incrementing the file ID.

7. The method for setting transaction operation dependencies based on transaction state according to claim 2, characterized in that, When the target data synchronization service receives a partial rollback operation, it writes the conflicting TRXIDs in the rolled-back operation back to the hash table R according to the KEY saved in the operation.

8. An apparatus for setting transaction operation dependencies based on transaction state, characterized in that, The apparatus includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the processor for performing the method of setting transaction operation dependencies based on transaction state as described in any one of claims 1-7.

9. A system for setting transaction operation dependencies based on transaction state, employing the method for setting transaction operation dependencies based on transaction state as described in any one of claims 1-7, characterized in that, The system includes a transaction status module, an operation receiving module, a transaction status update module, and a conflict information acquisition module, wherein: The transaction status module is used to initialize a hash table Q for registering transaction information after the target data synchronization service is started, and to store the status of all transactions within the range of the received conflict detection LSN. The operation receiving module is used to extract the transaction ID of the operation and register it in the hash table Q after the target data synchronization service receives the DML operation from the source end. The transaction status is active. The table ID and its primary key or index or custom selection column in the operation are extracted as KEY. The transaction ID in the operation is then extracted as TRXID. The KEY and TRXID are combined into a record and saved to the current file. If the record is the first record in the current file, the log LSN corresponding to the operation is used as the starting LSN of the current file. The transaction status update module is used to add the corresponding transaction to the pending transaction queue and modify the transaction status in hash table Q from active to committed when the target data synchronization service receives a commit operation; and to discard the transaction and modify the transaction status in hash table Q from active to rolledback when the target data synchronization service receives a rollback operation. The conflict information acquisition module is used to determine the conflicting TRXID and conflicting LSN of the operation based on the KEY, TRXID, starting LSN, and the state of the transaction in hash table Q.

10. A non-volatile computer storage medium, characterized in that, The computer storage medium stores computer-executable instructions that are executed by one or more processors to perform the method for setting transaction operation dependencies based on transaction state as described in any one of claims 1-7.

Images