Dynamic quick flash-back query method based on version in database

Abstract

The invention discloses a version-based dynamic quick flash-back query method in a database, and relates to the technical field of database query. According to the method, a full-memory-state MVCC constructed by a shared memory in a node and a dynamic nonlinear flash-back snapshot dotting mechanism are utilized, and a specific data version is reserved in the memory for quick flash-back query at any time. In a multi-node scene of a main cluster and a standby cluster, each node has a full-memory-state MVCC mechanism of the node. And quick, stable and consistent query efficiency can be achieved for short-time flash-back query just after misoperation or long-time span query. Especially for long-time-span flash-back query, long-time disk access and log rollback processing are avoided, and the query performance is greatly improved compared with a traditional mode. A dynamic non-linear time interval retention snapshot mechanism enables snapshots in a closer period of time to be denser and snapshots in a longer period of time to be sparser. The MVCC memory can be well controlled, and top-speed expansion of the memory is avoided.

Description

Technical Field

[0001] This invention relates to the field of database query technology, and in particular to a version-based dynamic fast flashback query method for databases. Background Art

[0002] In the evolution of database systems, efficiently supporting flashback queries has always been a key focus of the industry. Flashback queries allow users to quickly retrieve historical data versions at a specified point in time, which is invaluable not only in scenarios involving accidental data recovery but also in facilitating data analysis and auditing. However, existing technical approaches are generally based on MVCC (Multi-Version Concurrency Control) mechanisms and flashback log rollback technology. While these solutions can meet flashback requirements to some extent, they have several shortcomings in scenarios involving large-scale data, high-concurrency access, and long-duration queries. First, the basic principle of MVCC is to maintain multiple versions for each record, generating a new version each time a transaction is committed. These versions are typically stored on disk, and access efficiency is improved through a memory page buffer mechanism. However, disk storage space and memory capacity are limited. When the number of historical versions reaches a threshold, the system cleans up older versions to maintain a balance between storage and performance. This leads to a significant problem: if a user needs to query a historical data version that has already been cleaned up, they cannot directly obtain the result from the MVCC version chain, resulting in flashback query failure. To alleviate the above problems, existing technologies typically introduce a flashback log archiving mechanism.

[0003] CN119046254A discloses a PostgreSQL-based WAL log management method and system. Through archive mode configuration, dynamic sharding, encryption protection, and uploading to OSS storage buckets, it achieves long-term storage and disaster recovery of WAL files. This method has advantages in ensuring data integrity and security, but its core objective remains log archiving and storage optimization, failing to address the issue of insufficient real-time performance in flashback queries. In query scenarios, archived logs still require sequential scanning and replay operations.

[0004] CN117033398A discloses a method and apparatus for flashback querying data based on the dolt database. By synchronizing incremental data from the source database to the dolt database and establishing an incremental dataset and version identifier, it enables querying data at any historical point in time. While this method extends the time traceability of the data, the lack of cleanup of incremental data leads to long-term storage space expansion and significant maintenance costs. Furthermore, this solution relies on an external database for flashback queries, increasing the complexity of data synchronization and consistency management, making it difficult to maintain real-time performance and stability in high-concurrency, large-scale data scenarios.

[0005] In addition, MVCC version management is primarily based on disk persistence. While memory caching mechanisms can partially improve access efficiency, it remains limited by disk I / O latency, often exhibiting performance bottlenecks for fast flashback queries in large-scale data scenarios. Especially in long-running, frequently updated application environments, as the historical version chain grows longer, traditional traversal and backtracking methods require more computing and storage resources, further reducing query efficiency. Furthermore, existing technologies lack dynamic and intelligent means for organizing and managing snapshots; most implementations rely on fixed log archiving strategies or simple version retention rules, making it difficult to achieve a better balance between query efficiency and storage costs.

[0006] The database flashback query method based on full memory-state MVCC proposed in this invention effectively solves the performance bottleneck and storage overhead problems in the prior art through logical timestamp and snapshot tree structure management, dynamic snapshot density control and approximate query feedback mechanism. Summary of the Invention

[0007] The problem this invention aims to solve is how to achieve fast flashback queries and high-availability backtracking within long time windows under resource-constrained conditions.

