Query method and device of data and storage medium

Abstract

The application relates to a data query method and device and a storage medium, wherein the method comprises the following steps: based on a flashback query statement, a global transaction identifier (GTID) corresponding to a first time is queried in a global transaction management node (GTM); the GTM stores global transaction identifier information corresponding to a first preset time period; the first time is within the first preset time period; the flashback query statement carries the first time; the global transaction identifier information is used to represent whether a GTID within the first preset time period is active; whether the GTID corresponding to the first time is active is determined based on the global transaction identifier information; in the case that the GTID corresponding to the first time is not active, a version of data corresponding to the GTID corresponding to the first time is found in a data node (DN) through a rollback log. Through the application, the problem that the query result data of a distributed database is inconsistent when a flashback query is performed in the prior art is solved.

Description

Technical Field

[0001] This application relates to the field of computers, and more particularly to a data query method, apparatus, and storage medium. Background Technology

[0002] Flashback technology, provided by Oracle, is a general term for the ability to revert a database, either entirely or partially, to a past state, primarily to combat human error. This technology has been implemented in various databases using different methods. The main idea is to trade space for past time, storing data from a past period to retrieve data from a specific point in time. When Oracle executes a transaction, it assigns an SCN (System Change Number), which increments. The next transaction's SCN is always larger than the current transaction's. The data corresponding to the previous smaller SCN is stored in the rollback log file. When a business application requests a flashback query, it finds the historical version corresponding to the SCN in the rollback log file, thus completing the flashback query. In distributed databases, data is distributed across shards. When a flashback query is performed, some shards have already committed transactions, while others have not yet completed. The query results then show data from committed shards, while the data from uncommitted shards remains invisible, causing inconsistencies in the distributed database query results. Summary of the Invention

[0003] This application provides a data query method, apparatus, and storage medium to solve the problem of inconsistent data in distributed database query results when performing flashback queries in the prior art.

[0004] In a first aspect, this application provides a data query method, comprising: querying a Global Transaction Identifier (GTID) corresponding to a first time in a Global Transaction Management Node (GTM) based on a flashback query statement; wherein the GTM stores global transaction identifier information corresponding to a first preset time period, the first time being within the first preset time period, the flashback query statement carrying the first time, and the global transaction identifier information being used to characterize whether the GTID is active within the first preset time period; determining whether the GTID corresponding to the first time is active based on the global transaction identifier information; and if the GTID corresponding to the first time is inactive, finding the data corresponding to the version of the GTID corresponding to the first time in a Data Node (DN) through rollback logs.

[0005] Secondly, this application provides a data query device, comprising: a query module, configured to query a Global Transaction Identifier (GTID) corresponding to a first time in a Global Transaction Management Node (GTM) based on a flashback query statement; wherein the GTM stores global transaction identifier information corresponding to a first preset time period, the first time being within the first preset time period, the flashback query statement carrying the first time, and the global transaction identifier information used to characterize whether the GTID is active within the first preset time period; a determination module, configured to determine whether the GTID corresponding to the first time is active based on the global transaction identifier information; and a search module, configured to, when the GTID corresponding to the first time is inactive, search for the data corresponding to the version of the GTID corresponding to the first time in a Data Node (DN) through rollback logs.

[0006] Thirdly, an electronic device is provided, including a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus;

[0007] Memory, used to store computer programs;

[0008] When a processor executes a program stored in memory, it implements the steps of the method described in any embodiment of the first aspect.

[0009] Fourthly, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps of the method as described in any embodiment of the first aspect.

[0010] The technical solutions provided in this application have the following advantages compared with the prior art:

[0011] The method provided in this application embodiment, when querying the transaction corresponding to the first time within the first preset time period stored in GTM, can use the global transaction identifier information to indicate that the GTID corresponding to the first time is inactive, that is, after all transactions corresponding to the first time have been committed, to find the data corresponding to the GTID corresponding to the first time in the data node DN through the rollback log. This ensures data consistency and solves the problem of inconsistent data in the distributed database query results when performing flashback queries in the prior art. Attached Figure Description

[0012] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0013] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0014] Figure 1 One of the flowcharts illustrating a data query method provided in this application embodiment;

[0015] Figure 2 A second schematic flowchart illustrating a data query method provided in an embodiment of this application;

[0016] Figure 3 A flowchart illustrating the flashback query method based on a distributed database provided in this application embodiment;

[0017] Figure 4 This is a schematic diagram of the structure of a data query device provided in an embodiment of this application;

[0018] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0020] Figure 1 This is a flowchart illustrating a data query method provided in an embodiment of this application, as shown below. Figure 1 As shown, the steps of this method include:

