Data flashback query or flashback recovery method based on multi-version timestamp ordering mechanism

Abstract

The application discloses a data flashback query or recovery method based on a multi-version timestamp sorting mechanism, belonging to the field of distributed database backup and recovery, and the method is characterized in that: metadata and user data in a distributed database are respectively stored into a database storage module; and when a transaction in the distributed database causes any change in the metadata and the user data, the changed data and a timestamp associated with the current transaction are stored into the database storage module. By adopting the separated storage mode of the metadata and the user data in the storage module, the management of the historical data is simplified, and no additional space is occupied for storing operation logs; by setting an effective time range to periodically clean up the historical data, the effective management of the historical version is realized, the space occupation of data storage is reduced, and the flashback query of multiple data table objects can be performed once by explicitly specifying an ASOF timestamp.

Description

Technical Field

[0001] This invention relates to the field of distributed database backup and recovery, and more specifically, to a method for data flashback query or flashback recovery based on a multi-version timestamp sorting mechanism. Background Technology

[0002] Flashback functionality enables complete tracking of historical data within a specific timeframe, allowing for the storage, retrieval, and recovery of historical data. From a data tracking perspective, flashback queries can utilize historical data for security auditing and business analysis, achieving data traceability. From a data recovery perspective, flashback recovery eliminates the need for point-in-time media, allowing real-time online rollback of database objects to a point prior to the erroneous operation, ensuring data security. Flashback functionality complements data backup and recovery functionality. Data backup and recovery can be categorized in various ways, such as online / offline backup based on the database cluster status at the time of backup; physical / logical backup based on the backed-up data content; and full / incomplete recovery based on the recovered data content. Compared to these classifications, flashback functionality allows for the querying and recovery of historical data within a specific timeframe while the database cluster is online, without requiring backup and recovery operations. The ability to perform flashback operations without backup and recovery necessitates that the database cluster possess the capability to retrieve historical data versions; the database's support for storing historical data versions determines the level of support for flashback functionality. Based on the support for storing historical versions of data, different database products have developed different flashback function technical solutions by combining their different implementations of major functional modules such as data query and storage, and transaction management, and have different degrees of support for the flashback function.

[0003] Current database flashback technologies are primarily based on UNDO mechanisms, REDO logs, and Binlogs. However, these methods all have varying degrees of shortcomings in flashback querying and recovery. For example, historical data versions or logs need to be managed separately, increasing technical complexity and consuming additional storage space. Flashback functionality is often unsupported when metadata changes occur. When users need to perform numerous flashback queries, they must frequently specify historical timestamps for the operations. Therefore, further research and improvement are necessary on the implementation methods of flashback functionality in distributed databases. Summary of the Invention

[0004] One of the objectives of this invention is to address the aforementioned shortcomings by providing a data flashback query or flashback recovery method based on a multi-version timestamp sorting mechanism. This aims to solve the technical problems of existing flashback technologies, such as the need for separate management of historical versions or logs of data, which increases technical complexity and occupies additional storage space, and the inability to support flashback functionality when metadata changes.

[0005] To solve the above-mentioned technical problems, the present invention adopts the following technical solution:

[0006] The present invention provides a data flashback query or flashback recovery method based on a multi-version timestamp sorting mechanism, the method comprising the following steps:

[0007] Step A: Store the metadata and user data from the distributed database into the database storage module respectively;

[0008] Step B: When a transaction entering the distributed database causes any change in the metadata or user data, the changed data and the timestamp associated with the current transaction are stored together in the database storage module.

[0009] Step C: When a user initiates a flashback request, the distributed database parses and obtains the ASOF timestamp in the current flashback request, and then determines whether the current ASOF timestamp exceeds the valid time range of the flashback. If the determination result is yes, the current flashback request is replied with a failure message; otherwise, the metadata of the data table corresponding to the current ASOF timestamp is read, and the metadata less than or equal to the current ASOF timestamp and the user data are returned as historical data for flashback query.

[0010] As a preferred further technical solution, the method further includes step D: writing the currently returned historical data into the data table corresponding to the current flashback request as normal data, and performing flashback recovery.