[0008] To solve the above-mentioned technical problems, the present invention provides the following technical solution: In a first aspect, the present invention provides a version-based dynamic fast flashback query method for databases, comprising: S1: After a user submits a transaction, a historical data version chain of MVCCs is generated and managed using a full-memory MVCC mechanism, and a logical timestamp is assigned to each historical data version in each MVCC; S2: Simultaneously with the generation of the historical data version chain of MVCCs, a background thread generates a flashback snapshot containing a mapping relationship between system timestamps and logical timestamps at a preset period, inserts the flashback snapshot into a snapshot tree structure, and balances and deletes it according to retention rules to control the snapshot density; S3: When a flashback query request is received, a matching system timestamp is retrieved from the snapshot tree and the corresponding logical timestamp is extracted, and the corresponding historical data version of the MVCC is located in the historical data version chain of MVCCs; S4: The located historical data version of the MVCCs is assembled into a query result and returned, and the user is prompted during approximate queries, while the query result is fed back to S2 to dynamically adjust the flashback snapshot density.

[0009] As a preferred embodiment of the version-based dynamic fast flashback query method in the database described in this invention, wherein: S1 includes: the MVCC historical data version chain is implemented using a memory engine, storing multiple historical data versions generated when a DML transaction is committed, each historical data version recording a logical timestamp at the time of generation; the logical timestamp is uniformly allocated by a global timestamp allocator; the current data version is stored in a buffer or page, compatible with disk-based storage engines; if a certain MVCC historical data version in the MVCC historical data version chain is not visible to any transaction, it is cleaned up immediately.

[0010] As a preferred embodiment of the version-based dynamic fast flashback query method in the database described in this invention, the following steps are taken: a background flashback thread generates flashback snapshots at a fixed frequency, and the flashback snapshots are stored in a snapshot tree in a red-black tree structure. The flashback snapshot records the mapping relationship between the system timestamp and the logical timestamp at the time of generation. After a flashback snapshot is generated, MVCC historical data versions with the same logical timestamp value as those in the flashback snapshot maintain transaction visibility during garbage collection and cannot be cleared by the garbage collector until the corresponding flashback snapshot is deleted. The key value of the red-black tree is the system timestamp, and the snapshot query time complexity is O(logN), where N is the number of nodes in the tree.

[0011] As a preferred embodiment of the version-based dynamic fast flashback query method in the database described in this invention, the background flashback thread performs non-linear interval deletion of the snapshot tree according to preset parameters while inserting a new flashback snapshot; the preset parameters include full retention duration, maximum retention duration, and retention interval; when the time period from the generation of the flashback snapshot to the current period is less than the full retention duration, all snapshots are retained; snapshots with a retention interval greater than the full retention duration but less than or equal to twice the full retention duration are retained at intervals according to the retention interval; snapshots with a retention interval greater than twice the full retention duration but less than or equal to three times the full retention duration are retained at intervals of twice the retention interval, and so on, until the flashback snapshot retention duration exceeds the maximum retention duration and is then completely deleted.

[0012] As a preferred embodiment of the version-based dynamic fast flashback query method in the database described in this invention, wherein: during the snapshot tree maintenance process, when the flashback snapshot corresponding to the specified system timestamp queried by the user has been deleted at intervals, an approximate query strategy is executed; the approximate query strategy includes: automatically selecting the existing flashback snapshot closest to the system timestamp from the snapshot tree for version retrieval, extracting the corresponding logical timestamp from the flashback snapshot, and simultaneously returning the system timestamp of the approximate flashback snapshot to the user, while also prompting the user that the flashback snapshot corresponding to the system timestamp originally queried no longer exists.

[0013] As a preferred embodiment of the version-based dynamic fast flashback query method in the database described in this invention, when a user initiates a query using the SQL flashback query syntax, the system first retrieves the flashback snapshot node with the key value corresponding to the system timestamp in the snapshot tree and extracts the logical timestamp value; if no matching flashback snapshot node is found, the system obtains the flashback snapshot node with the most recent system timestamp according to the approximate query strategy and extracts the logical timestamp value.

[0014] As a preferred embodiment of the version-based dynamic fast flashback query method in the database described in this invention, the following steps are taken: after obtaining the logical timestamp value, the memory engine is traversed based on the logical timestamp to locate the MVCC historical data version with a logical timestamp equal to the obtained logical timestamp value from the MVCC historical data version chain, and the corresponding data is read and returned to the user; the MVCC historical data version chain traversal and the version writing of DML transactions avoid concurrency conflicts through a read-write lock mechanism.

