Re: TPC-A and -B Benchmarks

In <21j63iINNblg@emory.mathcs.emory.edu> SNIEUWEN@nl.oracle.com ("Sandor (Mr. Oracle7)") writes: >In-Reply-To: NLUNIX:pyramid!infmx!obelix.informix.com!johnl@uucp-gw-2.pa.dec.com's message of 07-08-93 15:47 >> Suppose we are using a multi-versioning system and wish to maintain a >> repeatable read isolation level on a transaction TX1 doing updates. TX1 >> reads Table1/Row1/Version1 (T1/R1/V1 for brevity). At some time later, >> some other transaction TX2 reads, updates and commits T1/R1/V1, producing >> T1/R1/V2. So far, so good. Now TX1 re-reads T1/R1 and receives the >> T1/R1/V1 -- great; TX1 has got repeatable read. >> But if TX1 now updates T1/R1/V1, what happens? Given that TX2 has made a >> committed change to the data, TX1 cannot be allowed to adjust T1/R1/V1 >> because it needs to know what was done to produce T1/R1/V2 -- it might >> change what TX1 should do. So TX1 has to be returned an error along the >> lines of "cannot update row -- stale data". If the purpose of TX1 is to >> update T1/R1, it will have to be rolled back, because no amount of waiting >> will make the data any less stale. >If you want repeatable reads to update data, you'd probably use > SELECT ... FOR UPDATE OF ... >which will ofcourse lock all rows which are selected (allways row-locks in ... unless SERIALIZABLE=TRUE or ROW_LOCKING=INTENT ... >Oracle) and makes sure that these will not be changed. This is allways the but will not prevent rows from being inserted into your cursor (unless SERIALIZABLE=TRUE). >normal procedure when an application wants to change data after reading them. >When you want repeatable reads, but do not want to change data, you can use the > SET TRANSACTION READONLY command (ANSI standard) No it's not. This is Oracle proprietary syntax. Just as SET ISOLATION TO ... is Informix proprietary. I believe the *only* ANSI standard SET statement is SET DESCRIPTOR. >This will disallow any updates within this transaction, as the name implies >(READONLY) to prevent the aforementioned problem. Other transaction may change >data however. >Personally I think it is a bit strange that if you have repeatable reads, you >could change data inbetween, isn't this what should not happen with repeatable >reads ??? The second select should return the same data, no matter what happens >inbetween. If there's just and update statement inbetween, it seems very clear >what should happen, but a modification on any other table might implicitly >trigger modifications in other tables, including the one(s) you want repeatable >reads on, which will suddenly not be repeatable anymore ! This seams like an >inconsistency. Hmmmm... As the ANSI standard doesn't support triggers (yet) I guess they haven't had to deal with this yet. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.