HTAP database query consistency verification method, system and application based on rotation transaction load and deterministic analysis query

Abstract

The application discloses an HTAP database query consistency verification method based on a self-rotating transaction load and a deterministic analysis query, and comprises the following steps: step one, identifying and analyzing the HTAP database processing real-time analysis query execution consistency problems; step two, through the self-rotating transaction load, the database state is periodically restored to the initial state; step three, a functional data generation method is used to generate a data set, a deterministic analysis query is defined, the database system internal query execution logic is simulated, different query semantics are processed and unified; step four, in combination with the self-rotating transaction load and the deterministic analysis query, the deterministic analysis query is initiated when the database state is restored to the initial state each time, the actual query result is compared with the expected ideal result or the query results between multiple same semantics, and the query consistency verification is completed. The application also provides a verification system and application for realizing the above method.

Description

Technical Field

[0001] This invention belongs to the field of database query verification technology, and relates to an HTAP database query consistency verification method, system and application based on self-rotating transaction load and deterministic analysis query. Background Technology

[0002] For more than 40 years, transaction processing and analytical querying have been two key services provided by database systems. As applications demand real-time data analysis and querying, such as fraud detection [3,4], business intelligence [5] and healthcare [6], HTAP database systems (hybrid load processing systems) have emerged, which are database systems that support both transaction load and analytical query processing. In HTAP database systems, in order to make analytical queries real-time, analytical queries are given ACID properties, thereby breaking down the barriers between transaction load processing and analytical query processing. However, after mixing transaction processing and analytical query processing, the dependencies between internal components of HTAP database systems become more complex, and this complexity makes it easier to hide subtle code defects. For example, in order to achieve simultaneous processing of transaction load and real-time query analysis under various possible failure conditions, TiDB [2] has implemented a series of mechanisms such as two-phase commit, consensus protocol and massive parallel processing on two completely different storage engines. The task processing will involve a complex combination of multiple remote machines, which can easily lead to some obscure code defects. Furthermore, the HTAP database system is responsible for transferring fresh data generated by OLTP workloads to OLAP workloads for real-time data analysis, discovering value in fresh data as early as possible. If the execution results of real-time analysis queries in the HTAP database system contain code defects, this could provide incorrect analysis results to the application, causing serious consequences in production. Therefore, validating the correctness of the execution results of real-time analysis queries in the HTAP database system is a crucial task.

[0003] Many existing related works are dedicated to generating analytical queries to test the correctness of database query execution. However, these methods are not suitable for testing the correctness of HTAP databases in handling real-time analytical queries. The reason is that these works can only generate a small number of static databases. In contrast, in real HTAP workloads, databases are often large in volume and the data state changes dynamically with the execution of OLTP workloads. For example, SQLSmith[1] generates a large number of analytical queries by randomly concatenating SQL expressions to check whether the database system will crash. SQLSmith does not generate update operations that modify the database state, which means that the database is static and immutable in the SQLSmith test environment. SQLancer[2] constrains a query to access a row of target data as a prerequisite for query correctness verification. However, similar to SQLSmith, SQLancer does not generate update operations that modify the database state, and all operations are executed serially in a single thread. Therefore, SQLancer is not suitable for testing the isolation level of HTAP database systems. Cobra[8] proposed using an SMT solver and hardware acceleration to verify the serializability of key-value stores. To improve verification efficiency, it injects barrier transactions into the application workload, which limits the use cases and increases the verification cost significantly. Elle[9] specifically designs some short workloads to infer the historical version order, thereby verifying the correctness of transaction processing. However, it cannot support verification of arbitrary application workloads. IsoDiff

[10] reduces the cost of detecting loops by searching only a representative subset, thereby helping developers debug anomalies caused by weak isolation levels, but it has the problem of false positives.

[0004] Verifying the correctness of real-time analytical query execution in HTAP databases presents numerous challenges. This invention summarizes three key challenges. First, in pursuit of ultimate performance and excellent interactivity, mainstream database systems' transaction processing exhibits uncertain scheduling characteristics. Specifically, even under the same input load, the final database state may differ. Furthermore, since the execution result of an analytical query depends on the database state at the time the query is initiated, the query result itself is uncertain. This renders traditional comparative testing methods unsuitable for verifying the correctness of database system transaction processing functionality. Therefore, the first key challenge is: how to verify the correctness of an analytical query execution under conditions of uncertain database state transitions. Second, due to the specific needs of analytical decision-making business scenarios, real-time analytical queries often require access to massive amounts of data. For example, an analytical query might need to access a large amount of historical order data to serve enterprise decision-making. Therefore, the second key challenge is: how to efficiently verify the correctness of an analytical query under conditions of accessing massive amounts of data. Third, analytical queries often possess diverse and complex semantics, including various logical operations (AND, OR, NOT, etc.) and various operators (join, aggregation, sorting, etc.). Therefore, the third key challenge is: how to find a general method to verify the correctness of execution results for analytical queries with different semantics. Summary of the Invention

[0005] This invention provides a method for verifying the consistency of query execution results in HTAP workloads by designing an isolation level testing tool for HTAP database systems. The objective of this invention can be summarized as: to perform consistency verification on the analytical query results of HTAP databases, thereby identifying potential problems in the HTAP databases as early as possible. The key issues are: how to perform consistency verification on the analytical query results of HTAP databases under conditions of interleaved execution of transactional and analytical workloads.

[0006] This invention proposes an HTAP database query consistency verification method based on self-rotating transaction load and deterministic analysis query, the flowchart of which is shown below. Figure 1 As shown, the method includes the following steps:

[0007] Step 1: Identify, analyze, and verify issues related to the consistency of real-time analysis query execution in HTAP database processing, including uncertain data state migration, massive amounts of data, and diverse query semantics.

[0008] Step 2: To address the challenges posed by uncertain database state migration to the verification task, a self-rotating transaction load is used to enable the database state to be periodically restored to its initial state.