[0015] As a preferred embodiment of a version-based dynamic fast flashback query method in the database described in this invention, the logical relationship between S1 and S2 is as follows: the system timestamp and logical timestamp mapping relationship generated in S1 serves as the sole index source for retrieval in S2, and the logical timestamp value is only incremented and updated when the DML transaction is committed, ensuring a one-to-one correspondence between the flashback snapshot time point and the historical version chain.

[0016] In a second aspect, the present invention provides a computer device, including a memory and a processor, wherein the memory stores a computer program, wherein: when the computer program instructions are executed by the processor, they implement the steps of a version-based dynamic fast flashback query method in a database as described in the first aspect of the present invention.

[0017] Thirdly, the present invention provides a computer-readable storage medium having a computer program stored thereon, wherein: when the computer program instructions are executed by a processor, they implement the steps of a version-based dynamic fast flashback query method in a database as described in the first aspect of the present invention.

[0018] The beneficial effects of this invention are as follows: This invention adds an additional fast flashback query method to the traditional flashback query. It utilizes a full-memory MVCC mechanism constructed from shared memory within nodes and a dynamic non-linear flashback snapshot logging mechanism to permanently retain specific data versions in memory for fast flashback queries at any time. In a multi-node master-slave cluster scenario, each node has its own full-memory MVCC mechanism. Whether it's a short-term flashback query immediately after a mistake or a long-span query, query efficiency is fast and consistent. Especially for long-span flashback queries, it avoids lengthy disk access and log rollback processing, significantly improving query performance compared to traditional methods. The dynamic non-linear time interval snapshot retention mechanism results in denser snapshots for more recent time periods and sparser snapshots for longer time periods. This effectively controls MVCC memory, preventing rapid memory expansion; simultaneously, it dynamically adjusts the snapshot interval density based on the user's flashback query performance, further improving flashback query efficiency. Attached Figure Description

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

[0020] Figure 1 This is a schematic diagram illustrating the principle of traditional flashback query technology. Figure 2 This is a schematic diagram illustrating the mapping relationship between system timestamp and logical timestamp tswrite; Figure 3 A diagram illustrating the insertion of a snapshot tree into a snapshot; Figure 4 A diagram illustrating the process of clearing snapshots from the snapshot tree that do not need to be retained after the retention rules have been calculated. Figure 5 This is a diagram showing the adjusted snapshot tree after the snapshot has been cleared. Figure 6 This is a diagram illustrating the snapshot interval deletion rules in a snapshot tree. Figure 7 This is a flowchart of a version-based dynamic fast flashback query method. Detailed Implementation

[0021] To make the above-mentioned objects, features and advantages of the present invention more apparent and understandable, the specific embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0022] Many specific details are set forth in the following description in order to provide a full understanding of the invention. However, the invention may also be practiced in other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the spirit of the invention. Therefore, the invention is not limited to the specific embodiments disclosed below.

[0023] Secondly, the term "one embodiment" or "embodiment" as used herein refers to a specific feature, structure, or characteristic that may be included in at least one implementation of the present invention. The phrase "in one embodiment" appearing in different places in this specification does not necessarily refer to the same embodiment, nor is it a single or selective embodiment that is mutually exclusive with other embodiments.

[0024] Example 1 Currently, all flashback query technologies used in the industry inevitably involve accessing disk pages and files. Specific scenarios are as follows: Figure 1 As shown, a table, due to transaction operations, has generated versions 1 through 6 from oldest to newest. Due to MVCC space limitations, versions 1-3 are moved to the flashback archive log. Although MVCC can speed things up by caching pages in memory, disk access is still unavoidable when the cached pages are insufficient or swapped out. For example, the MVCC in the diagram stores versions 6, 5, and 4 for a certain row. However, version 4 is stored on disk. When a user needs to flashback query version 4, it must first be loaded from disk into memory through the caching mechanism before being returned to the user, thus incurring I / O.

[0025] Accessing flashback log files involves significant I / O. If a user performs a flashback query for version 2, this version is too old and has been moved to the flashback archive log on disk, requiring disk I / O to retrieve it. This results in poor performance for flashback queries, especially when querying table data with long query times, leading to numerous file accesses and rollback processes. This invention adds an additional fast flashback query method to the traditional flashback query to solve the above problems.

