Re: TPC-A and -B Benchmarks

>Date: Fri, 9 Jul 93 08:56:55 +0200 >From: "Sandor (Mr. Oracle7)" <uunet!nl.oracle.com!SNIEUWEN> >Subject: Re: TPC-A and -B Benchmarks >X-Informix-List-Id: <list.2486> >> 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 of course lock all rows which are selected (always row-locks >in Oracle) and makes sure that these will not be changed. This is >always the normal procedure when an application wants to change data >after reading them. Yes, but you should also be able to scan through the data to ensure that the pre-conditions for the transaction are satisfied without having to use a SELECT FOR UPDATE, knowing that the data won't be changed by anyone else after you've scanned it. You can then go back and use the SELECT FOR UPDATE on some subset of the rows that were scanned to ensure that the preconditions were met. >When you want repeatable reads, but do not want to change data, you can >use the: > SET TRANSACTION READONLY command (ANSI standard) >This will disallow any updates within this transaction, as the name >implies (READONLY) to prevent the aforementioned problem. Other >transactions may change data however. A readonly transaction may or may not run at repeatable read isolation. The two concepts are independent. >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 in between. I disagree here. The point of repeatable read (as I understand it) is to ensure that the database appears to transaction TX1 to be running with a single user, TX1. It is not intended to pretend that TX1 is not making any changes to the database; it is intended to ensure that it seems to TX1 that only TX1 is making changes to the database. Under this definition, it is obvious that any changes made by TX1 should be reflected when the data is re-read, but no other transaction should be able to alter the data read by TX1. It is not sufficient to ensure that TX1 always sees T1/R1/V1; you also have to ensure that no other transaction creates any new version of T1/R1. >If there's just an update statement in between, 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 seems like an inconsistency. The whole point of setting the isolation level to repeatable read is to command to the database system to ensure that any modification on any table which triggers a modification on any of the data read by TX1 is disallowed, because TX1 has demanded repeatable read isolation. If this is not enforced, then what you have is not repeatable read as I understand it. The issue rapidly reduces to: what does who mean by repeatable read. Oracle supplies one definition; Informix supplies another; I can't usefully comment on 3rd party products. The two definitions differ, which is very unfortunate. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>