Re: TPC-A and -B Benchmarks

"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 |> Oracle) and makes sure that these will not be changed. This is allways the |> normal procedure when an application wants to change data after reading them. You are really confusing two issues here. A RR (i.e. isolation level) is different from update/promotable locks. The idea of RR is to protect the set from modification *by any user*. Versioning does not provide this as it allows other users to change the data while your version remains the same. |> |> 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 transaction may change |> data however. And therein lies the problem. RR is intended to PREVENT other transactions from changing the data, for the length of your transaction. Ideally, it shoudl do it without having to place a table level lock (i.e. another transaction *should* be able to change data *not* in the original transaction's active set, though it cannot be allowed to change it such that it meets the original transactions query criteria (i.e. it cannot become a valid member of that set). |> |> 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. I think the example you responded to is flawed, because in paragraph 1 it states that in getting T1/R1/V1 the second time through, a RR has been achieved, and I think that is wrong. If the contents of the table have been changed (T1/R1/V2) and that change would alter the set T1/R1/V1, then RR has NOT been maintained sine the set in the table has been changed. RR allows my transaction to protect its set for the life of the transaction. There is no reason I can't update elements in that set (assuming it is not part of another transaction's set protected by RR), whether or not I use a FOR UPDATE clause. Dave Disclaimer: These opinions are not those of Informix Software, Inc. ************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney