tpcc_current (1034761), страница 13
Текст из файла (страница 13)
Databasetransaction T2 then execu tes statem ents that generate one or m ore d ata elem ents that sa tisfy the <searchcond ition> u sed by d atabase transaction T1. If d atabase transaction T1 w ere to rep eat the initial read w iththe sam e <search cond ition>, it obtains a d ifferent set of valu es.Each d atabase transaction T1 and T2 above m u st be execu ted com p letely or not at all.The follow ing table d efines fou r isolation levels w ith resp ect to the p henom ena P0, P1, P2, and P3.IsolationLevelP0P1P2P30N ot PossiblePossiblePossiblePossible1N ot PossibleN ot PossiblePossiblePossible2N ot PossibleN ot PossibleN ot PossiblePossible3N ot PossibleN ot PossibleN ot PossibleN ot PossibleThe follow ing term s are d efined :T1 = N ew -Ord er transactionT2 = Paym ent transactionT3 = Delivery transactionT4 = Ord er-Statu s transactionT5 = Stock-Level transactionTn = Any arbitrary transactionTPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 51 of 130Althou gh arbitrary, the transaction Tn m ay not d o d irty w rites.The follow ing table d efines the isolation requ irem ents w hich m u st be m et by the TPC -C transactions.Req.#1.For transactionsin this set:thesep henom ena:m u st N OT be seenby this transaction:{Ti, Tj}P0, P1, P2, P3TiLevel 3 isolation betw een N ew Ord er, Paym ent, Delivery, andOrd er-Statu s transaction s.P0, P1, P2TiLevel 2 isolation for N ew -Ord er,Paym ent, Delivery, and Ord erStatu s transactions relative to anyarbitrary transaction.P0, P1T5Level 1 isolation for Stock-Leveltransactionrelative to TPC-Ctransactions and any arbitrarytransaction.1 ≤i,j ≤4{Ti, Tn }2.1 ≤i ≤4{Ti, T5}3.1 ≤i ≤nTextu al Descrip tion:Su fficient cond itions m u st be enabled at either the system or ap p lication level to ensu re the requ ired isolationd efined above is obtained .3.4.2Isolation TestsFor conventional locking schem es, isolation shou ld be tested as d escribed below .
System s that im p lem ent otherisolation schem es m ay requ ire d ifferent valid ation techniqu es. It is the resp onsibility of the test sp onsor to d isclosethose techniqu es and the tests for them . If isolation schem es other than conventional locking are u sed , it isp erm issible to im p lem ent these tests d ifferently p rovid ed fu ll d etails are d isclosed . (Exam p les of d ifferent valid ationtechniqu es are show n in Isolation Test 7, Clau se 3.4.2.7).3.4.2.1Isolation Test 1This test d em onstrates isolation for read -w rite conflicts of Ord er-Statu s and N ew -Ord er transaction s.
Perform thefollow ing step s:1.Start a N ew -Ord er transaction T1.2.Stop transaction T1 im m ed iately p rior to COMMIT.3.Start an Ord er-Statu s transaction T2 for the sam e cu stom er u sed in T1. Transaction T2 attem p ts to read thed ata for the ord er T1 has created .4.Verify that transaction T2 w aits.5.Allow transaction T1 to com p lete. T2 shou ld now com p lete.6.Verify that the resu lts from T2 m atch the d ata entered in T1.TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 52 of 1303.4.2.2Isolation Test 2This test d em onstrates isolation for read -w rite conflicts of Ord er-Statu s and N ew -Ord er transaction s w hen the N ew Ord er transaction is ROLLED BACK.
Perform the follow ing step s:1.Perform an Ord er-Statu s transaction T0 for som e cu stom er. Let T0 com p lete.2.Start a N ew -Ord er transaction T1 for the sam e cu stom er u sed in T0.3.Stop transaction T1 im m ed iately p rior to COMMIT.4.Start an Ord er-Statu s transaction T2 for the sam e cu stom er u sed in T0.
Transaction T2 attem p ts to read thed ata for the ord er T1 has created .5.Verify that transaction T2 w aits.6.ROLLBACK transaction T1. T2 sh ou ld now com p lete.7.Verify that the d ata retu rned from T2 m atch the d ata retu rned by T0.3.4.2.3Isolation Test 3This test d em onstrates isolation for w rite-w rite conflicts of tw o N ew -Ord er transaction s. Perform the follow in gstep s:1.Start a N ew -Ord er transaction T1.2.Stop transaction T1 im m ed iately p rior to COMMIT.3.Start another N ew -Ord er transaction T2 for the sam e cu stom er as T1.4.Verify that transaction T2 w aits.5.Allow transaction T1 to com p lete.
T2 shou ld now com p lete.6.Verify that the ord er nu m ber retu rned for T2 is one greater than the ord er nu m ber for T1. Verify that thevalu e of D_N EXT_O_ID reflects the resu lt s of both T1 and T2, i.e., it has been increm ented by tw o and isone greater than the ord er nu m ber for T2.3.4.2.4Isolation Test 4This test d em onstrates isolation for w rite-w rite conflicts of tw o N ew -Ord er transaction s w hen one transaction isROLLED BACK. Perform the follow ing step s:1.Start a N ew -Ord er transaction T1 w hich contains an invalid item nu m ber.2.Stop transaction T1 im m ed iately p rior to ROLLBACK.3.Start another N ew -Ord er transaction T2 for the sam e cu stom er as T1.4.Verify that transaction T2 w aits.5.Allow transaction T1 to com p lete. T2 shou ld now com p lete.6.Verify that the ord er nu m ber retu rned for T2 is one greater than the p reviou s ord er nu m ber.
Verify thatthe valu e of D_N EXT_O_ID reflects the resu lt of only T2, i.e., it has been increm ented by one and is onegreater than the ord er nu m ber for T2.TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 53 of 1303.4.2.5Isolation Test 5This test d em onstrates isolation for w rite-w rite conflicts of Paym ent and Delivery transaction s.follow ing step s:Perform the1.Start a Delivery transaction T1.2.Stop transaction T1 im m ed iately p rior to COMMIT.3.Start a Paym ent transaction T2 for the sam e cu stom er as one of the new ord ers being d elivered by T1.4.Verify that transaction T2 w aits.5.Allow transaction T1 to com p lete.
T2 shou ld now com p lete.6.Verify that C_BALAN CE reflects the resu lts of both T1 and T2.Comment: If the Delivery bu siness transaction is execu ted as m u ltip le d atabase transaction s, then the transaction T1,in bu llet 6 above, can be chosen to be one of these d atabase transactions.3.4.2.6Isolation Test 6This test d em onstrates isolation for w rite-w rite conflicts of Paym ent and Delivery transaction s w hen the Deliverytransaction is ROLLED BACK. Perform the follow ing step s:1.Start a Delivery transaction T1.2.Stop transaction T1 im m ed iately p rior to COMMIT.3.Start a Paym ent transaction T2 for the sam e cu stom er as one of the new ord ers being d elivered by T1.4.Verify that transaction T2 w aits.5.ROLLBACK transaction T1.
T2 shou ld now com p lete.6.Verify that C_BALAN CE reflects the resu lts of only transaction T2.3.4.2.7Isolation Test 7This test d em onstrates rep eatable read s for the N ew -Ord er transaction w hile an interactive transaction u pd ates thep rice of an item . Given tw o rand om item num ber x and y, p erform the follow ing step s:1.Start a transaction T1. Qu ery I_PRICE from item s x and y. COMMIT transaction T1.2.Start a N ew -Ord er transaction T2 for a grou p of item s inclu d ing item x tw ice and item y.3.Stop transaction T2 after qu erying the p r ice of item x a first tim e and im m ed iately before qu erying thep rices of item y and of item x a second tim e.4.Start a transaction T3.
Increase the p rice of item s x and y by 10 p ercent.Case A, if transaction T3 stalls:5A.Continu e transaction T2 and verify that the p rice of item s x (the second tim e) and y m atch the valu es readby transaction T1. COMMIT transaction T2.6A.Transaction T3 shou ld now com p lete and be COMMITTED.7A.Start a transaction T4.
Qu ery I_PRICE from item s x and y. COMMIT transaction T4.TPC Benchm ark™C - Stand ard Sp ecification, Revision 5.11 - Page 54 of 1308A.Verify that the p rices read by transaction T4 m atch the valu es set by transaction T3.Case B, if transaction T3 d oes not stall and transaction T2 ROLLS BACK:5B.Transaction T3 has com p leted and has been COMMITTED.6B.Continu e transaction T2 and verify that it is instru cted to ROLL BACK by the d ata m anager.7B.Start a transaction T4. Qu ery I_PRICE from item s x and y.