[0009] Step 3: A functional data generation method is used to generate the dataset. By defining a deterministic analytical query, the internal query execution logic of the database system is simulated to handle and unify different query semantics. Specifically, to address the challenges posed by massive amounts of data to the verification task, a functional data generation method is proposed—a data generation method that utilizes modern high-speed CPUs to compute the initial data of the database, thereby quickly generating massive amounts of data. On the other hand, to address the challenges posed by diverse query semantics, a deterministic analytical query is proposed. The deterministic analytical query is based on the functional data generation method and simulates the internal query execution logic of the database system to handle various query operators with different semantics, thereby calculating an ideal query result for the analytical query. If the actual query result of the database system is inconsistent with the ideal query result, then the query execution engine of the database system has a code defect.

[0010] Step 4: By combining self-rotating transactional load and deterministic analysis queries, a deterministic analysis query is initiated each time the database state is restored to its initial state. The actual results of each query are compared with the expected ideal results or the results of multiple queries with the same semantics to complete the query consistency verification. In Step 4, an HTAP workload that executes OLTP and OLAP workloads in parallel can be constructed, and the OLAP workload is a deterministic analysis query whose execution results are verifiable.

[0011] Step one addresses three issues. First, given the same input load, the database state and analytical query results are uncertain: to achieve optimal performance and good interactivity, mainstream database systems employ deferred scheduling in their transaction processing. Specifically, even under the same input load, the final database state may differ. Furthermore, since the execution result of an analytical query depends on the database state at the time the query is initiated, the query result itself is also uncertain. This renders traditional comparative testing methods unsuitable for verifying the correctness of the database system's transaction processing functionality. Therefore, the uncertainty of database state transitions and the consistency of verifying an analytical query result present a contradiction. Second, it is impossible to quickly verify the consistency of every piece of data read by an analytical query: due to the specific needs of analytical decision-making business scenarios, real-time analytical queries often require access to massive amounts of data, making it extremely difficult to quickly verify the consistency of every piece of data read by an analytical query. For example, an analytical query might need to access a large amount of historical order data to support enterprise decision-making. Third, different logical operations and / or different combinations of operators generate different semantics: analytical queries often have diverse and complex semantics, including various logical operations (AND, OR, NOT, etc.) and / or various operators (join, aggregation, sorting, etc.), which makes it very difficult to verify analytical queries with different semantics using a general method.

[0012] In step two, the implementation mechanism of the self-rotating transaction workload includes: first, collecting the write set of transactions in the original OLTP workload; then, generating several reverse transactions that restore the write set according to the initial state of the database; and finally, inserting these reverse transactions into the original OLTP workload to restore the database state to the initial state.

[0013] Step two, obtaining the self-rotating transaction load includes the following sub-steps:

[0014] Step 2.1: Divide the original transactional load W into several time-disjoint sub-loads, and then process each sub-load W... i Added to the self-rotating transaction load W R middle.

[0015] Step 2.2: Collect the write set and its initial record value set Φ generated by non-reverse transactions in each sub-load. Specifically, let set Φ be used to store the initial values ​​of the records, and check each sub-load W one by one. i For each committed transaction t, determine whether transaction t is a sub-load of the previous round W. i-1 The resulting reverse transaction; if transaction t is a reverse transaction, since the reverse transaction is used to recover the previous load W. i-1 The modified database state does not require its write set to be added to the record initial value set Φ; otherwise, if transaction t is not a reverse transaction, then it is necessary to collect every write operation w in the transaction, and for each record γ in the write set ws of write operation w, obtain the initial value of record γ from the initial database and save it in the record initial value set Φ.

[0016] Step 1.3: Divide the initial value set Φ into several subsets, namely Φ0, Φ1, ..., Φ i .

[0017] Step 1.4: Based on the initial value subset Φ of the records i Generate reverse transaction ξ(Φ) i Add to the next sub-load W i+1 middle.

[0018] In step three, the functional data generation method is as follows: For a record γ, its primary key is γ.k and its non-key attribute is γ.v. The functional data generation is defined as γ.v = F(γ.k), where F is a given unary function. The functional data generation can deterministically calculate the non-key attribute γ.v through the primary key attribute γ.k.

[0019] The deterministic analysis query, compared to direct database access, uses functional data generation to fill in records lacking non-primary key attributes into complete records through computation. It then calculates the ideal query result by simulating the internal query execution logic of the database system. Specifically, it first recursively calculates the intermediate results of all subqueries downstream of the root node of query q, i.e. If a subquery is a leaf node, then the complete record is obtained through functional data generation; combining the semantics of the root node operator of query q and the intermediate results of the subquery, the final result τ of query q is calculated. q The root node operator can be a filtering operator, aggregation operator, sorting operator, or join operator. For the filtering operator, all records in the intermediate result τ that satisfy the filtering condition θ are found as the ideal query result τ. * For aggregation operators, the corresponding aggregation algorithm θ is applied to the intermediate result τ to obtain the ideal aggregated query result τ. * Similar to aggregation operators, sorting operators sort intermediate results τ to obtain the desired query result τ. * For the join operator, a Cartesian product is performed on several intermediate results, and then the ideal query result τ that satisfies the join condition is obtained through the filtering condition θ in the join operator. * .

[0020] In step four, the method combining the self-rotating transactional load and deterministic analytical queries involves creating a query snapshot and initiating a deterministic analytical query when the self-rotating transactional load restores the database state to its initial state. This ensures that the deterministic analytical query always reads the database in its initial state. In this way, the consistency of query results can be judged by obtaining ideal query results through deterministic computation, and / or, a group of deterministic analytical queries with the same semantics can be initiated in the initial state, and their query results should be consistent. It is worth noting that since the deterministic analytical query does not change the database state, it does not affect the self-rotating transactional load's ability to satisfy the invariance of the database state. Therefore, initiating a deterministic analytical query when the self-rotating transactional load restores the database state to its initial state can simultaneously satisfy the invariance of the database state and the computability of the query results, thereby verifying the consistency of the HTAP database system's query results.

[0021] This invention also provides an analysis query consistency verification system, such as... Figure 2 As shown, the analysis query consistency verification system includes: a functional data generator, a query result calculator, a workload splitter, and a reverse transaction generator.

[0022] The functional data generator defines several mathematical functions based on the configuration items given by the user. These mathematical functions are used to establish the mapping relationship between the primary key attribute and non-primary key attribute of each record, and generate initial data to be imported into the HTAP database under test.

[0023] The query result calculator generates deterministic analysis queries that satisfy the computability of query results. Specifically, to obtain an ideal execution result for a query, a functional data generator is used to fill in records lacking non-primary key attributes into complete records through calculation. Then, an ideal query result for an operator is calculated by simulating the query execution logic inside the database system.

[0024] The workload splitter divides the original OLTP workload into several non-overlapping sub-workloads according to the configuration items given by the user.

[0025] The reverse transaction generator first collects the write set for each sub-load, then calls a functional data generator to obtain the initial value of each record in the write set, and finally generates several reverse transactions which are injected into the next sub-load. The reverse transactions restore the records in the write set to their initial state. Each sub-load undergoes similar processing in sequence, thus forming a self-rotating transaction load that can periodically restore its state.

[0026] This invention also provides a method for discovering code defects in HTAP databases.

[0027] The method for discovering code defects in an HTAP database involves splitting the HTAP workload to be verified into an OLTP workload and an OLAP workload. The OLTP workload is transformed into a self-rotating transaction workload using the method proposed in this invention, and the OLAP workload is transformed into a deterministic analytical query using the method proposed in this invention. Finally, when the self-rotating transaction workload restores the database state to its initial state, a deterministic analytical query is initiated, thereby verifying the consistency of the analytical query results and discovering code defects that were overlooked by the database developers.

[0028] The present invention also provides a hardware system for implementing the above-described consistency verification method. The hardware system includes a memory and a processor. The memory stores a computer program, and when the computer program is executed by the processor, the above-described method is implemented.

[0029] The present invention also provides 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 above-mentioned consistency verification method.

[0030] This invention also provides the application of the above-mentioned query consistency verification method, verification system, hardware system or computer-readable storage medium in HTAP database code defect detection, etc.

[0031] The beneficial effects of this invention include: the HTAP database query consistency verification system and method based on self-rotating transaction load and deterministic analytical query proposed in this invention can effectively detect weak consistency reads in current mainstream HTAP databases, effectively discover code defects in HTAP databases, and maintain low CPU and memory usage on physical machines. First, this invention identifies three challenges in addressing this problem: uncertain data state migration, massive amounts of data, and diverse query semantics. Then, it proposes a self-rotating transaction load that deterministically restores the database state to its initial state periodically under conditions of uncertain data state migration. Next, it proposes a functional data generation method to efficiently generate massive amounts of data using the high-speed processing power of modern CPUs, and proposes a deterministic analytical query that verifies semantically diverse queries by simulating the internal execution logic of the database in a general way. Finally, by combining self-rotating transaction load and deterministic analytical query, the above three challenges are solved in a single system. Attached Figure Description

[0032] 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, the 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.

[0033] Figure 1 This is a flowchart of the HTAP database query consistency verification method of the present invention.

[0034] Figure 2 This is an architecture diagram of the HTAP database query consistency verification system of the present invention.

[0035] Figure 3 This is an example of the self-rotating transaction load of the present invention.

[0036] Figure 4 This is an example of deterministic analysis query in this invention.

[0037] Figure 5 This is a test method for query execution in the HTAP database system of the present invention.

[0038] Figure 6 This invention measures the memory and CPU usage rates. Detailed Implementation

[0039] The invention will be further described in detail below with reference to the specific embodiments and accompanying drawings. Except for the contents specifically mentioned below, the processes, conditions, and experimental methods for implementing the invention are all common knowledge and general knowledge in the art, and the invention does not have any particular limitations.

[0040] Basic Concept Explanation: A database consists of the data itself and a data dictionary. In mainstream database systems, the data dictionary organizes the data in the database into a relational model. Specifically, a database contains several tables, each containing several records. Each record consists of a primary key attribute and several other attributes, with the primary key attribute uniquely identifying each record. A record is labeled as γ = ...<k,v> Here, k represents the primary key attribute of the record, and v represents other non-primary key attributes of the record. Transaction load W is a data processing pattern consisting of a large number of transactions executed in parallel. Each transaction t contains several read or write operations executed serially, and terminates with a commit c or a rollback a. Read operations retrieve several records from the database and store them in a read set, labeled r(rs, where rs is the read set); write operations write several records from the write set to the database, labeled w(ws, where ws is the write set). Write operations include three semantics: insert, delete, and update operations; therefore, write operations in transaction load cause changes in the database state. The i-th version of record γ is γ. i , and γ i+1 This is its direct successor. Before the transaction load runs, the database records exist in an initial state γ. 0 An analytical query is a data processing pattern consisting of a series of complex SQL statements. An analytical query retrieves a data version from a specific database state snapshot. The database state snapshot is consistent with the data version generated when the snapshot was created, and the snapshot is created at the start of a query. Through the database system's query processing module, these queries are transformed into semantically equivalent query plans. The query plan, in a tree structure, specifies the flow of data retrieval and processing in the database, thus obtaining the final query result. In the query plan, the leaf nodes of the tree represent data sources, and the intermediate nodes represent operators with specific processing logic. These operators process the intermediate results of downstream subqueries and obtain the final query result. This invention uses the query recursion notation q=<θ,{q0,q1,q2,…}>, where θ is the root node operator of q, and q… i It is a subquery downstream of the root node q, and the query result of q is marked as τ. q ,τ q It consists of several records, namely τ q ={γ0,γ1,γ2,…}. Based on the query plan, the query result τ q This can be obtained by recursively calculating the intermediate results of each subquery, i.e. in, It refers to all subqueries q downstream of the root node q. i The query results, if the subquery q i If it is a leaf node, then This corresponds to the data source. An HTAP workload combines OLTP and OLAP workloads running on the same database.