[0011] A further technical solution is as follows: When a user initiates a flashback query request in step C, the transaction includes implicit transactions and explicit transactions; when an implicit transaction causes any change in the metadata or user data, it supports reading the corresponding metadata and user data according to the ASOF timestamp to perform flashback query and flashback recovery; when an explicit transaction causes any change in the metadata or user data, it supports reading the corresponding metadata and user data according to the ASOF timestamp to perform flashback query.

[0012] A further technical solution is as follows: In step C, when reading the metadata of the data table corresponding to the current ASOF timestamp: if the data table object does not exist at the current ASOF timestamp, then reply that the current flashback request execution failed; if the data table object has changed at the current ASOF timestamp, then obtain the state of the metadata of the data table before the change at the current ASOF timestamp; if the data table object has undergone multiple changes at the current ASOF timestamp, then directly obtain the metadata of the data table corresponding to the current ASOF timestamp; if multiple data table objects at the current ASOF timestamp have their own timestamps, then obtain the metadata of the corresponding data table according to the corresponding timestamps; if multiple data table objects at the current ASOF timestamp have the same timestamp, then obtain the metadata of each data table according to the current timestamp.

[0013] A further technical solution is as follows: In step B, the timestamp associated with the transaction is allocated by the distributed database based on the current system clock time using a clock algorithm; when the transaction execution is rolled back, the distributed database reallocates the timestamp for association.

[0014] A further technical solution is to store the timestamp in the database storage module as the timestamp when the transaction is finally committed.

[0015] A further technical solution is that the effective time range in step C is 25 hours, and the distributed database by default clears historical data older than 25 hours from the current time.

[0016] Compared with the prior art, one of the beneficial effects of the present invention is that, under the multi-version timestamp sorting mechanism, metadata and user data are stored separately in the storage module, which simplifies the management of historical data and eliminates the need to occupy additional space to store operation logs; by setting a valid time range to periodically clear historical data, the space occupied by data storage is reduced while achieving effective management of historical versions; and even if DDL operations are performed within the valid time range, data flashback and recovery operations can still be supported, and multiple flashback queries of multiple data table objects can also be performed by specifying an ASOF timestamp once through an explicit transaction. Attached Figure Description

[0017] Figure 1 This is a flowchart illustrating a method according to an embodiment of the present invention. Detailed Implementation

[0018] The multi-version timestamp sorting mechanism is a specific implementation of a concurrency control mechanism. This mechanism compares transaction timestamps, data read timestamps, and data write timestamps, and then applies different processing methods according to predetermined rules based on the results, thereby achieving concurrency control. Here, the timestamp can be a logical counter or a system clock. The scope of this invention applies to multi-version timestamp sorting mechanisms that combine the aforementioned system clock with a clock algorithm.

[0019] Implementing flashback functionality in a distributed database requires a data storage scheme that differs from existing solutions. Firstly, user data is stored in a schema-less manner, separate from metadata. Secondly, historical version storage of both data and metadata is implemented.

[0020] In traditional data storage methods, user data is stored in a schema-based manner, meaning the data itself also reflects its metadata. In this model, if a user performs a DDL operation, flashback functionality is often difficult to support, or even if it supports one DDL operation, it often struggles to handle multiple DDL operations. Therefore, separating user data from metadata is a necessary foundation for flashback functionality to support multiple metadata changes. Distributed databases are still a type of relational database. Relational databases store data similar to a two-dimensional table in Excel; the aforementioned metadata is like the table header, and user data is like the data in the two-dimensional table. Flashback functionality is based on storing historical versions of user data. If metadata changes are further supported, historical versions of the metadata also need to be stored to enable flashback to the point before the metadata change. This implicitly requires user data and metadata to be managed separately and stored independently. A multi-version timestamp sorting mechanism using the system clock is employed, with each transaction being assigned a unique timestamp by the database. When a database performs DML operations, the changed data and its associated transaction timestamps are persisted to the data store. This means that for the same row of data, the database stores different versions of that data at different timestamps. A normal query will retrieve the data of the version with the highest timestamp for that row. Similarly, when a database performs DDL operations, the metadata information of the table also stores multiple versions. The changed data and its associated transaction timestamps are also persisted to the data store. A normal query will retrieve all the metadata of that table, which will be the data of the version with the highest timestamp for that row. Therefore, when performing a flashback query, metadata and data with the highest timestamp less than or equal to the specified time will be returned as search results.