[0021] Step 101: Based on the flashback query statement, query the GTID (Global Transaction ID) corresponding to the first time in the GTM (Global Transaction Manager Node); wherein, the GTM stores the global transaction ID information corresponding to the first preset time period. The first time is within the first preset time period. The flashback query statement carries the first time. The global transaction ID information is used to characterize whether the GTID is active within the first preset time period.

[0022] In this embodiment of the application, for the GTM associated with the database, information is sent from the CN (Compute Node) to the DN (Data Node), and one CN is associated with one or more DNs.

[0023] Step 102: Determine whether the GTID corresponding to the first time is active based on the global transaction identifier information;

[0024] Step 103: If the GTID corresponding to the first time is inactive, find the data corresponding to the version of the GTID corresponding to the first time in the data node DN through the rollback log.

[0025] It should be noted that the GTID activity level indicates whether all transactions for the corresponding DN have been committed. In other words, if the GTID is active, it means that not all transactions for the corresponding DN have been committed; if the GTID is inactive, it means that all transactions for the corresponding DN have been committed. The number of DNs can be one or more; that is, they can be DNs associated with a CN and corresponding to the GTID at that specific time.

[0026] As can be seen, in this embodiment of the application, when querying the transaction corresponding to the first time within the first preset time period stored in GTM, the global transaction identifier information can be used to find the data corresponding to the GTID corresponding to the first time in the data node DN by means of the rollback log when the GTID corresponding to the first time is inactive, that is, after all transactions corresponding to the first time have been committed. This ensures data consistency and solves the problem of inconsistent data in the distributed database query results when performing flashback queries in the prior art.

[0027] In an optional embodiment of this application, the global transaction identifier information includes: GTID and a GTID active list; for example, the GTID active list includes GTID1 (inactive), GTID2 (active), GTID3 (active), GTID4 (inactive), GTID5 (inactive), GTID6 (inactive), and GTID7 (inactive). That is, the activity of a GTID can be directly determined through the GTID active list, thereby determining whether all transactions corresponding to the GTID have been committed. Based on this, the method of determining the activity of the GTID corresponding to the first time based on the global transaction identifier information involved in step 102 of this application embodiment can further include:

[0028] Step 11: Determine the GTID corresponding to the first time from the global transaction identifier information;

[0029] Step 12: Determine whether the GTID corresponding to the first time is in the GTID active list;

[0030] Step 13: If the GTID corresponding to the first time is in the GTID active list, determine that the GTID corresponding to the first time is active.

[0031] Step 14: If the GTID corresponding to the first time is not in the GTID active list, determine that the GTID corresponding to the first time is inactive.

[0032] For example, if the GTID active list includes GTID1 (inactive), GTID2 (active), GTID3 (active), GTID4 (inactive), GTID5 (inactive), GTID6 (inactive), and GTID7 (inactive), then if the GTID corresponding to the first instance is GTID5, then GTID5 will be found to be inactive through the GTID active list. If the GTID corresponding to the first instance is GTID2, then GTID2 will be found to be active through the GTID active list. It should be noted that the above GTID active list is merely an example; the GTID active list can take other forms, and the specific settings can be adjusted according to the actual situation.

[0033] In an optional embodiment of this application, the method steps of this application further include:

[0034] Step 21: If it is determined that the GTID corresponding to the first time is active, query the global transaction identifier GTID corresponding to the first time again in GTM.

[0035] As can be seen, in this embodiment of the application, if the GTID corresponding to the first time that needs to be queried is still active, it will be queried again in GTM. That is, for active GTIDs, it will be queried repeatedly until the GTID is inactive. This ensures that the data corresponding to the version of the GTID corresponding to the first time that is found through the rollback log is a transaction that has been fully committed, thus ensuring the consistency of subsequent data.

[0036] In an optional embodiment of this application, after finding the data corresponding to the GTID version corresponding to the first time in the data node DN through the rollback log, as follows: Figure 2 As shown, the method in this application embodiment may further include:

[0037] Step 201: Clear the data stored in the data node corresponding to the first preset time period.

[0038] Step 202: Update GTM to save global transaction identifier information corresponding to the second preset time period, wherein the first preset time period is different from the second preset time period.

[0039] As can be seen from steps 201 to 202 above, if the query of the data corresponding to the transaction at the specified time point has been achieved, the data corresponding to the GTID in the rollback log that is no longer saved by GTM for the previous period (first preset time period) will be cleared in the subsequent process, so as to start a new round of querying past time points. Before that, it is also necessary to save the global transaction identifier information corresponding to another time period (second preset time period) for the new round of data query.

[0040] The following describes a specific implementation of this application, which provides a flashback query method based on a distributed database. This method involves storing GTID information for a specific time period within the distributed database using a GTM. The GTID information for a specified time point within that time period can then be queried via the GTM. This information includes the GTID and a list of active GTIDs. Finally, the GTID information is sent to each DN for activity determination. After all transactions are committed, the corresponding version of the data is found, thus realizing a distributed flashback query method.

[0041] like Figure 3 As shown, the flashback query process based on a distributed database includes the following steps:

[0042] Step 301: The client service sends an SQL (Structured Query Language) flashback query statement carrying the specified time to the CN.

[0043] Step 302: After CN parses the SQL statement, it sends a request to GTM to query the GTID information for that time point, carrying the time information.

[0044] Step 303: Upon receiving the request, GTM retrieves the GTID information corresponding to time and returns it to CN. GTM stores the GTID information for the time period from time1 to time2, where time falls between time1 and time2.

[0045] Step 304: CN receives the response from GTM carrying GTID information, and then sends the GTID along with the SQL statement to each DN;

[0046] Step 305: The DN parses the GTID and GTID active list from the GTID information and determines whether it is active. If inactive, proceed to step 306; if active, the CN enters the active retry process and continues from step 303.

[0047] Step 306: Locate the corresponding historical version information in the rollback log and return it to the CN. The CN then merges and processes the data before returning it to the client.

[0048] As can be seen, in this implementation, GTM stores GTID information for a period of time. During this period, the GTIDs are active, and GTM does not notify CN to perform a purge operation on the rollback logs of each DN. When the service is deployed, the SQL flashback query statement carries the specified "past" time point (time1 to time2). When requesting to query the GTID from GTM, this time point is pushed to GTM. GTM finds the corresponding GTID and the list of active GTID information based on this time point and returns it to CN. CN carries this GTID information to the DN node, and DN determines whether the GTID is in the active list. If it is, it indicates that the current GTID is still active, and the transaction has not been fully committed. In this case, it returns to CN to start the active retry process and restarts the query for GTID information from GTM. If it is not, it indicates that the transaction has been fully committed, and the data corresponding to the GTID is found in the rollback log and returned to CN. Thus, distributed flashback query can be implemented to query data at a specified time point while ensuring consistency. Subsequently, when GTM updates and saves GTID information for another period of time, it can notify each DN to purge the data in the rollback log corresponding to GTIDs that are no longer saved by GTM for the previous period of time. This initiates a new round of flashback queries for the "past" time points.

[0049] Based on this, in this embodiment, a global management node can be used to store global transaction ID information for a past period of time, avoiding the memory consumption of storing past transaction information locally, which would affect the performance of other SQL statements. Furthermore, by utilizing the unique global transaction ID, the transaction ID is used to determine its activity when querying data from multiple data nodes. Only after confirming that all transactions have been committed is the data retrieved from the rollback log, ensuring data consistency and achieving distributed flashback queries.

[0050] Corresponding to the above Figure 1 This application also provides a data query device, such as... Figure 4 As shown, the device includes:

[0051] The query module 42 is used to query the global transaction identifier GTID corresponding to the first time in the global transaction management node GTM based on the flashback query statement; wherein, the GTM stores the global transaction identifier information corresponding to the first preset time period. The first time is within the first preset time period, the flashback query statement carries the first time, and the global transaction identifier information is used to characterize whether the GTID is active within the first preset time period.

[0052] Module 44 is used to determine whether the GTID corresponding to the first time is active based on the global transaction identifier information;

[0053] The lookup module 46 is used to find the data corresponding to the GTID corresponding to the first time in the data node DN by means of the rollback log when the GTID corresponding to the first time is inactive.

[0054] As can be seen, in this embodiment of the application, when querying the transaction corresponding to the first time within the first preset time period stored in GTM, the global transaction identifier information can be used to find the data corresponding to the GTID corresponding to the first time in the data node DN by means of the rollback log when the GTID corresponding to the first time is inactive, that is, after all transactions corresponding to the first time have been committed. This ensures data consistency and solves the problem of inconsistent data in the distributed database query results when performing flashback queries in the prior art.

[0055] Optionally, the global transaction identifier information in this embodiment includes: GTID and GTID active list; the determining module 44 in this embodiment may further include: a first determining unit, used to determine the GTID corresponding to the first time from the global transaction identifier information; a second determining unit, used to determine whether the GTID corresponding to the first time is in the GTID active list; a third determining unit, used to determine that the GTID corresponding to the first time is active if the GTID corresponding to the first time is in the GTID active list; and a fourth determining unit, used to determine that the GTID corresponding to the first time is inactive if the GTID corresponding to the first time is not in the GTID active list.

[0056] Optionally, the apparatus in this application embodiment further includes: a second query module, used to query the global transaction identifier GTID corresponding to the first time again in GTM when it is determined that the GTID corresponding to the first time is in an active state.

[0057] Optionally, the apparatus in this application embodiment may further include: a clearing module, used to clear the data stored in the data node corresponding to the first preset time period after finding the data corresponding to the GTID version corresponding to the first time period in the data node DN through the rollback log.

[0058] Optionally, the apparatus in this application embodiment may further include: a processing module, configured to update the GTM after finding the data corresponding to the version of the GTID corresponding to the first time by searching the rollback log in the data node DN, so as to save the global transaction identifier information corresponding to the second preset time period, wherein the first preset time period is different from the second preset time period.

[0059] like Figure 5 As shown in the figure, this application provides an electronic device, including a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114.

[0060] Memory 113 is used to store computer programs;

[0061] In one embodiment of this application, when the processor 111 executes the program stored in the memory 113, it implements the data query method provided in any of the aforementioned method embodiments, and its function is similar, so it will not be described again here.

[0062] This application also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the data query method provided in any of the foregoing method embodiments.

[0063] It should be noted that, in this document, relational terms such as "first" and "second" are used merely to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0064] The above description is merely a specific embodiment of the present invention, enabling those skilled in the art to understand or implement the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the present invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A data query method, characterized in that, include: Based on the flashback query statement, the global transaction identifier (GTID) corresponding to the first time is queried in the global transaction management node (GTM). The GTM stores global transaction identifier information corresponding to a first preset time period. The first time is within the first preset time period. The flashback query statement carries the first time. The global transaction identifier information is used to characterize whether the GTID is active within the first preset time period. Based on the global transaction identifier information, determine whether the GTID corresponding to the first time is active; If the GTID corresponding to the first time is inactive, the data corresponding to the version of the GTID corresponding to the first time is found in the data node DN by searching the rollback log; The global transaction identifier information includes: GTID and a list of active GTIDs; determining whether the GTID corresponding to the first time is active based on the global transaction identifier information includes: The GTID corresponding to the first time is determined from the global transaction identifier information; Determine whether the GTID corresponding to the first time is in the GTID active list; If the GTID corresponding to the first time is in the GTID active list, it is determined that the GTID corresponding to the first time is active. If the GTID corresponding to the first time is not in the GTID active list, it is determined that the GTID corresponding to the first time is in an inactive state; The inactivity of the GTID corresponding to the first time indicates that all transactions of the data node DN corresponding to the GTID at the first time have been committed, and the number of the corresponding data node DN is greater than or equal to 1.

2. The method according to claim 1, characterized in that, The method further includes: If it is determined that the GTID corresponding to the first time is active, the global transaction identifier GTID corresponding to the first time is queried again in GTM.

3. The method according to claim 1, characterized in that, After finding the data corresponding to the GTID corresponding to the first time in the data node DN by searching the rollback log, the method further includes: Clear the data stored in the data node corresponding to the first preset time period.

4. The method according to claim 1, characterized in that, After finding the data corresponding to the GTID corresponding to the first time in the data node DN by searching the rollback log, the method further includes: The GTM is updated to save global transaction identifier information corresponding to the second preset time period, wherein the first preset time period is different from the second preset time period.

5. A data query device, characterized in that, include: The query module is used to query the Global Transaction Management Node (GTM) for the Global Transaction Identifier (GTID) corresponding to the first time based on the flashback query statement. The GTM stores the Global Transaction Identifier information corresponding to the first preset time period. The first time is within the first preset time period. The flashback query statement carries the first time. The Global Transaction Identifier information is used to characterize whether the GTID is active within the first preset time period. The determination module is used to determine whether the GTID corresponding to the first time is active based on the global transaction identifier information; The lookup module is used to find the data corresponding to the version of the GTID corresponding to the first time in the data node DN by means of the rollback log when the GTID corresponding to the first time is inactive. The global transaction identification information includes: GTID and a list of active GTIDs; the determination module includes: The first determining unit is used to determine the GTID corresponding to the first time from the global transaction identifier information; The second determining unit is used to determine whether the GTID corresponding to the first time is in the GTID active list; The third determining unit is configured to determine that the GTID corresponding to the first time is active if the GTID corresponding to the first time is in the GTID active list. The fourth determining unit is used to determine that the GTID corresponding to the first time is in an inactive state if the GTID corresponding to the first time is not in the GTID active list. The inactivity of the GTID corresponding to the first time indicates that all transactions of the data node DN corresponding to the GTID at the first time have been committed, and the number of the corresponding data node DN is greater than or equal to 1.

6. The apparatus according to claim 5, characterized in that, The device further includes: The second query module is used to query the global transaction identifier GTID corresponding to the first time again in GTM if it is determined that the GTID corresponding to the first time is in an active state.

7. An electronic device, characterized in that, It includes a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other through the communication bus; Memory, used to store computer programs; A processor, when executing a program stored in memory, implements the steps of the method described in any one of claims 1-4.

8. 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 as described in any one of claims 1-4.

Images