[0041] A Real-Time Query Execution Result Verification Method for HTAP Databases: To address the challenges of existing technologies, this invention proposes a novel method to verify the correctness of real-time analytical query execution results for HTAP databases. This method comprises three steps. First, to address the challenges posed by uncertain database state transitions to the verification task, a reverse transaction is proposed—a transaction that can restore the database state to its initial state. Furthermore, a self-rotating transaction load is implemented using reverse transactions, allowing the database state to periodically recover to its initial state. Second, to address the challenges posed by massive amounts of data to the verification task, a functional data generation method is proposed—a data generation method that utilizes modern high-speed CPUs to compute initial database data, thereby rapidly generating massive amounts of data. Third, to address the challenges posed by diverse query semantics, a deterministic analytical query is proposed. Based on the functional data generation method, the deterministic analytical query simulates the execution logic of queries within the database system to handle various semantic query operators, thereby calculating an ideal query result for the analytical query. If the actual query result of the database system is inconsistent with the ideal query result, then the query execution engine of the database system has a code defect. Finally, by combining self-rotating transactional workloads and deterministic analytical queries—that is, by initiating deterministic analytical queries each time the database state is restored to its initial state—an HTAP workload in which OLTP and OLAP workloads are executed in parallel can be constructed, and the OLAP workload is a deterministic analytical query whose execution results are verifiable.

[0042] Spinning Transaction Load: A spinning transaction load is an OLTP workload that periodically restores the database state to its initial state. The database state remains invariant before and after the spinning transaction load execution. To achieve this invariance, the write sets of transactions in the original OLTP workload are first collected. Then, several reversed transactions that restore the write sets are generated based on the initial state of the database. Finally, these reversed transactions are inserted into the original OLTP workload, thus restoring the database state to its initial state, as shown in Algorithm 1. A record is marked with an initial value of φ =<k,v> Here, k is the primary key attribute of the record, and v is the initial value of other attributes of the record. The set of initial values ​​for different rows of records is denoted as Φ = {φ0, φ1, φ2…}, where Φ stores the initial state of several rows of records in the database. A reverse transaction is defined as ξ(Φ), where Φ is a set of initial values ​​for records, and the reverse transaction restores the state of the records in Φ based on their initial values.

[0043]

[0044] The input to Algorithm 1 is the original transactional workload W and the initial database Γ, and the output is a self-rotating transactional workload W that satisfies state invariance. R Initially, the self-rotating transactional load W R The set is empty (line 1). Algorithm 1 consists of the following four steps.

[0045] The first step, Algorithm 1, divides the original transactional load W into several time-disjoint sub-loads (line 2), and then processes each sub-load W... i Added to the self-rotating transaction load W R In the second step (line 3), Algorithm 1 collects the write set and its initial record value set Φ generated by non-reverse transactions in each sub-load (lines 4-10). Specifically, Algorithm 1 uses set Φ to store the initial values ​​of records (line 4), and Algorithm 1 checks each sub-load W one by one. i For each committed transaction t (line 5), determine whether transaction t is a child load from the previous round W. i-1 The resulting reverse transaction (line 6); if transaction t is a reverse transaction, since the reverse transaction is used to recover the previous round of sub-load W i-1 The modified database state does not require its write set to be added to the initial record value set Φ; otherwise, if transaction t is not a reverse transaction, then it is necessary to collect each write operation w in the transaction (line 7). For each record γ in the write set ws of write operation w (line 8), Algorithm 1 obtains the initial value of record γ from the initial database (line 9) and saves it in the initial record value set Φ (line 10); in the third step, Algorithm 1 divides the initial record value set Φ into several subsets, namely Φ0, Φ1, ... Φ i(Line 11); Fourth step, Algorithm 1 will be based on the subset Φ of the initial records. i Generate reverse transaction ξ(Φ) i Add to the next sub-load W i+1 (Line 12)

[0046] However, due to various reasons, reversed transactions may terminate by rolling back. To achieve database state invariance, Algorithm 1 requires continuous retries to ensure that all reversed transactions are committed to the database. It is worth noting that Algorithm 1 relies on the initial database Γ, which can be stored in a standby database that does not accept any load. As stated in Theorem 1, for any transaction load, the self-rotating transaction load generated by Algorithm 1 satisfies database state invariance.

[0047] by Figure 3 The main logic of Algorithm 1 will be introduced using an example. Figure 3 In the initial database, there are two records, k0 and k1. The transactional workload W contains two transactions, t0 and t1, which write a new value to each record, k0 and k1 respectively. Algorithm 1 divides the original transactional workload W into three sub-loads, W0, W1, and W2. To achieve database state invariance, an initial set of record values ​​and a corresponding reversed transaction need to be formed for each sub-load. Sub-load W0 contains a committed transaction t0, and transaction t0 modifies the state of record k0. Therefore, in the initial set of record values ​​of sub-load W0... Records need to be added.<k0,v0> Here, v0 is the initial value of other attributes of record k0. The reverse transaction ξ0 corresponding to sub-load W0 writes record k0 back to its initial value, inserts reverse transaction ξ0 into sub-load W1, and ensures that reverse transaction ξ0 is committed. Similarly, reverse transaction ξ1 restores the database state changes made by sub-load W1 to the initial state, inserts it into the next sub-load W2, and commits reverse transaction ξ1. In this way, reverse transaction ξ0 in sub-load W1 restores the database state modifications made by sub-load W0, reverse transaction ξ1 in sub-load W2 restores the database state modifications made by sub-load W1, while sub-load W2 only contains reverse transaction ξ1 and does not make new modifications to the database state. Each reverse transaction in a sub-load is responsible for undoing the modifications made to the database by the previous sub-load, thus achieving the immutability of the database state. In terms of performance, the time and space complexity of Algorithm 1 are both O(N). w ), where N w It is the number of write records of committed transactions in the transaction load.