[0021] The aforementioned flashback functionality is the execution process of a flashback request, specifically as follows:

[0022] When a user initiates a flashback request based on the flashback syntax, the database parses the syntax and obtains the corresponding flashback timestamp (hereinafter referred to as ASOF timestamp).

[0023] Implicit transactions support flashback query and flashback recovery, which can be used by specifying the AS OF clause in the SQL statement; explicit transactions only support flashback query, and the explicit transaction can only be a read-only transaction.

[0024] When an ASOF timestamp is specified in an explicit transaction, all queries within that transaction can perform flashback queries without specifying a timestamp; the timestamp used in this case is the ASOF timestamp specified in the transaction. If a query in an explicit transaction still specifies an ASOF timestamp, but it differs from the ASOF timestamp specified in the transaction, the transaction execution will fail. Explicit transactions do not support flashback recovery; they can only perform flashback queries.

[0025] The system determines whether the flashback function is outside its valid time range based on the parsed ASOF timestamp. The database has a mechanism to clean up expired historical data versions; these versions are automatically deleted if they exceed the set time. The flashback function cannot query or recover data from deleted historical versions that have exceeded the deletion time. Furthermore, the flashback function can only query data from historical moments; it cannot use future times for flashback queries or recovery.

[0026] Based on the parsed ASOF timestamp, the metadata of the corresponding data table is read. The following scenarios are possible: 1) If the corresponding data table object does not exist at the specified time, the flashback function cannot be executed; 2) If the metadata of the specified data table has changed, the data state before the metadata change at the specified time is obtained; 3) If the metadata of the specified data table has undergone multiple changes, since there are also multiple versions of metadata, the metadata at the specified time can still be obtained; 4) If different data table objects use different ASOF timestamps, the metadata information of different data tables is queried separately based on the different timestamps; 5) If different data table objects use the same ASOF timestamp, the corresponding metadata information is also queried separately based on the corresponding ASOF timestamp of the data table.

[0027] In the timestamp sorting protocol, each transaction entering the system has a timestamp. During transaction execution, this timestamp is used to perform internal operations. If the transaction is rolled back, a new timestamp is obtained. To implement the flashback function, in addition to the transaction timestamp, each data table object involved in the query has an additional ASOF timestamp field. For non-flashback queries, the transaction timestamp is used for read and write operations; for flashback queries, the data table's metadata and data read operations use the ASOF timestamp field.

[0028] At the data storage layer, the transaction timestamp allocated during the execution of a user request is persisted along with the user data. This persisted timestamp is the final commit timestamp of the transaction. When performing a flashback query, the data corresponding to the latest committed transaction timestamp (less than or equal to the ASOF timestamp) is returned as the query data. If a write request existed before the flashback request, it indicates a read-write conflict, which is handled according to the default concurrency control strategy.

[0029] When performing a flashback recovery, the flashback query section retrieves historical data based on the ASOF timestamp, and then processes the retrieved historical data according to the normal data writing logic.

[0030] Based on the above-described flashback request execution process, one embodiment of the present invention is a data flashback query method based on a multi-version timestamp sorting mechanism, refer to... Figure 1 As shown, this method is executed in the following manner:

[0031] S1. Store the metadata and user data from the distributed database into the database storage module respectively.

[0032] S2. When a transaction entering the distributed database causes any change in the metadata or user data, the changed data and the timestamp associated with the current transaction are stored together in the database storage module.

[0033] The timestamps associated with the aforementioned transactions are assigned by the distributed database based on the current system clock time. When a transaction rollback occurs, the distributed database reallocates the timestamps for association. Furthermore, the timestamps stored in the database storage module are the timestamps at the time of the final transaction commit.

