Database transaction management method and device, equipment, storage medium and program product

Abstract

The invention relates to a database transaction management method and device, equipment, a storage medium and a program product. The method comprises the steps that when it is detected that truncation operation needs to be conducted on a transaction submission log, a rollback file in a memory is traversed, and a target transaction identifier in the rollback file is found; the identifier value of the target transaction identifier is the minimum value of a to-be-recycled transaction identifier in the rollback file, and the transaction corresponding to the target transaction identifier is in a rollback state, a to-be-rollback state or a submitted state; the rollback file comprises a plurality of transaction identifiers, the state of each transaction and the information storage address of each transaction; the transaction identifier is coded by adopting 64 bits; and performing truncation operation on all transactions smaller than the target transaction identifier in the transaction submission log. By setting the rollback file in the memory to store the transaction information, the target transaction identifier can be quickly determined when the transaction submission log is cut off, and the transactions smaller than the target transaction identifier are directly judged to be visible, so that the performance fluctuation of the database is avoided.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a database transaction management method, apparatus, device, storage medium, and program product. Background Technology

[0002] In a database management system, a transaction identifier (xid) is written to the tuple header for comparison with the transaction snapshot to determine the visibility of the tuple. At the same time, a transaction commit log (CLOG) is set to record the state before data modification, so as to quickly determine whether the transaction has been committed. When determining visibility, the validity of the tuple itself is determined based on whether the transaction has been committed.

[0003] Traditional methods for determining tuple visibility through rollback operations suffer from limitations due to the transaction commit log's inability to grow indefinitely and the need for transaction flag rollback. During table garbage collection, the database first determines a freeze limit (freeze_limit) based on parameters and the smallest currently visible transaction (oldest_xmin). It then iterates through tuples within each table block, setting all tuples smaller than the freeze limit to a frozen transaction flag (frozen_xid). This allows visibility checks to be performed directly based on the frozen flag. Finally, after table garbage collection, the minimum freeze limit across all tables is used to truncate the transaction commit log.

[0004] However, because truncating the transaction commit log requires scanning all tuples to perform a freeze operation, which relies on the garbage collection operation of the table scan, it greatly affects the performance of the database system. Summary of the Invention

[0005] Therefore, it is necessary to provide a database transaction management method, apparatus, device, storage medium, and program product that can avoid significant performance fluctuations in the database, addressing the aforementioned technical problems.

[0006] Firstly, this application provides a method for managing database transactions, including:

[0007] When it is detected that a truncation operation is required on the transaction commit log, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0008] Truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

[0009] In one embodiment, the method for determining the target transaction identifier includes:

[0010] The transaction identifier of the transaction to be reclaimed in the rollback file is determined, and the transaction to be reclaimed is in the rollback state, the pending rollback state, the committed state, and the running state;

[0011] The transaction identifier with the smallest identifier value among all the transaction identifiers of the transactions to be recycled is selected as the target transaction identifier.

[0012] In one embodiment, the method further includes:

[0013] When a first transaction is detected, a first log space and a first rollback file are allocated for the first transaction; the first transaction corresponds to an operation that inserts or updates data.

[0014] Record the old data of the first transaction into the first log space;

[0015] Update the data page with the new data from the first transaction;

[0016] The transaction identifier of the first transaction and the address information of the first log space are recorded in the first rollback file, and the status of the first transaction is identified; the status includes the reclaimed status, the rolled-back status, the pending rollback status, the committed status, and the running status.

[0017] In one embodiment, the first transaction corresponds to the data insertion operation, and the method further includes:

[0018] Add the index of the first transaction to the index page.

[0019] In one embodiment, the method further includes:

[0020] When a second transaction is detected, the old data corresponding to the second transaction is retrieved from the first log space corresponding to the second transaction, and the old data is updated to the data page; the second transaction corresponds to the operation of rolling back data;

[0021] Delete the new data corresponding to the second transaction on the data page;

[0022] Update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

[0023] In one embodiment, the rollback data is of the type of inserted data, and the method further includes:

[0024] Delete the index information corresponding to the second transaction on the index page.

[0025] Secondly, this application also provides a database transaction management device, comprising:

[0026] The query module is used to traverse the rollback file in memory and find the target transaction identifier in the rollback file when it is detected that a truncation operation on the transaction commit log is required. The identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state. The rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction. The transaction identifier is encoded using 64 bits.

[0027] The truncation module is used to truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

[0028] Thirdly, this application also provides a computer device, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0029] When it is detected that a truncation operation is required on the transaction commit log, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0030] Truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

[0031] Fourthly, this application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the following steps:

[0032] When it is detected that a truncation operation is required on the transaction commit log, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0033] Truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

[0034] Fifthly, this application also provides a computer program product, including a computer program that, when executed by a processor, performs the following steps:

[0035] When it is detected that a truncation operation is required on the transaction commit log, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0036] Truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

[0037] The aforementioned database transaction management methods, devices, equipment, storage media, and program products, when detecting the need to truncate the transaction commit log, traverse the rollback file in memory to find the target transaction identifier within the rollback file. The target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is either already rolled back or awaiting rollback. The rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction. The transaction identifiers are encoded using 64 bits. All transactions in the transaction commit log with an identifier smaller than the target transaction identifier are truncated. By storing transaction identifiers and transaction statuses in a rollback file in memory, and leveraging the small space required by the rollback file, the target transaction identifier can be quickly determined when truncation of the transaction commit log is needed. The target transaction identifier is then used as a reference point to determine transaction visibility, thus trunculating the transaction commit log. Since data pages and index pages have already undergone rollback operations when determining transaction visibility, transactions with identifiers smaller than the target transaction identifier are directly determined to be visible, eliminating the need to access the transaction commit log. Compared to traditional rollback operations that involve freezing and impact database performance, this method truncates the transaction commit log directly based on the target transaction identifier. This avoids significant performance fluctuations caused by database scanning operations, thus ensuring stable database operation. Furthermore, setting the transaction identifier to 64 bits avoids the risk of transaction rollback. Attached Figure Description

[0038] To more clearly illustrate the technical solutions in the embodiments of this application or related technologies, the drawings used in the description of the embodiments of this application or related technologies will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0039] Figure 1 This is a schematic diagram illustrating the application of an existing freeze operation in one embodiment;

[0040] Figure 2 This is a schematic diagram illustrating the application of an existing freeze operation in another embodiment;

[0041] Figure 3 This is an application diagram of an existing database table scanning operation in one embodiment;

[0042] Figure 4 This is a structural block diagram of a computer device for a database transaction management method in one embodiment;

[0043] Figure 5 This is a flowchart illustrating a database transaction management method in one embodiment;

[0044] Figure 6 This is a schematic diagram of the process for determining the target transaction identifier in one embodiment;

[0045] Figure 7 This is a schematic diagram of the process of recording the first transaction to the first rollback file in one embodiment;

[0046] Figure 8 This is an application illustration of recording the first transaction to the first rollback file in one embodiment;

[0047] Figure 9 This is a flowchart illustrating the execution of a rollback operation in one embodiment;

[0048] Figure 10 This is an application illustration of performing a rollback operation in one embodiment;

[0049] Figure 11 This is an application illustration of performing a rollback operation in another embodiment;

[0050] Figure 12 This is a structural block diagram of a database transaction management device in one embodiment. Detailed Implementation

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

[0052] It should be noted that the terms "first," "second," etc., used in this application can be used to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from the second element. The terms "comprising" and "having," and any variations thereof, used in this application, are intended to cover non-exclusive inclusion. The term "multiple" used in this application refers to two or more. The term "and / or" used in this application refers to one of the embodiments, or any combination of multiple embodiments.

[0053] In a database management system, a transaction identifier (xid) is written to the tuple header for comparison with the transaction snapshot to determine the tuple's visibility. Simultaneously, a transaction commit log (CLOG) is set to record the state before data modification, allowing for quick determination of transaction commit. During visibility checks, the validity of the tuple itself is determined based on whether the transaction has committed. Traditional methods, when determining tuple visibility through rollback operations, are hampered by the inability of the transaction commit log to grow indefinitely and the need for transaction identifier rollback. Therefore, during table garbage collection, the database first determines a freeze limit (freeze_limit) based on parameters and the smallest currently visible transaction (oldest_xmin). It then iterates through tuples in each block of the table, setting the frozen transaction identifier (frozen_xid) for all tuples smaller than the freeze limit. This allows visibility checks to be performed directly based on the frozen identifier. Finally, after table garbage collection, the minimum freeze limit across all tables in the database is obtained to truncate the transaction commit log. Figure 1 As shown, PostgreSQL database garbage collection iterates through invalid tuples in each block of the data page, then cleans up invalid tuples in the index page, and finally cleans up tuples in the data page. Here, assuming `freeze_limit=16003`, all tuples with a value less than 16003 (`freeze_limit`) will have their frozen transaction flag set (`frozen_xid=2`). This way, visibility determination will directly use the frozen flag to determine tuple visibility. After completing the data table garbage collection, the minimum frozen limit of 16003 for all tables in the database is obtained, and transaction commit logs with values ​​less than 16003 are truncated.