[0026] Figure 7 This is a flowchart illustrating a version-based dynamic fast flashback query method in a database according to an embodiment of the present invention. Figure 7 As shown, a version-based dynamic fast flashback query method in a database includes: When a DML transaction modifies a table, the fully in-memory MVCC saves each version of the data generated by the transaction in memory. Simultaneously, a background thread performs flashback snapshot logging, generating snapshots at various points in time and storing them in a snapshot tree structure. Each snapshot contains the current system timestamp (`timestamp`) and the logical timestamp (`tswrite`) of the current version within the MVCC corresponding to that timestamp. When a user performs a flashback query for a specific time, the specified timestamp is used to search the snapshot tree. The logical timestamp (`tswrite`) from the found snapshot is extracted, the version is searched in the in-memory MVCC, and the final result set is generated.

[0027] S1: After the user submits the transaction, the MVCC historical data version chain is generated and managed using the full memory-state MVCC mechanism, and a logical timestamp is assigned to each historical data version in the MVCC historical data version chain.

[0028] S1.1: The MVCC historical data version chain is implemented using a memory engine, which stores multiple historical data versions generated when a DML transaction is committed. Each historical data version records a logical timestamp at the time of its generation; the logical timestamp is uniformly allocated by the global timestamp allocator.

[0029] It should be noted that historical (multi-version) information is stored in the memory engine (HUB), which can achieve better multi-version query performance; the current (unique) version is stored in the data page cache, which is compatible with disk-based storage engines; write operations update the current version and generate historical versions, and read operations read the current version and historical versions; when multi-version loses visibility, there are no transactions that need to read it, and the GC is responsible for timely cleanup, that is, the current data version is stored in the buffer or page, which is compatible with disk-based storage engines; if the MVCC historical data version is not visible to any transaction, it is cleaned up immediately.

[0030] S1.2: A background flashback thread generates flashback snapshots at a fixed frequency (e.g., 1 second) and stores the flashback snapshots in a snapshot tree in a red-black tree structure. The flashback snapshot records the mapping relationship between the system timestamp and the logical timestamp at the time of generation.

[0031] It's important to note that MVCC (Multi-Version Concurrency Control) is the foundation of flashback queries, creating versions of data at different points in time. The next step in flashback queries is to retrieve data from the table by obtaining the version corresponding to the system timestamp within the MVCC. However, the system timestamp cannot be directly used to find a specific version within the MVCC. The data stored in the MVCC version also stores the corresponding logical timestamp (tswrite). The background snapshot thread generates flashback snapshots along the time dimension, saving the mapping between the system timestamp at the time of snapshot creation and the logical timestamp (tswrite) within the MVCC version.

[0032] The mapping relationship is as follows: Figure 2 As shown, the details are as follows: The logical timestamp `tswrite` is globally managed and initially set to 0. When a user commits a DML transaction, the logical timestamp `tswrite` is incremented by 1. Simultaneously, a data version corresponding to the transaction is generated, and the modified logical timestamp `tswrite` is saved within this version. When a flashback snapshot is generated, the current value is retrieved from the global logical timestamp `tswrite` and saved along with the current system timestamp `timestamp` into the snapshot. This establishes a mapping between the system timestamp `timestamp` and the logical timestamp `tswrite`. Figure 2 It is known that `tswrite` is only modified after a DML transaction is committed. Therefore, the logical timestamp `tswrite` of flashback snapshots A and B are both 1, meaning that both snapshots are visible to the same data version 1, and no other transactions have changed the data between them. However, the logical timestamp `tswrite` of flashback snapshot C is 3, indicating that it was modified by two transactions between itself and snapshot B; therefore, only version 3 is visible to it. Furthermore, modifications and queries to the logical timestamp `tswrite` are handled using a locking mechanism to avoid concurrency conflicts. For subsequent flashback queries, the user specifies a system timestamp. Only by finding the corresponding flashback snapshot through the snapshot tree and retrieving its `tswrite` can the corresponding visible version be queried in the MVCC.

[0033] Furthermore, after a flashback snapshot is generated, the MVCC historical data version with the same logical timestamp value tswrite in the flashback snapshot remains visible in the Garbage Collector (GC) version reclamation (i.e., this version is visible and cannot be deleted) until the corresponding flashback snapshot is deleted.

[0034] In this red-black tree, the key value is the system timestamp, and the time complexity of snapshot lookup is O(logN), where N is the number of nodes in the red-black tree.

[0035] In addition, read and write operations on the snapshot tree avoid concurrent conflicts through a read-write shared lock.

[0036] S2: While the MVCC historical data version chain is being generated, a background thread generates flashback snapshots containing the mapping relationship between system timestamps and logical timestamps at a preset period, and inserts the flashback snapshots into the snapshot tree structure. The snapshot density is controlled by balancing and deleting according to retention rules.

[0037] Specifically, during the snapshot tree maintenance process, if the snapshot corresponding to the specified system timestamp queried by the user has been deleted at intervals, an approximate query strategy is executed.

[0038] The approximate query strategy includes: automatically selecting the existing flashback snapshot closest to the system timestamp from the snapshot tree for version retrieval, extracting the corresponding logical timestamp from the flashback snapshot, returning the system timestamp of the approximate flashback snapshot to the user, and prompting the user that the flashback snapshot corresponding to the system timestamp originally queried no longer exists.

[0039] A better approach is to insert a snapshot tree, such as... Figure 3 As shown, the generated snapshots are stored in a red-black tree structure, with the key value being the system timestamp of the snapshot. That is, the tree structure is sorted according to the system timestamp of the snapshot, which can improve the performance of subsequent snapshot lookups.

[0040] A better approach is to maintain the snapshot tree as follows: Figures 4-5 As shown, as snapshots are continuously added to the snapshot tree, the background snapshot thread also deletes certain snapshots from the tree according to specific rules. For example, although snapshot 2 is still within the retention period, it needs to be deleted at intervals according to the interval deletion rules. After snapshot 2 is deleted, the snapshot tree will be adjusted to a balanced state to maintain query performance.

[0041] Furthermore, while inserting a new flashback snapshot, the background flashback thread performs non-linear interval deletion of the snapshot tree according to preset parameters. These preset parameters include the full retention duration, the maximum retention duration, and the retention interval. When the time interval from the generation of a flashback snapshot to the current period is less than the full retention duration, all snapshots are retained. Snapshots with a duration greater than the full retention duration but less than or equal to twice the full retention duration are retained at intervals according to the retention interval. Snapshots with a duration greater than twice the full retention duration but less than or equal to three times the full retention duration are retained at intervals of twice the retention interval, and so on, until the flashback snapshot retention duration exceeds the maximum retention duration and is then completely deleted.

[0042] For example, the specific rules for deleting snapshots are as follows: Figure 6As shown, `cur_time` is the current time; `full_ret_val` is the full retention period, which starts from the time the snapshot is generated and will be retained without deletion. Snapshots exceeding this period will enter an interval deletion phase, during which they may be deleted.

[0043] `max_ret_val` is the maximum retention period. If the retention time exceeds the maximum retention period from the time the snapshot is generated, the snapshot will definitely be deleted. `pro_time = cur_time - full_ret_val` is the starting point for interval deletion. `delete_time = cur_time - max_ret_val` is the starting point for direct deletion.

[0044] like Figure 6 As shown, snapshots 1-24 were generated along the time dimension. During each maintenance cycle, the snapshot thread determines which stage the system timestamp of a snapshot in the snapshot tree meets based on the parameters `full_ret_val` and `max_ret_val`. If it's greater than `pro_time`, it's retained; if it's between `pro_time` and `delete_time`, it's deleted at intervals; and if it's less than `delete_time`, it's deleted directly. The full retention time `full_ret_val` is 3 minutes, and the default value for the maximum retention time `max_ret_val` is 1440 minutes. The user can reset the `max_ret_val` parameter. The retention interval parameter `interval` defaults to 5 seconds.

[0045] For interval deletion rules Figure 6 This is for illustrative purposes only; the specific rules are shown in Table 1 below: Table 1. Illustration of Interval Deletion Rules:

[0046] A snapshot time period represents the time from the moment a snapshot is generated to the current moment. That is, as time progresses, each generated snapshot moves to a specific time period. Snapshots within each time period are deleted based on their corresponding retention interval. For example, snapshots within 3 minutes are not deleted (snapshots are generated at the second level). Snapshots within (3,6] minutes are retained every 5 seconds (the default interval is 5 seconds), and the others are deleted. Snapshots not deleted within (3,6] minutes that move to (6,9] minutes are retained every 10 seconds (2 x interval), and the others are deleted. This continues until the retention time of any undeleted snapshot exceeds the maximum retention time `max_ret_val`, at which point they are completely deleted. This non-linear interval deletion results in a snapshot tree with a higher density of snapshots in newer time periods and a lower density in older time periods. This is done to provide users with fast flashback functionality while preventing rapid memory expansion.

[0047] In production scenarios, users often perform flashback operations immediately after accidental operations. Therefore, snapshots with more recent time periods are retained at a higher density (all snapshots within 3 minutes are retained). This allows users to accurately flashback and query the time point before the accidental operation. For snapshots with longer time periods (e.g., several days or months ago), users only need a roughly similar time point, so only a few points are retained. For example, only one snapshot older than one day is retained per day.

[0048] When users query specific timestamps, the snapshot for that timestamp may have been deleted within the specified interval, preventing the query from being completed. To address this scenario, an approximate query and dynamic snapshot density feedback strategy are introduced. The approximate query, when the snapshot for the user-specified timestamp does not exist, will use the nearest adjacent snapshot to perform a version lookup and prompt the user with an approximate timestamp result. Simultaneously, the backend will track the number of failed searches for the user-specified timestamp.

[0049] The snapshot density dynamic feedback utilizes the number of failed user flashback queries counts from the backend statistics, categorizing them into three feedback levels—weak, medium, and strong—based on the number of failures per hour: [0-5], [5-20], and [20-+∞]. The weak level interval is a default of 5 seconds, the medium level interval is set to 3 seconds, and the strong level interval is set to 1 second. This means that the feedback level counted each hour will reset its interval value within the next hour to dynamically adjust the snapshot density across different time periods. This increases the snapshot density when the user's query error rate is high, improving query efficiency. After a user stops querying for a period of time, the snapshot density automatically drops to its lowest level, reducing memory consumption.

[0050] The logical relationship between S1 and S2 is as follows: the mapping relationship between the system timestamp and logical timestamp generated in S1 serves as the sole index source for retrieval in S2, and the logical timestamp value is only incremented and updated when the DML transaction is committed, ensuring a one-to-one correspondence between the flashback snapshot time point and the historical version chain.

[0051] S3: When receiving a flashback query request, retrieve the matching system timestamp in the snapshot tree and extract the corresponding logical timestamp, and locate the corresponding MVCC historical data version in the MVCC historical data version chain.

[0052] When a user initiates a query using the SQL flashback query syntax, the system first retrieves the flashback snapshot node in the snapshot tree whose key value corresponds to the system timestamp, and then extracts the logical timestamp value. Specifically, users can use the SQL flashback query syntax as follows: SELECT<column_name_list> FROM<table_name> AS OF TIMESTAMP <timestamp3>; like Figure 7 As shown, when querying table data with a specific system timestamp (timestamp3), the system will search for snapshots with the same timestamp3 in the snapshot tree maintained in step S1. Since step S1 maintains and balances this tree every cycle, the query efficiency is high.

[0053] If no matching flashback snapshot node is found, the flashback snapshot node with the most recent system timestamp is obtained according to the approximate query strategy, and the logical timestamp value is extracted.

[0054] After obtaining the logical timestamp value, the memory engine iterates based on the logical timestamp to locate the MVCC historical data version with the logical timestamp equal to the obtained logical timestamp value from the MVCC historical data version chain, and reads the corresponding data and returns it to the user. The MVCC historical data version chain traversal and DML transaction version writing are protected from concurrency conflicts through a read-write lock mechanism.

[0055] S4: Assemble the located MVCC historical data versions into query results and return them. Prompt the user when performing an approximate query. At the same time, feed the query results back to S2 to dynamically adjust the flashback snapshot density.

[0056] Specifically, after finding a specific snapshot, the logical timestamp within it is obtained, i.e. Figure 7 tswrite3, via Figure 7 In step S1, tswirte3 searches the data version chain within the HUB to find VER3 (data version 3 in the HUB). Once the version is found, the data is returned to the client for user use. Conflicts between version modifications (DML transactions) within the HUB and snapshot version lookups are resolved through a read-write lock mechanism to ensure consistency.

