Re: Article about supposed "murky" future for Oracle

"rkusenet" <rkusenet_at_sympatico.ca> wrote in message news:<c4cifo$2h5tbc$1_at_ID-75254.news.uni-berlin.de>...
> "Thomas Kyte" <thomas.kyte_at_oracle.com> wrote
>
> > Well, I didn't prove that really -- the point I made on page 35 was
> > that
>
> ha ha. this is getting better. So what Daniel Morgan has been doing
> is blindly believing oracle marketing hype. Not very smart of him.
>

I would hardly call it marketing hype. I know how the other databases work, I've worked with them as well

>
> > "in practically every other database, if you wanted to get a
> > 'consistent' and 'correct' answer to this query, you would either have
> > to lock the whole table while the sum was calculated or you would have
> > to lock the rows as you read them"
>
> true. But in Informix we use scroll cursor to do it.
>
> BEGIN WORK
> DECLARE SCROLL WITH HOLD CURSOR FOR SELECT ....
> COMMIT WORK
>
> The lock is held for a very short duration of time and COMMIT WORK
> releases the lock. However WITH HOLD retains the cursor after
> COMMIT and which become an immutable copy for that session as long
> as it wants.

the problem is -- unless it holds the lock until it resolves the entire query (or locks all rows up front) you are *not* getting a read consistent result set. (and if it does hold the lock, i would hardly call that "very short" if it takes 5 minutes for my query to execute)

with hold is a fancy way of saying "please copy my query into a temp table"

so, either informix is locking all the data as it reads it and keeps it locked until it has copied all of the data somewhere....... (leading to deadlocks of READERS with WRITERS, leading to READS blocked by WRITES, leading to WRITES blocked by READS) or -- you have not gotten a consistent result set.

If you are using that, and believing you are getting read consistent result sets, you are "mistaken"

>
> Instead of SCROLL CURSOR, a TEMP TABLE can also be created for same.

and how would a temp table (which is what a scroll cursor in informix is doing under the covers -- copying the data off to the side) solve anything?

As you are reading the data that makes up your result set -- lets say it is three enourmous rows -- what happens if:

1. you read row 1 in your query. It is for account=55, account_type=savings, balance=100
2. you move onto row 2 values = (55,checking_2,1000). You did not lock row 1 (assumption, you said it was short)
3. another transaction comes and updates row 1, moves $50 from row 1 to row 3 (simple transfer)... commits
4. you resume, you get to row 3, it now has $50 from row 1. Your result set has $50 more showing for this account=55 then *ever existed* in the data -- ever.

Now, if you tell me -- well, we'd lock row 1 of course and them commit after we got the entire query, so that would not happening.

Then I'd say "thats an enterprise solution?" you would totally just throw transaction semantics out the window in hopes of a read consistent result set? You'd commit prematurely -- even if your transaction isn't really done? How would you mix reads and writes together. And if the query took 5 minutes to run -- is that "a short time"

Unless they grow their locks until the entire result set is materialized -- you are not getting read consistent results from them. (and scroll cursors do not lock the source data while building the temp table -- meaning, they are not read consistent)

>
> I am sure same technique will work just fine in SQLServer.
>
> Bottomline: this was never an issue in any enterprise RDBMS.

yes it was, is, will be -- in databases that do not do multi-versioning, you must use shared read locks at the very least in order to get read consistent views of data. Received on Tue Mar 30 2004 - 18:13:42 CST