[0054] Besides the need to scan all tuples for freezing due to transaction log truncation, there are other reasons why tuple scanning for freezing is necessary. Databases originated in the 1980s, and many early databases used 32-bit transaction identifiers to reduce data overhead. However, because 32-bit transaction identifiers themselves tend to wrap around, it's impossible to determine transaction visibility simply by comparing their sizes. Therefore, a two's complement algorithm is used for comparison, which necessitates freezing older tuples to prevent data from becoming abnormally invisible. Taking the open-source PostgreSQL database as an example... Figure 2 As shown, the current transaction identifier is 2^31+100. According to the aforementioned rule, 100-(2^31+100) = 2^31 < 0, so transaction 100 was visible in the past. However, as the current transaction identifier progresses to 2^31+101, 100-(2^31+101) = 2^31 - 1 > 0, so transaction 100 becomes invisible in the future. Therefore, the tuple with transaction identifier 100 becomes invisible due to the progression of the current transaction identifier. To avoid data becoming invisible, the most direct method is to scan all tuples and freeze the transaction identifiers of old tuples to a special transaction identifier (frozen_xid=2). Similarly, the most direct option is to freeze data that is visible in any snapshot into permanent visibility during garbage collection (vacuum scan) of data tuples.

[0055] However, because truncating the transaction commit log requires scanning all tuples to perform the freeze operation, and the freeze operation relies on the table scan's garbage collection operation, such as... Figure 3 As shown, PostgreSQL garbage collection triggers multiple disk scans, exacerbating the impact of garbage collection on performance and causing drastic performance fluctuations.

[0056] In view of the above-mentioned technical problems, this application provides a database transaction management method that can improve database operating performance. The following embodiments will specifically illustrate the database transaction management method.

[0057] The database transaction management method provided in this application embodiment can be applied to, for example, Figure 4 The computer device shown can be a server, and its internal structure diagram can be as follows: Figure 4As shown, this computer device includes a processor, memory, input / output interfaces (I / O), and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operating system and computer programs stored in the non-volatile storage media. The database stores database transaction identifiers and rollback states. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communicating with external terminals via a network connection. When executed by the processor, the computer program implements a database transaction management method.

[0058] Those skilled in the art will understand that Figure 4 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0059] In one exemplary embodiment, such as Figure 5 As shown, a database transaction management method is provided. This embodiment illustrates the method by applying it to a computer device. In this embodiment, the method includes:

[0060] S101, when it is detected that a truncation operation needs to be performed on the transaction commit log, the rollback file in memory is traversed to find the target transaction identifier in the rollback file.

[0061] The target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state. The rollback file includes reclaimed transactions and pending transactions. Each transaction is described by its transaction identifier, transaction status, and transaction information storage address. The transaction status is stored in the form of flag bits. The status of reclaimed transactions is displayed as "reclaimed," and the status of pending transactions can be displayed as "committed," "pending rollback," "rolled back," or "running." The transaction identifier is encoded using 64 bits. The memory can be a rollback segment, and the rollback file includes, but is not limited to, various datasets such as MEM raster drivers, Memory vector drivers, VRT (Virtual Format) virtual raster, and VSIMEM virtual file systems. The size of the rollback file is less than or equal to the memory capacity and can be entirely cached in memory.

[0062] In the embodiments of this application, the computer device monitors the status of transaction identifiers or transaction commit logs of data pages and index pages in real time during database operation. Optionally, the first method is to truncate the transaction commit log when it is detected that any intermediate transaction in the data page or index page has completed a rollback operation. Optionally, the second method is to truncate the transaction commit log when it is detected that the remaining capacity of the transaction commit log has reached a preset capacity threshold, indicating that the log file is full or about to be exhausted. Optionally, the third method is to truncate the transaction commit log when it is detected that a backup command is executed in the transaction commit log. Optionally, the fourth method is to truncate the transaction commit log when it is detected that the database recovery mode has switched from full recovery mode or bulk-logged recovery mode to simple recovery mode. When a truncation operation on the transaction commit log is detected, the rollback file in the rollback segment is traversed. Optionally, one approach is to determine the transaction identifiers of the transactions to be reclaimed from the rollback file, compare all transaction identifiers of the transactions to be reclaimed, and determine the transaction identifier of the transaction with the smallest identifier value as the target transaction identifier. Subsequent stage operations are then performed based on the target transaction identifier. Optionally, another approach is to determine the status of each transaction to be reclaimed in the rollback file, extract the transaction identifiers of the transactions with the status of "pending rollback" and "already rolled back," compare them, and determine the transaction identifier of the transaction with the smallest identifier value as the target transaction identifier. It should be noted that when performing subsequent truncation operations based on the rollback operation, the transaction status in the rollback file is updated in real time after a transaction rollback operation is completed. Furthermore, the update order of the transaction status is consistent with the rollback order of the transactions. For example, the status corresponding to the transaction identifier currently involved in the rollback is updated from "pending rollback" to "already rolled back," and the status corresponding to the transaction identifier of the previous rollback is updated from "already rolled back" to "reclaimed."