[0057] Example 2 To further verify the effectiveness of the present invention, the prior art was compared with our invention, as shown in Table 2 below: Table 2 Comparison with Existing Technologies:

[0058] The existing technology suffers from low query performance due to the fact that the version is stored on the disk, which involves disk I / O operations. This is especially true when flashback queries for versions that have been out of date, as the technology requires long-term I / O and rollback processing in the flashback log file.

[0059] Our invention, Flashback Query, processes everything in memory, without any I / O. Especially when querying older versions, the efficiency is consistent with querying the current version, resulting in high performance and stability.

[0060] Furthermore, in order to verify the beneficial effects of the present invention, the following is a performance comparison experiment conducted under simple scenarios.

[0061] Specifically, a sample table MXYT1 is selected, and a pre-written automated script performs a fixed sequence of insert, delete, and update operations on the table every second, with the content of the data modifications being random. This simulates the continuous update behavior of a database table during long-term operation.

[0062] After the above experiment ran for 24 hours, a flashback query was performed on the MXYT1 table to retrieve historical data from 24 hours ago, which was used as the test metric.

[0063] During the experiment, all tests were performed under the same hardware environment to ensure the comparability of the results. In the GBASE database system using the technology of this invention, the execution time of the flashback query was 1.095 seconds; while in other database systems that did not use this technology, the same query task took 2.22 seconds. The comparative results show that the technology of this invention can significantly shorten the query time, achieving a performance improvement of nearly 100%.

[0064] Therefore, this invention can quickly complete flashback queries of historical data even after processing long-term, high-frequency data updates, ensuring the stability and efficiency of the system in long-term operation scenarios. This effect verifies the advantages of this invention in database flashback query efficiency, demonstrating that it can effectively reduce query latency and improve the availability and real-time performance of data backtracking even after long-term, high-frequency data operations, and has practical application value for large-scale data management scenarios.

[0065] This embodiment also provides a computer device applicable to a version-based dynamic fast flashback query method in a database, including a memory and a processor; the memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions to implement the version-based dynamic fast flashback query method in a database as proposed in the above embodiment.

[0066] The computer device can be a terminal, comprising a processor, memory, communication interface, display screen, and input devices connected via a system bus. The processor provides computing and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, carrier networks, NFC (Near Field Communication), or other technologies. The display screen can be an LCD screen or an e-ink screen. The input devices can be a touch layer covering the display screen, buttons, a trackball, or a touchpad on the computer device's casing, or an external keyboard, touchpad, or mouse.

[0067] This embodiment also provides a storage medium storing a computer program that, when executed by a processor, implements a version-based dynamic fast flashback query method for a database as proposed in the above embodiments.

[0068] In summary, this invention adds an additional fast flashback query method to the traditional flashback query. It utilizes a full-memory MVCC mechanism constructed from shared memory within nodes and a dynamic non-linear flashback snapshot logging mechanism to permanently retain specific data versions in memory for fast flashback queries at any time. In a multi-node master-slave cluster scenario, each node has its own full-memory MVCC mechanism. Whether it's a short-term flashback query immediately after a mistake or a long-span query, query efficiency is fast and consistent. Especially for long-span flashback queries, it avoids lengthy disk access and log rollback processing, significantly improving query performance compared to traditional methods. The dynamic non-linear time interval snapshot retention mechanism results in denser snapshots for more recent time periods and sparser snapshots for longer periods. This effectively controls MVCC memory, preventing rapid memory expansion; simultaneously, it dynamically adjusts the snapshot interval density based on the user's flashback query performance, further improving flashback query efficiency.

[0069] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention, and all such modifications or substitutions should be covered within the scope of the claims of the present invention.

Claims

1. A version-based dynamic fast flashback query method for databases, characterized in that: include: S1: After the user submits the transaction, the MVCC historical data version chain is generated and managed using the full memory-state MVCC mechanism, and a logical timestamp is assigned to each historical data version in the MVCC historical data version chain. S2: While the MVCC historical data version chain is being generated, a background thread generates flashback snapshots containing the mapping relationship between system timestamps and logical timestamps at a preset period, and inserts the flashback snapshots into the snapshot tree structure, balancing and deleting them according to retention rules to control the snapshot density. S3: When receiving a flashback query request, retrieve the matching system timestamp in the snapshot tree and extract the corresponding logical timestamp, and locate the corresponding MVCC historical data version in the MVCC historical data version chain; S4: Assemble the located MVCC historical data versions into query results and return them. Prompt the user when performing an approximate query. At the same time, feed the query results back to S2 to dynamically adjust the flashback snapshot density.