[0048] It is worth noting that OLTP workloads typically run continuously and uninterruptedly in a multi-threaded manner. However, Algorithm 1 divides the transactional workload into several time-disjoint sub-loads, which requires synchronizing the timing of load transmission between multiple threads, disrupting the continuous operation of the transactional workload. On the one hand, the negative impact of the division can be minimized by reasonably controlling the timing of the division; for example, the division point can be chosen at a time when the transaction volume in the workload is sparse. On the other hand, the blocking effect of synchronization on the transactional workload can be reduced by asynchronously generating reverse transactions in the background.

[0049] Theorem 1 and Algorithm 1 generate a self-rotating transaction load that satisfies the database state invariance, thus solving the problem of uncertain database migration.

[0050] Proof 1: Assume W i It is any sub-load in the original OLTP workload W. The sub-loads W can be collected through lines 4-10 of Algorithm 1. i The write set and its initial value set Φ generated by a transaction (excluding reverse transactions), that is, Φ stores the payload W. i The initial state of all modified records. Lines 11-12 of Algorithm 1 divide Φ into several disjoint subsets, and further construct the corresponding reverse transactions ξ(Φ0), ξ(Φ1), ξ(Φ2) to be added to the sub-load W of the next time period. i+1 Furthermore, it requires that all reversed transactions must be committed to the database through continuous retries.

[0051] In this way, Algorithm 1 guarantees the sub-load W i Reverse transaction in sub-load W i After execution and submission to the database, the sub-load W for the next time period... i+1 After execution, the sub-load W can be... i All modifications made to the database are restored to their initial state, so the self-rotating transaction load generated by Algorithm 1 satisfies the database state invariance.

[0052] Deterministic analytical queries: Due to the specific needs of analytical decision-making scenarios, analytical queries often require access to massive amounts of data. Traditional comparative testing methods verify the correctness of the database system's query processing functionality by executing a query on two comparison databases. However, this method suffers from numerous problems, including high testing costs and low efficiency. Specifically, creating a massive comparison database requires significant time and hardware / software costs; furthermore, executing a query on two separate databases doubles the system resource usage.

[0053] To avoid relying on a comparison database, this invention proposes a query processing function correctness verification method based on deterministic analytical query. Deterministic analytical query is a type of analytical query that calculates query results using functional computation; that is, given rules, a query's expected correct result can be calculated, and the result satisfies computability. Assuming the database's query processing function has defects, the returned query result may differ from the calculated ideal correct result. Therefore, deterministic analytical query can be used to verify the correctness of a database system's query processing function. More importantly, the correctness verification method based on deterministic analytical query does not rely on a comparison database; a correct query result can be obtained through high-speed CPU computation, greatly improving the efficiency of verifying the correctness of the database's query processing function. For record γ, its primary key is γ.k, and for the non-key attribute γ.v, functional data generation is defined as γ.v = F(γ.k), where F is a given unary function. That is, functional data generation can deterministically calculate the non-key attribute γ.v through the primary key attribute γ.k. Using functional data generation, the query result τ of any query q can be calculated. q As shown in Algorithm 2.

[0054] Algorithm 2 first recursively calculates the intermediate results of all subqueries downstream of the root node of query q, that is... (Lines 2-3); Combining the semantics of the root node operator for query q and the intermediate results of the subquery, Algorithm 2 calculates the final result τ of query q. q (Line 4), where the root node operator Θ can be a filtering operator, aggregation operator, sorting operator, or connection operator, as shown in Algorithm 2.

[0055] Algorithm 3 uses functional data generation to supplement records lacking non-primary key attributes into complete records, thereby obtaining the query result τ corresponding to the intermediate result τ through computation. * (Lines 2-3); Algorithm 3 calculates the ideal query result for an operator by simulating the internal query execution logic of a database system, including filtering operators (lines 5-9), aggregation / sorting operators (lines 10-13), and join operators (lines 14-20). For the filtering operator, Algorithm 3 finds all records in the intermediate result τ that satisfy the filtering condition θ as the ideal query result τ. * (Lines 7-8); For the aggregation operator, Algorithm 3 applies the corresponding aggregation algorithm θ to the intermediate result τ to obtain the ideal query result τ after aggregation. * (Lines 12-13); Similar to aggregation operators, sorting operators sort intermediate results τ to obtain the desired query result τ. * For the join operator, Algorithm 3 performs a Cartesian product operation on several intermediate results, and then obtains the ideal query result τ that satisfies the join condition through the filtering condition θ in the join operator. *(Lines 16-19).

[0056] Algorithm 3 is responsible for calculating intermediate results or completing records. It simulates the internal query execution logic of a database system to calculate the ideal query result for a given operator (filtering, aggregation / sorting, join). This process includes completing incomplete records to provide the necessary data for subsequent query execution logic. Algorithm 2 then uses the output of Algorithm 3 to calculate the final query result. Algorithm 2 first recursively calculates the intermediate results of all subqueries downstream of the root node of query q, calling Algorithm 3 for each intermediate result calculation. After obtaining these intermediate results, Algorithm 2 then combines the semantics of the operators at the root node of query q to calculate the final result τ. q .

[0057]

[0058]

