TPC BENCHMARK (TM) H (779138), страница 14
Текст из файла (страница 14)
Verify that the appropriate rows have not been changed in the ORDERS,LINEITEM, and HISTORY tables.3.3Consistency Requirements3.3.1Consistency Property DefinitionConsistency is the property of the application that requires any execution of transactions to take the database fromone consistent state to another.3.3.2Consistency Condition3.3.2.1 A consistent state for the TPC-H database is defined to exist when:TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 72O_TOTALPRICE =SUM(trunc(trunc(L_EXTENDEDPRICE *(1 - L_DISCOUNT),2) * (1+L_TAX),2))for each ORDERS and LINEITEM defined by (O_ORDERKEY = L_ORDERKEY)3.3.2.2 A TPC-H database, when populated as defined in Clause 4.2, must meet the consistency condition defined in Clause3.3.2.1.3.3.2.3 If data is replicated, as permitted under Clause 1.5.7, each copy must meet the consistency condition defined inClause 3.3.2.1.3.3.3Consistency TestsTo verify the consistency between the ORDERS, and LINEITEM tables, perform the following steps:1.Verify that the ORDERS, and LINEITEM tables are initially consistent as defined in Clause 3.3.2.1, based on arandom sample of at least 10 distinct values of O_ORDERKEY.2.Submit at least 100 ACID Transactions from each of at least the number of execution streams ( # query streams+ 1 refresh stream) used in the reported throughput test (see Clause 5.3.4).
Each transaction must use values of(O_KEY, L_KEY, DELTA) randomly generated within the ranges defined in Clause 3.1.6.2. Ensure that all thevalues of O_ORDERKEY chosen in Step 1 are used by some transaction in Step 2.3.Re-verify the consistency of the ORDERS, and LINEITEM tables as defined in Clause 3.3.2.1 based on thesame sample values of O_ORDERKEY selected in Step 1.3.4Isolation Requirements3.4.1Isolation Property DefinitionIsolation can be defined in terms of the following phenomena that may occur during the execution of concurrentdatabase transactions (i.e., read-write transactions or read-only queries):P0 (“Dirty Write”): Database transaction T1 reads a data element and modifies it.
Database transaction T2then modifies or deletes that data element, and performs a COMMIT. If T1 were to attempt to reread the data element, it may receive the modified value from T2 or discover that the data elementhas been deleted.P1 (“Dirty Read”): Database transaction T1 modifies a data element. Database transaction T2 then readsthat data element before T1 performs a COMMIT. If T1 were to perform a ROLLBACK, T2 willhave read a value that was never committed and that may thus be considered to have never existed.P2 (“Non-repeatable Read”): Database transaction T1 reads a data element. Database transaction T2 thenmodifies or deletes that data element, and performs a COMMIT.
If T1 were to attempt to re-readthe data element, it may receive the modified value or discover that the data element has beendeleted.P3 (“Phantom”): Database transaction T1 reads a set of values N that satisfy some <search condition>.Database transaction T2 then executes statements that generate one or more data elements thatsatisfy the <search condition> used by database transaction T1. If database transaction T1 were torepeat the initial read with the same <search condition>, it obtains a different set of values.Each database transaction T1 and T2 above must be executed completely or not at all.The following table defines four isolation levels with respect to the phenomena P0, P1, P2, and P3.Phenomena P0Phenomena P1Phenomena P2Phenomena P3Level 0Not PossiblePossiblePossiblePossibleLevel 1Not PossibleNot PossiblePossiblePossibleLevel 2Not PossibleNot PossibleNot PossiblePossibleTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 73Level 3Not PossibleNot PossibleNot PossibleNot PossibleTable 1: Isolation LevelsThe following terms are defined:T1 = An instance of the ACID Transaction;T2 = An instance of the ACID Transaction;T3 = Any of the TPC-H queries 1 to 22 or an instance of the ACID query;Tn = Any arbitrary transaction.Although arbitrary, the transaction T n shall not do dirty writes.The following table defines the isolation requirements that must be met by TPC-H implementations.Req.
#For transactions inthis set:these phenomena:must NOT be seenby this transaction:Textual Description:1.{ Ti, Tj} 1 i,j 2P0, P1, P2, P3TiLevel 3 isolation between any two ACIDTransactions.2.{ Ti, Tn} 1 i 2P0, P1, P2TiLevel 2 isolation for any ACID Transactionrelative to any arbitrary transaction.3.{ Ti, T3}1 i nP0, P1TiLevel 1 isolation for any of TPC-H queries1 to 22 relative to any ACID Transactionand any arbitrary transaction.Table 2: Isolation RequirementsSufficient conditions must be enabled at either the system or application level to ensure the required isolationdefined above is obtained.However, the required isolation levels must not be obtained by the use of configurations or explicit session-leveloptions that give a particular session or transaction a priori exclusive access to the database.The intent is not to preclude automatic mechanisms such as lock escalation, but to disallow configurations andoptions that would a priori preclude queries and update transactions against the same database from making progressconcurrently.In addition, the configuration of the database or session-level options must be such that the continuous submissionof arbitrary (read-only) queries against one or more tables could not indefinitely delay update transactions affectingthose tables from making progress.3.4.2Isolation TestsFor conventional locking schemes, isolation shall be tested as described below.
Systems that implement other isolation schemes may require different validation techniques. It is the responsibility of the test sponsor to disclose thosetechniques and the tests for them. If isolation schemes other than conventional locking are used, it is permissible toimplement these tests differently provided full details are disclosed.The six tests described here are designed to verify that the system under test is configured to support the requiredisolation levels, as defined in Clause 3.4.1.
All Isolation Tests are performed using a randomly selected set of values(P_KEY, S_KEY, O_KEY, L_KEY, DELTA).Comment: In the isolation tests, the values returned by the ACID Transaction are the old values, as read before theupdates.3.4.2.1 Isolation Test 1TPC BenchmarkTM H Standard Specification Revision 2.17.1Page 74This test demonstrates isolation for the read-write conflict of a read-write transaction and a read-only transactionwhen the read-write transaction is committed.
Perform the following steps:1.Start an ACID Transaction Txn1 for a randomly selected O_KEY, L_KEY, and DELTA.2.Suspend Txn1 immediately prior to COMMIT.3.Start an ACID Query Txn2 for the same O_KEY as in Step 1. (Txn2 attempts to read the data that has just beenupdated by Txn1.)4.Verify that Txn2 does not see Txn1's updates.5.Allow Txn1 to complete.6.Txn2 should now have completed.3.4.2.2 Isolation Test 2This test demonstrates isolation for the read-write conflict of a read-write transaction and a read-only transactionwhen the read-write transaction is rolled back.
Perform the following steps:1.Start an ACID Transaction Txn1 for a randomly selected O_KEY, L_KEY, and DELTA.2.Suspend Txn1 immediately prior to COMMIT.3.Start an ACID Query Txn2 for the same O_KEY as in Step 1. (Txn2 attempts to read the data that has just beenupdated by Txn1.)4.Verify that Txn2 does not see Txn1's updates.5.Force Txn1 to rollback.6.Txn2 should now have completed.3.4.2.3 Isolation Test 3This test demonstrates isolation for the write-write conflict of two update transactions when the first transaction iscommitted.
Perform the following steps:1.Start an ACID Transaction Txn1 for a randomly selected O_KEY, L_KEY, and DELTA1.2.Stop Txn1 immediately prior to COMMIT.3.Start another ACID Transaction Txn2 for the same O_KEY, L_KEY and for a randomly selected DELTA2.(Txn2 attempts to read and update the data that has just been updated by Txn1.)4.Verify that Txn2 waits.5.Allow Txn1 to complete. Txn2 should now complete.6.Verify thatTxn2.L_EXTENDEDPRICE = Txn1.L_EXTENDEDPRICE+(DELTA1 * (Txn1.L_EXTENDEDPRICE / Txn1.L_QUANTITY))3.4.2.4 Isolation Test 4This test demonstrates isolation for the write-write conflict of two update transactions when the first transaction isrolled back.
Perform the following steps:1.Start an ACID Transaction Txn1 for a randomly selected O_KEY, L_KEY, and DELTA1.2.Stop Txn1 immediately prior to COMMIT.3.Start another ACID Transaction Txn2 for the same O_KEY, L_KEY and for a randomly selected DELTA2.(Txn2 attempts to read and update the data that has just been updated by Txn1.)4.Verify that Txn2 waits.5.Force Txn1 to rollback. Txn2 should now complete.6.Verify thatTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 75Txn2.L_EXTENDEDPRICE = Txn1.L_EXTENDEDPRICE3.4.2.5 Isolation Test 5This test demonstrates the ability of read and write transactions affecting different database tables to make progressconcurrently.1.Start an ACID Transaction Txn1 with randomly selected values of O_KEY, L_KEY and DELTA.2.Suspend Txn1 immediately prior to COMMIT.3.Start a transaction Txn2 that does the following:4.Select random values of PS_PARTKEY and PS_SUPPKEY.
Return all columns of the PARTSUPP table forwhich PS_PARTKEY and PS_SUPPKEY are equal to the selected values.5.Verify that Txn2 completes.6.Allow Txn1 to complete. Verify that the appropriate rows in the ORDERS, LINEITEM and HISTORY tableshave been changed.3.4.2.6 Isolation Test 6This test demonstrates that the continuous submission of arbitrary (read-only) queries against one or more tables ofthe database does not indefinitely delay update transactions affecting those tables from making progress.1.Start a transaction Txn1. Txn1 executes Q1 (from Clause 2.4) against the qualification database where the substitution parameter [delta] is chosen from the interval [0 .. 2159] so that the query runs for a sufficient length oftime.Comment: Choosing [delta] = 0 will maximize the run time of Txn1.2.Before Txn1 completes, submit an ACID Transaction Txn2 with randomly selected values of O_KEY, L_KEYand DELTA.If Txn2 completes before Txn1 completes, verify that the appropriate rows in the ORDERS, LINEITEM and HISTORY tables have been changed.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