[0063] S102, truncate all transactions in the transaction commit log that are less than the target transaction identifier.

[0064] In the embodiments of this application, when the target transaction identifier in the rollback file is found, the transaction visibility of the corresponding data page and index page is determined by the target transaction identifier. Since the transaction identifiers in the current data page and index page have already undergone a rollback operation, it is directly determined that transaction identifiers (tuples) smaller than the target transaction identifier are visible, and the transaction commit log is directly truncated based on the target transaction identifier. The recovery controller in the computer device immediately instructs the log manager to perform a truncation operation on all transactions in the transaction commit log smaller than the target transaction identifier, that is, to set the logical end point of the transaction commit log to the target transaction identifier and release the disk blocks occupied by all log files after the target transaction identifier. After the transaction commit log truncation operation is completed, the recovery controller notifies the database engine, the database engine completes the remaining initialization work, sets the database status to "online", and begins to accept new connections and transactions.

[0065] The aforementioned database transaction management method, when detecting the need to truncate the transaction commit log, traverses the rollback file in memory to find the target transaction identifier within it. The target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target identifier is either already rolled back or awaiting rollback. The rollback file includes multiple transaction identifiers, the status of each transaction, and the storage address of each transaction's information. The transaction identifiers are encoded using 64 bits. All transactions in the transaction commit log with an identifier smaller than the target transaction identifier are truncated. By storing transaction identifiers and transaction statuses in a rollback file in memory, and leveraging the small space required by the rollback file, the target transaction identifier can be quickly determined when truncation of the transaction commit log is needed. This target transaction identifier is then used as a reference point to determine transaction visibility, allowing for truncation of the transaction commit log. Since data pages and index pages have already undergone rollback operations when determining transaction visibility, transactions with identifiers smaller than the target transaction identifier are directly determined to be visible, eliminating the need to access the transaction commit log. Compared to traditional rollback operations that involve freezing and impact database performance, this method truncates the transaction commit log directly based on the target transaction identifier. This avoids significant performance fluctuations caused by database scanning operations, thus ensuring stable database operation. Furthermore, setting the transaction identifier to 64 bits avoids the risk of transaction rollback.

[0066] In one exemplary embodiment, such as Figure 6 As shown, the method for determining the target transaction identifier includes:

[0067] S201, determine the transaction identifier of the transaction to be reclaimed in the rollback file.

[0068] Among them, the transaction to be recycled is in the following states: rolled back, pending rollback, committed, and running.

[0069] In the embodiments of this application, when it is necessary to determine the transaction identifier of the transaction to be reclaimed in the rollback file, the first option is to directly extract the transaction identifiers of the transaction status as rolled back, pending rollback, committed, and running from the rollback file, and determine the extracted transaction identifiers as the transaction identifiers of the transaction to be reclaimed; the second option is to determine the transaction identifiers of the transaction status as reclaimed from the rollback file, and then perform reverse filtering by extracting even-numbered transaction identifiers and comparing them with all transaction identifiers in the rollback file, and determine the filtered transaction identifiers as the transaction identifiers of the transaction to be reclaimed.

[0070] S202, select the transaction identifier with the smallest identifier value among all transaction identifiers of transactions to be recycled as the target transaction identifier.

[0071] In the embodiments of this application, after determining the transaction identifier of the transaction to be reclaimed, one option is to compare the transaction identifiers of the transactions to be reclaimed in all rollback files and determine the transaction identifier of the transaction to be reclaimed with the smallest identifier value as the target transaction identifier. Subsequent stage operations are then performed based on the target transaction identifier. Another option is to determine the status of each transaction to be reclaimed in the rollback files, and extract the transaction identifiers of the transactions with the status of pending rollback and those that have already been rolled back, comparing them to determine the transaction identifier of the transaction to be reclaimed with the smallest identifier value as the target transaction identifier.

[0072] The above method improves the efficiency of transaction visibility determination by identifying the target transaction identifier, further avoiding significant performance fluctuations caused by database scanning operations, and achieving stable database operation.

[0073] In one exemplary embodiment, such as Figure 7 As shown, the method also includes:

[0074] S301: When a first transaction is detected, allocate the first log space and the first rollback file for the first transaction.