[0059] By combining Algorithm 2 and Algorithm 3, the functional data generation method can calculate the correct result of a query according to the query plan, thus satisfying the computability of the query result and verifying the correctness of the database query processing function. Figure 4 This demonstrates an example of using functional data generation to achieve computability of query results. The query plan for the query to be computed contains two leaf nodes and two intermediate nodes. The two leaf nodes correspond to tables Y and Z, respectively. Each table contains a primary key attribute 'a' and one other attribute 'b'. Assume the cardinality of the tables is N. Y =2 and N Z =1, function F Y and F Z Functional data generation for tables Y and Z is implemented using the primary key attribute 'a'. This functional data generation allows the calculation of all data points in tables Y and Z, specifically τ0 and τ1. This is achieved by combining the filtering operator Yb<2 and the join operator. Semantically, intermediate result τ2 and final result τ3 are calculated respectively. The correctness of the query processing function can be verified by comparing the consistency between the query results returned by the database system and τ3. In terms of performance, using deterministic analysis queries, the time complexity of calculating the ideal execution result of an analysis query is O(N). θ ·N τ ·C F , where N θ N is the number of operators in a query plan. τ C is the average size of the intermediate results for each operator. F It is the cost of functional data generation to compute a single record. Thanks to the high-speed computing power of modern CPUs, C... FIt is a very small constant, so functional data generation can quickly complete the computation of massive amounts of data. In contrast, traditional testing methods require continuous I / O to read the comparison library, with a time complexity of O(N). θ ·N τ ·C IO ), where C IO The cost of accessing a record from the comparison database is several orders of magnitude higher than the CPU's computational cost, resulting in significant time overhead. To further optimize time performance, one approach is to utilize the database system's query optimizer to select an optimal query plan to complete the query result computation task. Another approach is to leverage the multi-core processing power of modern CPUs to vectorize and execute multiple operators within the query plan. The space complexity of calculating the ideal execution result of an analytical query is O(N). θ ·N τ +N F , where N F This refers to the number of functions in functional data generation, i.e., the number of non-primary key attributes, which is usually a small constant. Therefore, the space cost of Algorithm 2 mainly depends on the size of the intermediate query results. To reduce space overhead, storage space occupied by unnecessary intermediate result sets can be released in a timely manner. Specifically, after calculating the query result of a certain operator, the downstream intermediate result set can be released in a timely manner to minimize the space consumption of intermediate results.

[0060] Construction of HTAP Workload: To extract value from real-time data, HTAP database systems break down the barriers between OLTP and OLAP workloads, enabling analytical queries to obtain fresh data generated by transaction processing in real time. However, this presents a significant challenge to testing the correctness of query execution in HTAP database systems. On one hand, the uncertainty of transaction scheduling introduces uncertainty into the evolution of the database state; on the other hand, the relational model involves complex and diverse relational algebra operations. Fortunately, leveraging the database state invariance inherent in self-rotating transaction loads, the database state can be restored to its initial state periodically; and utilizing the computability of query results in the initial state of the database by deterministic analytical queries, ideal query results can be obtained through functional data generation calculations. Based on the properties of self-rotating transaction loads and deterministic analytical queries, this invention proposes a testing method for the correctness of real-time analytical query execution in HTAP database systems, such as... Figure 5As shown. Specifically, when the self-rotating transaction load restores the database state to its initial state, a query snapshot is created and a deterministic analysis query is initiated. This ensures that the deterministic analysis query always reads the database from its initial state. In this way, the consistency of query results can be judged by obtaining ideal query results through deterministic computation. Furthermore, when a group of deterministic analysis queries with the same semantics are initiated in the initial state, their query results should be consistent. It is worth noting that since the deterministic analysis query does not change the database state, it does not affect the self-rotating transaction load's ability to satisfy the invariance of the database state. Therefore, initiating a deterministic analysis query when the self-rotating transaction load restores the database state to its initial state can simultaneously satisfy the invariance of the database state and the computability of the query results, thereby verifying the consistency of query results in the HTAP database system.

[0061] It is noteworthy that the proposed HTAP database testing method has universality. Specifically, the self-rotating transaction workload's requirement for database state invariance depends neither on the internal logic of the transaction workload itself nor on the implementation details of the database system. For deterministic analytical queries, the requirement for computability of query results also does not depend on the query or the database system. Therefore, the HTAP database query consistency verification method proposed in this invention has universality and can be applied to testing tasks of different HTAP workloads and database systems. In terms of performance, the self-rotating transaction workload requires achieving database state invariance through reversed transactions, and the formation of reversed transactions requires the initial database Γ. Ingeniously, functional data generation can deterministically calculate other non-primary key attributes γ.v through the primary key attribute γ.k, thereby avoiding the need to save a backup database and reducing the storage overhead of the initial database from O(N) to O(N) Γ The value was reduced to O(N). F ), where N Γ N is the initial database size. F This refers to the number of functions in functional data generation. Besides the time and space overhead of both the self-contained transactional load and the deterministic analysis query, the self-contained transactional load needs to message-pass with the deterministic analysis query. To minimize communication overhead between the two, message passing can be implemented by setting globally shared variables. Specifically, when restoring the database state to its initial state, the self-contained transactional load sets a flag on the globally shared variable; after the deterministic analysis query intercepts the message, it initiates a query on the database; once all deterministic analysis queries have formed snapshot-related data structures in the database system, the deterministic analysis query resets the globally shared variable, allowing the self-contained transactional load to continue to the next round of state evolution; therefore, each message passing only involves the process of acquiring / releasing locks on the global variable once.

[0062] Example

[0063] Implementation Environment

[0064] In this embodiment, each server is equipped with two 2.1GHz, 8-core Intel Xeon Silver 4100 processors, and each server can run 32 threads simultaneously; each server has 120GB of memory, a 4TB hard drive (RAID-5), and 4GB of cache on the disk array; the servers are connected to each other via Gigabit Ethernet.

[0065] Implementing a database system