[0034] Furthermore, when reading the metadata of the data table corresponding to the current ASOF timestamp, several different scenarios may occur. For example, if the data table object does not exist at the current ASOF timestamp, the flashback request will fail. Further:

[0035] If the data table object at the current ASOF timestamp has changed, then retrieve the state of the metadata of the corresponding data table before the change at the current ASOF timestamp.

[0036] If the data table object at the current ASOF timestamp has undergone multiple changes, the metadata of the corresponding data table at the current ASOF timestamp will be retrieved directly.

[0037] If multiple data table objects at the current ASOF timestamp have their own timestamps, then the metadata of the corresponding data table can be obtained according to the corresponding timestamps.

[0038] If multiple data table objects at the current ASOF timestamp have the same timestamp, then the metadata of each data table is retrieved according to the current timestamp.

[0039] S3. When a user initiates a flashback request, the distributed database parses and obtains the ASOF timestamp from the current flashback request. It then determines whether the current ASOF timestamp exceeds the valid time range for flashback. If the result is yes, the database replies that the flashback request failed; otherwise, it reads the metadata from the table corresponding to the current ASOF timestamp, returns metadata less than or equal to the current ASOF timestamp along with the user data as historical data, and performs a flashback query. Based on the historical data cleanup mechanism of the relational database, the aforementioned valid time range is 25 hours by default; that is, the distributed database by default clears historical version data older than 25 hours from the current time.

[0040] In this step, when the user initiates a flashback query request, the aforementioned transactions include implicit transactions and explicit transactions; where an implicit transaction causes any change in the metadata or user data, it supports reading the corresponding metadata and user data according to the ASOF timestamp to perform flashback queries and flashback recovery; while an explicit transaction causes any change in the metadata or user data, it supports reading the corresponding metadata and user data according to the ASOF timestamp to perform flashback queries.

[0041] Furthermore, another embodiment of the present invention is a data flashback recovery method based on a multi-version timestamp sorting mechanism, which targets only the aforementioned implicit transactions. Specifically, based on the above steps, step S4 is further executed, characterized in that the method further includes the following step: writing the currently returned historical data into the data table corresponding to the flashback request according to the normal write logic using the current timestamp, and performing flashback recovery.

[0042] As mentioned in the above embodiments, in specific program compilation, taking the aforementioned flashback query operation as an example, its application is as follows:

[0043]

[0044]

[0045] As can be seen from the above embodiments, compared with flashback solutions based on UNDO, binlog, and other mechanisms, this invention proposes a new solution under this mechanism. Furthermore, unlike the flashback functionality under existing mechanisms, this proposal further enhances the flashback function, breaking through existing functional limitations and improving its usability. Specific technical advantages are as follows:

[0046] 1) Under the multi-version timestamp sorting mechanism, the storage layer adopts a storage method that separates data and metadata, and both data and metadata are persisted together with the transaction timestamp. This invention simplifies the management of historical versions of data and eliminates the need to occupy additional space to store operation logs. Flashback queries and recovery can be achieved using existing multi-version data. Compared with existing solutions, this reduces space consumption and lowers the technical difficulty, while also enabling the full and effective utilization of historical versions of data.

[0047] 2) Historical version data exceeding the set time range is periodically cleared through an expiration cleanup mechanism. Compared to existing solutions, this invention also achieves effective management of historical version data. The expiration cleanup mechanism ensures that transaction characteristics and concurrency control within the valid time range are not affected. Furthermore, by clearing historical version data, it reduces the space occupied by data storage, thus maintaining the design advantages of the flashback function while avoiding overlap with backup and recovery functions.

[0048] 3) When flashback queries are required for multiple database objects, multiple flashback queries for multiple objects can be performed by specifying a historical timestamp once through an explicit transaction. When a user wants to view the data status of multiple database objects at a certain point in time, the existing solution requires specifying multiple historical timestamps in multiple queries to retrieve the historical data of the corresponding object. Using the flashback function in an explicit transaction can simplify the user operation and provide an effect similar to "time travel".