[0075] The first transaction corresponds to either inserting or updating data. Inserting data can be a modification to the corresponding tuples on the data page and index page. Updating data can be a modification to the corresponding tuples on the data page. The first log space can include the start and end addresses of the transaction in the transaction commit log and the first rollback file. The first rollback file includes, but is not limited to, various datasets such as MEM raster drivers, Memory vector drivers, VRT (Virtual Format) virtual rasters, and VSIMEM virtual file systems. The size of the first rollback file is less than or equal to the memory capacity and can be entirely cached in memory.

[0076] S302, record the old data of the first transaction to the first log space.

[0077] The old data of the first transaction can be the data of tuple records in the data page and / or index page before the detection of the first transaction.

[0078] S303 updates the data page with the new data from the first transaction.

[0079] The new data in the first transaction can be the data of tuple records in the data page and / or index page after the detection of the first transaction.

[0080] S304, record the transaction identifier of the first transaction and the address information of the first log space into the first rollback file, and mark the status of the first transaction.

[0081] The status includes recycled status, rolled back status, pending rollback status, committed status, and running status.

[0082] In the embodiments of this application, when the computer device detects a first transaction in the database, it determines the type of the first transaction. If the first transaction is an insert data operation, it allocates a first log space from the transaction commit log for the data inserted into the data page and index page, records the start and end addresses of the first log space, records the old data of the first transaction into the first log space, and updates the data page and index page with the new data of the first transaction. Simultaneously, it records the transaction identifier of the first transaction and the address information of the first log space into the first rollback file, and identifies the status of the first transaction. At this time, the status of the first transaction can be running. If the first transaction is an update data operation, it allocates a first log space from the transaction commit log for the update data in the data page, records the start and end addresses of the first log space, records the old data of the first transaction into the first log space, and updates the data page with the new data of the first transaction. Simultaneously, it records the transaction identifier of the first transaction and the address information of the first log space into the first rollback file, and identifies the status of the first transaction. At this time, the status of the first transaction can be pending rollback.

[0083] The above method records the space information allocated to each transaction by setting a rollback file on the rollback segment. It has a small space footprint and can be fully cached in memory, ensuring the stability of database operation.

[0084] In an exemplary embodiment, the first transaction corresponds to the operation of inserting data, and the method further includes: adding the index of the first transaction to the index page.

[0085] In the embodiments of this application, such as Figure 8As shown, when the first transaction type is an insert operation, it involves changes to both the data page and the index page. For example, the Undo log is the transaction commit log, and the Undo Txn is the first rollback file. When transaction 15001 inserts a piece of data, both the index page and the data page allocate space from the Undo log and record information in the Undo log. Simultaneously, the start (start address) and end (end address) allocated by transaction 15001 are recorded in the Undo Txn. If transaction 15020 updates this data but the index is not updated, the start (start address) and end (end address) allocated by transaction 15020 will also be recorded in the Undo Txn.

[0086] To reduce the reliance on data for index reclamation, the above method also records the transaction identifier on the index page tuple. The data page tuple, index page tuple, and first rollback file all use a 64-bit transaction identifier, avoiding transaction identifier rollback issues. Whenever a database transaction performs a change operation, the modifications to data pages and index pages are recorded in a space allocated in the transaction commit log, and the allocated space information is also recorded in the first rollback file. This method has a small space footprint and can be fully cached in memory, ensuring the stability of database operation.

[0087] In one exemplary embodiment, such as Figure 9 As shown, the method also includes:

[0088] S401, when a second transaction is detected, retrieve the old data corresponding to the second transaction from the first log space corresponding to the second transaction, and update the data page with the old data.

[0089] The second transaction corresponds to the data rollback operation. Rollback data types include inserted data and updated data. The old data corresponding to the second transaction can be the data recorded in the transaction commit log before the second transaction was detected.

[0090] S402, delete the new data corresponding to the second transaction on the data page.

[0091] The new data corresponding to the second transaction can be the data recorded in the data page and / or index page tuples before the detection of the second transaction.

[0092] S403, Update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