[0066] This embodiment compares and tests the open-source database system PostgreSQL and a mainstream commercial HTAP database system TiDB[3]. PostgreSQL has only one row storage engine and the index structure is B. + -Tree, this section deploys PostgreSQL (v12.7) on a single server. TiDB is an HTAP database system with both row storage engine (TiKV) and column storage engine (TiFlash). This section deploys TiDB (v6.0) and configures it in three-replica mode, deploying TiKV nodes on three servers, PD nodes on three servers, a TiFlash node on one server, and a TiDB node on one server.

[0067] Implementation Results and Analysis

[0068] To demonstrate the ability of this invention to verify real-time analysis query consistency, the HTAP workload generated by this invention was run on multiple databases, and the number of records read in a strong consistency manner and the number of records read in a weak consistency manner were counted. Furthermore, since providing strong consistency in a distributed system may limit system performance to some extent, many distributed databases provide weak consistency reads to balance system performance and data consistency, such as TiDB. This embodiment also runs the HTAP workload generated by this invention on a database with weak consistency reads enabled to verify whether this invention can effectively detect records with weak consistency reads. This embodiment generates two types of HTAP workloads: one type with intensive data access conflicts between OLTP and OLAP workloads, and the other type with milder data access conflicts. Specifically, in the conflict-intensive HTAP workload, the OLTP workload has a concurrency of 24 transaction threads, with each transaction reading / writing an average of 10 records; the OLAP workload has a concurrency of 24 query threads, with each query querying an average of 1000 records, and the data read and written by OLTP transactions falls entirely within the data read by OLAP queries. In a conflict-mitigated HTAP workload, the concurrency of an OLTP workload is 24 transaction threads, with each transaction reading / writing an average of 10 records. The concurrency of an OLAP workload is 24 query threads, with each query retrieving an average of 1000 records. Furthermore, the data read and written by an OLTP transaction has only a 50% probability of falling within the data range read by an OLAP query.

[0069] The implementation results are shown in Table 1. The results demonstrate that this invention is capable of detecting records read in a weakly consistent manner. Strongly consistent reads mean that transactions access the latest state of the database based on the start time as a snapshot point. However, for the Read Committed isolation level, it accesses the database's consistent snapshot based on the operation's start time, which is not a strongly consistent read. Therefore, the Read Committed isolation level for both databases cannot read all records in a strongly consistent manner. In contrast, Repeatable Read and Serializable isolation levels both access the database's consistent snapshot based on the transaction's start time, which is a strongly consistent read. The implementation results show that regardless of the intensity of conflicts, both Repeatable Read and Serializable isolation levels for both databases read all records in a strongly consistent manner. After enabling weakly consistent reads in TiDB, this invention detected a certain proportion of weakly consistent records regardless of whether the Read Committed or Repeatable Read isolation level was used. In summary, the HTAP workload generated by this invention has the ability to detect weakly consistent records, thereby verifying the consistency of real-time analytical queries.

[0070] Table 1: Query execution consistency verification for multiple databases

[0071]

[0072] To demonstrate the verification efficiency of this invention, PostgreSQL and TiDB were evaluated separately. During the evaluation, the system resource utilization of the test clients, including CPU utilization and memory utilization, was statistically analyzed. Figure 6 As shown. For PostgreSQL, increasing the number of transaction load threads improves transaction throughput, leading to increased CPU and memory usage on the test client, such as... Figure 6 (a) and Figure 6 As shown in (b). It is worth noting that, because the high throughput of transaction loads limits the throughput of analytical queries, increasing the number of analytical query threads does not increase the CPU utilization of the test client. For TiDB, system resource utilization increases with the number of threads, as shown in (b). Figure 6 (c) and Figure 6 As shown in (d). Even in the worst-case scenario, the CPU utilization of the test client is less than 15% and the memory utilization is less than 6%. Overall, the test client has a low resource consumption and can be deployed on inexpensive machines to efficiently complete the testing tasks of the HTAP database system.

[0073] Although relevant testing tools have conducted long-term tests on some popular database systems, this embodiment still found several code defects on these database systems. A typical code defect is shown in Table 2. Specifically, when creating a table, the primary key is set to a floating-point type and is set to auto-increment. OLTP workloads continuously insert three data entries into the table while ignoring the primary key data setting. In an OLAP workload, an analytical query is initiated to read these inserted data and perform aggregation operations on the read data. However, the query results show that the primary key data actually increments by 2, not by 1, causing the analytical query to produce incorrect query results when performing aggregation operations on the primary key. The cause of this code defect is that the transaction processing engine in the tested database system incorrectly performs multiple auto-increments when transmitting the missing primary key data to the analytical query engine.

[0074] Table 2: Typical Code Defects

[0075]

[0076] References

[0077] [1]SQLsmith[EB / OL]2023https: / / github.com / anse1 / sqlsmith

[0078] [2]RIGGER M,SU Z.Testing database engines via pivoted query synthesis[C] / / 14th OSDI.2020:667–682.

[0079] [3]HUANG D,LIU Q,CUI Q,et al.TiDB:a Raft-based HTAP database[J].Proceedings of the VLDB Endowment,2020,13(12):3072–3084.

[0080] [4]CAO,S.,YANG,X.,CHEN,C.,ZHOU,J.,LI,X.,ANDQI,Y.TitAnt:Online Real-Time Transaction Fraud Detection inAnt Financial.Proceedings of the VLDBEndowment 12,12(Aug.2019),2082—-2093.

[0081] [5]QIU,X.,CEN,W.,QIAN,Z.,PENG,Y.,ZHANG,Y.,LIN,X.,ANDZHOU,J.Real-TimeConstrained Cycle Detec-tion in Large Dynamic Graphs.Proc.VLDB Endow.11,12(2018),1876–1888.

[0082] [6]PAUL AMMANN,JEFF OFFUTT.2016.Introduction to softwaretesting.Cambridge University Press.