2. The version-based dynamic fast flashback query method in a database as described in claim 1, characterized in that: S1 includes: The MVCC historical data version chain is implemented using a memory engine, which stores multiple historical data versions generated when a DML transaction is committed. Each historical data version records a logical timestamp when it was generated. The logical timestamps are uniformly allocated by the global timestamp allocator; The current data version is stored in a buffer or page, compatible with disk-based storage engines; When a particular MVCC historical data version in the MVCC historical data version chain is not visible to any transaction, it is cleaned up immediately.

3. The version-based dynamic fast flashback query method in a database as described in claim 1, characterized in that: A background flashback thread generates flashback snapshots at a fixed frequency and stores the flashback snapshots in a snapshot tree with a red-black tree structure. The flashback snapshots record the mapping relationship between the system timestamp and the logical timestamp at the time of generation. Once a flashback snapshot is generated, the MVCC historical data version with the same logical timestamp value as the flashback snapshot maintains transaction visibility during garbage collector version reclamation and cannot be cleared by the garbage collector until the corresponding flashback snapshot is deleted. The key value of the red-black tree is the system timestamp, and the time complexity of snapshot query is O(logN), where N is the number of nodes in the tree.

4. The version-based dynamic fast flashback query method in a database as described in claim 3, characterized in that: While inserting a new flashback snapshot, the background flashback thread performs non-linear interval deletion of the snapshot tree according to preset parameters. The preset parameters include full retention time, maximum retention time, and retention interval; If the time interval from the creation of a flashback snapshot to the current time is less than the full retention period, all snapshots are retained. Snapshots that are longer than the full retention period but less than or equal to twice the full retention period are retained at intervals. Snapshots that are longer than twice the full retention period but less than or equal to three times the full retention period are retained at twice the retention interval, and so on, until the flashback snapshot retention period exceeds the maximum retention period and is then deleted.

5. A version-based dynamic fast flashback query method for a database as described in claim 1, characterized in that: During the snapshot tree maintenance process, if the snapshot corresponding to the specified system timestamp queried by the user has been deleted at intervals, an approximate query strategy is executed. The approximate query strategy includes: automatically selecting the existing flashback snapshot closest to the system timestamp from the snapshot tree for version retrieval, extracting the corresponding logical timestamp from the flashback snapshot, returning the system timestamp of the approximate flashback snapshot to the user, and prompting the user that the flashback snapshot corresponding to the system timestamp originally queried no longer exists.

6. The version-based dynamic fast flashback query method in a database as described in claim 5, characterized in that: When a user initiates a query using the SQL flashback query syntax, the system first retrieves the flashback snapshot node with the key value corresponding to the system timestamp in the snapshot tree and extracts the logical timestamp value. If no matching flashback snapshot node is found, the flashback snapshot node with the most recent system timestamp is obtained according to the approximate query strategy, and the logical timestamp value is extracted.

7. A version-based dynamic fast flashback query method for a database as described in claim 6, characterized in that: After obtaining the logical timestamp value, the memory engine iterates based on the logical timestamp to locate the MVCC historical data version with the logical timestamp equal to the obtained logical timestamp value from the MVCC historical data version chain, and reads the corresponding data and returns it to the user. The MVCC historical data version chain traversal and DML transaction version writing are protected from concurrency conflicts through a read-write lock mechanism.

8. The version-based dynamic fast flashback query method in a database as described in claim 7, characterized in that: The logical relationship between S1 and S2 is as follows: the system timestamp and logical timestamp mapping relationship generated in S1 serves as the sole index source for retrieval in S2, and the logical timestamp value is only incremented and updated when the DML transaction is committed, ensuring a one-to-one correspondence between the flashback snapshot time point and the historical version chain.

9. A computer device comprising a memory and a processor, wherein the memory stores a computer program, characterized in that: When the processor executes the computer program, it implements the steps of a version-based dynamic fast flashback query method in the database according to any one of claims 1 to 8.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by the processor, it implements the steps of a version-based dynamic fast flashback query method in the database as described in any one of claims 1 to 8.

Images