[0093] In the embodiments of this application, when the computer device detects a second transaction in the database, it parses the second transaction to obtain its transaction identifier. The transaction identifier recorded in the transaction commit log is matched with the transaction identifier of the second transaction, i.e., the rollback data type corresponding to the second transaction is determined. Optionally, if the transaction commit log includes data and an index matching the transaction identifier of the second transaction, indicating that the rollback data type is inserted data, then the old data and old index corresponding to the second transaction are retrieved from the first log space corresponding to the second transaction, and the old data and old index are updated in the corresponding tuples of the data page and index page. The new data and new index corresponding to the second transaction on the data page and index page are deleted, and the status of the second transaction in the first rollback file corresponding to the second transaction is changed from the pending rollback state to the rolled-back state. If the transaction commit log includes data matching the transaction identifier of the second transaction, indicating that the rollback data type is updated data, then the old data corresponding to the second transaction is retrieved from the first log space corresponding to the second transaction, and the old data is updated in the corresponding tuple of the data page. The new data corresponding to the second transaction on the data page is deleted, and the status of the second transaction in the first rollback file corresponding to the second transaction is changed from the pending rollback state to the rolled-back state.

[0094] The above method restores the modified data in both the index page and the data page to their previous state whenever a transaction rollback occurs. After the rollback is complete, the transaction rollback status is updated, making the transaction status more intuitive and facilitating quick determination of transaction visibility.

[0095] In one exemplary embodiment, the type of rollback data is inserted data, and the method further includes: deleting the index information corresponding to the second transaction on the index page.

[0096] In the embodiments of this application, such as Figure 10 As shown, when it is determined that the rollback data type of transaction 15098 is inserted data, after transaction 15098 rolls back, the inserted data is cleared from the data page and index page. After the rollback is completed, a rollback completion flag (i.e., rolledback status) is recorded in the first rollback file. By traversing the first rollback file in memory, the target transaction identifier (global_fronze_xid) in the first rollback file is calculated. Since all aborted transactions with identifiers less than the target transaction identifier are in a rolled-back status and are cleared from the data page and index page, transactions with identifiers less than the target transaction identifier must have been committed. Furthermore, since transactions with identifiers less than the target transaction identifier are necessarily less than the minimum active transaction identifier, it means that the transactions are visible in all snapshots. Therefore, transactions with identifiers less than the target transaction identifier are necessarily visible and do not need to be compared in any snapshot. This improves the efficiency of determining transaction visibility.

[0097] Optionally, such as Figure 11 As shown, `recycled` indicates a recycled state, `committed` indicates a committed state, `to rollback` indicates a pending rollback state, `rollbacked` indicates a rolled-back state, and `running` indicates a running state. When transaction 15098 has not yet completed its rollback, the minimum active transaction identifier is 16029. By traversing the information in the first rollback file in memory, the target transaction identifier is determined to be 15098. All transactions with an identifier less than 15098 are guaranteed to be visible. When transaction 15098 switches to the rolled-back state, the target transaction identifier advances to 16003. At this point, the data pages and index pages no longer contain data modified by transaction 15098, and all transactions with an identifier less than 16003 are guaranteed to be visible. Computer devices can directly truncate the transaction commit log using the target transaction identifier.

[0098] The above method determines the target transaction identifier through the first rollback file in memory, directly judges the transaction visibility, and truncates the transaction commit log based on the target transaction identifier. This eliminates the need to repeatedly access the transaction commit log and avoids fluctuations in database performance.

[0099] Optionally, when performing rollback operations or sampling on aborted transactions, the method further includes: selecting the data page to be cleaned up and determining the tuple distribution information in the data page; cleaning up invalid tuples in the data page according to the tuple distribution information; determining whether the number of data pages to be cleaned up has reached a preset threshold; if not, returning to select the data page to be cleaned up; if so, updating the tuple statistics information in the database and truncating the transaction commit log.

[0100] The above method performs piggybacking cleanup of invalid tuples during the rollback or sampling process of aborted transactions. Since the transaction rollback operation has already processed the tuples of aborted transactions, invalid tuples only need to be processed during the rollback operation and sampling process, which reduces the workload of data processing and avoids multiple disk scans during garbage collection, making the database performance more stable.

[0101] It should be understood that although the steps in the flowcharts of the above embodiments are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the above embodiments may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages in other steps. It is understood that the steps in different embodiments can be freely combined as needed, and all non-contradictory solutions formed by such combinations are within the scope of protection of this application.

[0102] Based on the same inventive concept, this application also provides a database transaction management device for implementing the database transaction management method described above. The solution provided by this device is similar to the implementation described in the above method; therefore, the specific limitations of one or more database transaction management device embodiments provided below can be found in the limitations of the database transaction management method described above, and will not be repeated here.

[0103] In one exemplary embodiment, such as Figure 12 As shown, a database transaction management device is provided, including: a query module 121 and a truncation module 122, wherein:

[0104] The query module 121 is used to traverse the rollback file in memory and find the target transaction identifier in the rollback file when it is detected that a truncation operation on the transaction commit log is required. The identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a state to be rolled back. The rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction. The transaction identifier is encoded using 64 bits.