[0049] 4) Flashback queries and recovery are still supported even when DDL operations exist within the valid time frame. Compared to existing solutions that often fail to support flashback queries in cases of metadata changes, this invention not only supports metadata changes but also enables low-cost support for multiple metadata changes. User errors or malicious operations can occur not only in the DML scope but also in the DDL scope, and multiple DDL operations may be performed before it is discovered that a database object's state needs to be restored to a specified point in time. Support for metadata changes greatly expands the scope of flashback functionality.

[0050] In addition to the above, it should be noted that the terms "one embodiment," "another embodiment," and "embodiment" used in this specification refer to specific features, structures, or characteristics described in connection with that embodiment, which are included in at least one embodiment described in the general description of this application. The appearance of the same expression in multiple places in the specification does not necessarily refer to the same embodiment. Furthermore, when a specific feature, structure, or characteristic is described in connection with any embodiment, the intention is to suggest that implementing such a feature, structure, or characteristic in conjunction with other embodiments also falls within the scope of this invention.

[0051] Although the invention has been described herein with reference to several illustrative embodiments, it should be understood that many other modifications and implementations can be devised by those skilled in the art, which will fall within the scope and spirit of the principles disclosed herein. More specifically, various variations and modifications can be made to the components and / or layout of the subject matter arrangement within the scope of the disclosure, drawings, and claims. Besides variations and modifications to the components and / or layout, other uses will be apparent to those skilled in the art.

Claims

1. A method for data flashback query or flashback recovery based on a multi-version timestamp sorting mechanism, characterized in that... The method includes the following steps: Metadata and user data in the distributed database are stored separately in the database storage module. When a transaction in the distributed database causes any change in either the metadata or the user data, the changed data and the timestamp associated with the current transaction are stored together in the database storage module. When a user initiates a flashback request, the distributed database parses and obtains the ASOF timestamp in the current flashback request, and then determines whether the current ASOF timestamp exceeds the valid time range of the flashback. If the result is yes, the current flashback request is replied as failed; otherwise, the metadata of the data table corresponding to the current ASOF timestamp is read, and the metadata less than or equal to the current ASOF timestamp and the user data are returned as historical data for flashback query. Write the currently returned historical data into the data table corresponding to the current flashback request as normal data, and perform flashback recovery; When a user initiates a flashback query request, the transaction includes implicit transactions and explicit transactions; when the implicit transaction causes any change in the metadata and user data, it supports reading the corresponding metadata and user data according to the ASOF timestamp to perform flashback query and flashback recovery; when the explicit transaction causes any change in the metadata and user data, it supports reading the corresponding metadata and user data according to the ASOF timestamp to perform flashback query. When the method reads the metadata of the data table corresponding to the current ASOF timestamp: If the data table object does not exist at the current ASOF timestamp, then reply that the current flashback request failed. If the data table object at the current ASOF timestamp has changed, then retrieve the state of the metadata of the corresponding data table before the change at the current ASOF timestamp; If the data table object at the current ASOF timestamp has undergone multiple changes, the metadata of the corresponding data table at the current ASOF timestamp will be retrieved directly. If multiple data table objects at the current ASOF timestamp have their own timestamps, then the metadata of the corresponding data table can be obtained according to the corresponding timestamps. If multiple data table objects at the current ASOF timestamp have the same timestamp, then the metadata of each data table is retrieved according to the current timestamp.

2. The data flashback query or flashback recovery method based on a multi-version timestamp sorting mechanism according to claim 1, characterized in that: The timestamp associated with a transaction is assigned by the distributed database based on the current system clock time using a clock algorithm; when a transaction is rolled back, the distributed database reallocates the timestamp for association.

3. The data flashback query or flashback recovery method based on a multi-version timestamp sorting mechanism according to claim 1, characterized in that: The timestamp stored in the database storage module is the timestamp when the transaction is finally committed.

4. The data flashback query or flashback recovery method based on a multi-version timestamp sorting mechanism according to claim 1, characterized in that: The effective time range is 25 hours, and the distributed database by default clears historical data older than 25 hours from the current time.

Images