[0083] [7]RAMNARAYAN,J.,MOZAFARI,B.,WALE,S.,MENON,S.,KUMAR,N.,BHANAWAT,H.,CHAKRABORTY,S.,MAHA-JAN,Y.,MISHRA,R.,ANDBACHHAV,K.SnappyData: A HybridTransactional Analytical Store Built On Spark.InProceedings of the 2016International Conference on Man-agement ofData(2016),SIGMOD'16,pp.2153--2156.

[0084] [8]CHENG TAN,CHANGENG ZHAO,SHUAI MU,AND MICHAEL WALFISH.2020.Cobra:Making Transactional Key-Value Stores Verifiably Serializable.In OSDI.63–80.

[0085] [9]PETER ALVARO AND KYLE KINGSBURY.2020.Elle:Inferring IsolationAnomalies from Experimental Observations.Proceedings of the VLDB Endowment 14,3(2020),268–280.

[0086]

[10] YIFAN GAN,

[0087] The scope of protection of this invention is not limited to the above embodiments. Any variations and advantages that can be conceived by those skilled in the art without departing from the spirit and scope of this invention are included in this invention and are protected by the appended claims.

Claims

1. A method for verifying the consistency of HTAP database queries based on self-rotating transaction load and deterministic analysis queries, characterized in that, The method includes the following steps: Step 1: Identify, analyze, and verify issues related to the consistency of real-time analytical query execution in the HTAP database. These issues include data state uncertainty migration, large data volume, and diverse query semantics. Data state uncertainty migration refers to the uncertainty in the database state and analytical query results given the same input load conditions. Large data volume refers to the inability to quickly verify the consistency of each piece of data read by the analytical query. Diverse query semantics refers to different logical operations and / or combinations of different operators generating different semantics. Logical operators include AND, OR, and NOT; operators include join, aggregation, and sorting. Step 2: Through a self-rotating transaction workload, the database state is periodically restored to its initial state. The self-rotating transaction workload is an OLTP workload that periodically restores the database state to its initial state. Before and after the execution of the self-rotating transaction workload, the database state satisfies invariance. The implementation mechanism of the self-rotating transaction workload includes the following: First, the write sets of transactions in the original OLTP workload are collected. Then, several reverse transactions that restore the write sets are generated according to the initial state of the database. Finally, these reverse transactions are inserted into the original OLTP workload, thereby restoring the database state to its initial state. Step 3: Generate a dataset using a functional data generation method. By defining deterministic analysis queries, simulate the internal query execution logic of the database system to process and unify different query semantics. Step 4: Combining self-rotating transaction load and deterministic analysis query, initiate a deterministic analysis query each time the database state is restored to the initial state. Compare the actual results of each query with the expected ideal results, or compare the results of multiple queries with the same semantics, to complete the query consistency verification.

2. The query consistency verification method as described in claim 1, characterized in that, Obtaining the self-rotating transaction load includes the following sub-steps: Step 2.1: Transfer the original transactional workload Divide the workload into several time-disjoint sub-loads, and then process each sub-load... Add to self-rotating transaction load middle; Step 2.2: Collect the write set and its initial record value set generated by non-reverse transactions in each sub-load. ; Step 2.3: Set the initial record values. Divide into several subsets, that is ; Step 2.4: Based on the subset of initial record values Generate reverse transaction Add to the next subload middle.

3. The query consistency verification method as described in claim 1, characterized in that, In step three, the functional data generation method refers to obtaining non-primary key attributes through deterministic calculation of the primary key: for records Its primary key is Non-key value properties are Functional data generation is defined as , It is a given unary function.

4. The query consistency verification method as described in claim 1, characterized in that, In step three, the deterministic analysis query includes the following steps: first, recursively calculate the query. The intermediate results of all subqueries downstream of the root node, i.e. If a subquery is a leaf node, then the complete record is obtained through functional data generation; combined with the query The semantics of the root node operator and the intermediate results of the subquery are used to calculate the query. The final result The root node operator includes filtering, aggregation, sorting, and connection operators; for the filtering operator, intermediate results are found. The filtering conditions are met. All records are considered as ideal query results For aggregation operators, for intermediate results Apply the corresponding aggregation algorithm This allows us to obtain the desired query results after aggregation. For sorting operators, intermediate results are... Sort the results to get the desired query results. For the join operator, perform a Cartesian product operation on several intermediate results, and then pass the filtering conditions in the join operator. Obtain the ideal query results that satisfy the join conditions. .

5. The query consistency verification method as described in claim 1, characterized in that, In step four, the combination of the self-rotating transaction load and the deterministic analysis query involves establishing a query snapshot and initiating a deterministic analysis query when the self-rotating transaction load restores the database state to its initial state. This ensures that the deterministic analysis query always reads the database in its initial state and obtains the ideal query result through deterministic computation to determine the consistency of the query result; and / or, initiating multiple deterministic analysis queries with the same semantics in the initial state to complete the query consistency verification.

6. A verification system for implementing the query consistency verification method as described in any one of claims 1-5, characterized in that, The verification system includes: a functional data generator, a query result calculator, a workload splitter, and a reverse transaction generator; The functional data generator defines several mathematical functions based on the configuration items given by the user, establishes the mapping relationship between the primary key attribute and non-primary key attribute of each record, and generates initial data to be imported into the HTAP database under test. The query result calculator is used to generate deterministic analysis queries that satisfy the computability of query results; The workload splitter divides the original OLTP workload into several non-overlapping sub-workloads according to the configuration items given by the user. The reverse transaction generator is used to collect the write set of each sub-load, then call the functional data generator to obtain the initial value of each record in the write set, and finally generate several reverse transactions to inject into the next sub-load. The reverse transaction restores the records in the write set to their initial state.

7. A hardware system for implementing the method as described in any one of claims 1-5, characterized in that, The hardware system includes a memory and a processor; the memory stores a computer program that, when executed by the processor, implements the method as described in any one of claims 1-5.

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

Images