[0105] The truncation module 122 is used to truncate all transactions in the transaction commit log that are less than the target transaction identifier.

[0106] In one exemplary embodiment, the device further includes a first determining module, configured to:

[0107] Determine the transaction identifier of the transaction to be reclaimed in the rollback file, and identify whether the transaction is in the rolled-back state, the pending rollback state, the committed state, or the running state.

[0108] The transaction identifier with the smallest identifier value among all the transaction identifiers of the transactions to be recycled is selected as the target transaction identifier.

[0109] In one exemplary embodiment, the device further includes:

[0110] The first detection module is used to allocate a first log space and a first rollback file to the first transaction when a first transaction is detected; the first transaction corresponds to the operation of inserting data or updating data.

[0111] The first recording module is used to record the old data of the first transaction into the first log space;

[0112] The first update module is used to update the data page with the new data from the first transaction.

[0113] The second recording module is used to record the transaction identifier of the first transaction and the address information of the first log space into the first rollback file, and to identify the status of the first transaction; the status includes the reclaimed status, the rolled-back status, the pending rollback status, the committed status, and the running status.

[0114] In one exemplary embodiment, the update module further includes an adding unit for adding the index of the first transaction to the index page.

[0115] In one exemplary embodiment, the device further includes:

[0116] The second detection module is used to retrieve the old data corresponding to the second transaction from the first log space corresponding to the second transaction when a second transaction is detected, and update the old data to the data page; and to perform the operation of rolling back the data corresponding to the second transaction.

[0117] The delete module is used to delete new data corresponding to the second transaction on the data page.

[0118] The second update module is used to update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

[0119] In an exemplary embodiment, the deletion module further includes a deletion unit for deleting the index information corresponding to the second transaction on the index page.

[0120] The modules in the aforementioned database transaction management device can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0121] In one exemplary embodiment, a computer device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to perform the following steps:

[0122] When it is detected that a truncation operation on the transaction commit log is required, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a state to be rolled back; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0123] Truncate all transactions in the transaction commit log that are less than the target transaction identifier.

[0124] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0125] Determine the transaction identifier of the transaction to be reclaimed in the rollback file, and identify whether the transaction is in the rolled-back state, the pending rollback state, the committed state, or the running state.

[0126] The transaction identifier with the smallest identifier value among all the transaction identifiers of the transactions to be recycled is selected as the target transaction identifier.

[0127] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0128] When a first transaction is detected, allocate a first log space and a first rollback file for the first transaction; the first transaction corresponds to data insertion or data update operations.

[0129] Record the old data of the first transaction into the first log space;

[0130] Update the data page with the new data from the first transaction;

[0131] The transaction identifier of the first transaction and the address information of the first log space are recorded in the first rollback file, and the status of the first transaction is marked; the status includes the reclaimed status, the rolled-back status, the pending rollback status, the committed status, and the running status.

[0132] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0133] Add the index of the first transaction to the index page.

[0134] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0135] When a second transaction is detected, retrieve the old data corresponding to the second transaction from the first log space corresponding to the second transaction and update the data page with the old data; the second transaction corresponds to the operation of rolling back the data;

[0136] Delete the new data corresponding to the second transaction on the data page;

[0137] Update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

[0138] In one embodiment, the processor, when executing a computer program, also performs the following steps:

[0139] Delete the index information corresponding to the second transaction on the index page.

[0140] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, the computer program performing the following steps when executed by a processor:

[0141] When it is detected that a truncation operation on the transaction commit log is required, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a state to be rolled back; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0142] Truncate all transactions in the transaction commit log that are less than the target transaction identifier.

[0143] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0144] Determine the transaction identifier of the transaction to be reclaimed in the rollback file, and identify whether the transaction is in the rolled-back state, the pending rollback state, the committed state, or the running state.

[0145] The transaction identifier with the smallest identifier value among all the transaction identifiers of the transactions to be recycled is selected as the target transaction identifier.

[0146] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0147] When a first transaction is detected, allocate a first log space and a first rollback file for the first transaction; the first transaction corresponds to data insertion or data update operations.

[0148] Record the old data of the first transaction into the first log space;

[0149] Update the data page with the new data from the first transaction;

[0150] The transaction identifier of the first transaction and the address information of the first log space are recorded in the first rollback file, and the status of the first transaction is marked; the status includes the reclaimed status, the rolled-back status, the pending rollback status, the committed status, and the running status.

[0151] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0152] Add the index of the first transaction to the index page.

[0153] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0154] When a second transaction is detected, retrieve the old data corresponding to the second transaction from the first log space corresponding to the second transaction and update the data page with the old data; the second transaction corresponds to the operation of rolling back the data;

[0155] Delete the new data corresponding to the second transaction on the data page;

[0156] Update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

[0157] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0158] Delete the index information corresponding to the second transaction on the index page.

[0159] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, performs the following steps:

[0160] When it is detected that a truncation operation on the transaction commit log is required, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a state to be rolled back; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits.

[0161] Truncate all transactions in the transaction commit log that are less than the target transaction identifier.

[0162] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0163] Determine the transaction identifier of the transaction to be reclaimed in the rollback file, and identify whether the transaction is in the rolled-back state, the pending rollback state, the committed state, or the running state.

[0164] The transaction identifier with the smallest identifier value among all the transaction identifiers of the transactions to be recycled is selected as the target transaction identifier.

[0165] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0166] When a first transaction is detected, allocate a first log space and a first rollback file for the first transaction; the first transaction corresponds to data insertion or data update operations.

[0167] Record the old data of the first transaction into the first log space;

[0168] Update the data page with the new data from the first transaction;

[0169] The transaction identifier of the first transaction and the address information of the first log space are recorded in the first rollback file, and the status of the first transaction is marked; the status includes the reclaimed status, the rolled-back status, the pending rollback status, the committed status, and the running status.

[0170] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0171] Add the index of the first transaction to the index page.

[0172] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0173] When a second transaction is detected, retrieve the old data corresponding to the second transaction from the first log space corresponding to the second transaction and update the data page with the old data; the second transaction corresponds to the operation of rolling back the data;

[0174] Delete the new data corresponding to the second transaction on the data page;

[0175] Update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

[0176] In one embodiment, when the computer program is executed by a processor, it also performs the following steps:

[0177] Delete the index information corresponding to the second transaction on the index page.

[0178] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments of the above methods. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile memory and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, artificial intelligence (AI) processors, etc., and are not limited to these.

[0179] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this application.

[0180] The above embodiments are merely illustrative of several implementation methods of this application, and their descriptions are relatively specific and detailed. However, they should not be construed as limiting the scope of this application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A method for managing database transactions, characterized in that, The method includes: When it is detected that a truncation operation is required on the transaction commit log, the rollback file in memory is traversed to find the target transaction identifier in the rollback file; the identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state; the rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction; the transaction identifier is encoded using 64 bits. Truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

2. The method according to claim 1, characterized in that, The method for determining the target transaction identifier includes: The transaction identifier of the transaction to be reclaimed in the rollback file is determined, and the transaction to be reclaimed is in the rollback state, the pending rollback state, the committed state, and the running state; The transaction identifier with the smallest identifier value among all the transaction identifiers of the transactions to be recycled is selected as the target transaction identifier.

3. The method according to claim 1 or 2, characterized in that, The method further includes: When a first transaction is detected, a first log space and a first rollback file are allocated for the first transaction; the first transaction corresponds to an operation that inserts or updates data. Record the old data of the first transaction into the first log space; Update the data page with the new data from the first transaction; The transaction identifier of the first transaction and the address information of the first log space are recorded in the first rollback file, and the status of the first transaction is identified; the status includes the reclaimed status, the rolled-back status, the pending rollback status, the committed status, and the running status.

4. The method according to claim 3, characterized in that, The method further includes the operation of inserting data corresponding to the first transaction: Add the index of the first transaction to the index page.

5. The method according to claim 4, characterized in that, The method further includes: When a second transaction is detected, the old data corresponding to the second transaction is retrieved from the first log space corresponding to the second transaction, and the old data is updated to the data page; the second transaction corresponds to the operation of rolling back data; Delete the new data corresponding to the second transaction on the data page; Update the status of the second transaction in the first rollback file corresponding to the second transaction to the rolled-back status.

6. The method according to claim 5, characterized in that, The type of the rollback data is inserted data, and the method further includes: Delete the index information corresponding to the second transaction on the index page.

7. A database transaction management device, characterized in that, The device includes: The query module is used to traverse the rollback file in memory and find the target transaction identifier in the rollback file when it is detected that a truncation operation on the transaction commit log is required. The identifier value of the target transaction identifier is the minimum value of the transaction identifiers to be reclaimed in the rollback file, and the transaction corresponding to the target transaction identifier is in a rolled-back state or a pending rollback state. The rollback file includes multiple transaction identifiers, the status of each transaction, and the information storage address of each transaction. The transaction identifier is encoded using 64 bits. The truncation module is used to truncate all transactions in the transaction commit log that are smaller than the target transaction identifier.

8. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that, When the processor executes the computer program, it implements the steps of the method of any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method of any one of claims 1 to 6.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method according to any one of claims 1 to 6.

Images