Re: Snapshot problem: Update in cursor

Hi Thomas Kyte and all the rest of comp.databases.oracle.misc!

Concerning "Snapshot problem: Update in cursor", you wrote (quoting me):

> This is called Multi-versioning, Read Consistency. The instant you
> open a cursor, the result set is pre-ordained. Before any IO is
> done, before the tables are accessed, the answer is set in stone.
> This is the way it works in Oracle.

But if _I_ change a row and I fetch it a few times later and it contains not the new value - the right one in terms of _my_ actuality - this seems to be a bug and not a feature.

> >Our understanding of consistency is a different one - the one which
> >Informix shares (and the one, which complies common sense, we
> >suppose). ;-)
> common sense is in the eye of the beholder I guess.

Yes, obviously. ;-(

> Informix's answer will be different under different circumstances
> (the behaviour you noticed above not gauranteed, there are very
> clear cases when in Informix you would see 40, not 42 as well).

Yes. And because these cases are _very_ clear and _documented_, you are able to react according to this. But, and this is the thing I was talking about, there is a way to get the expected values - if you respect  the limitations.

[...several examples]
All these wonderful examples may be a problem. But these are much more complicated than ours - and they insert new rows. We just want to update a column which is _not_ part of a key or an order by. IOW: it doesn't concern "critical" columns.

Your instance sounds like "If a car is able to run 150 mph nobody can prevent that somebody drives 150 in a city - hence our cars have a maximum speed of 35 mph".

> If you consider the above as reading from the 'beginning' of the
> table to the end, what if lots of free space was at the beginning,
> some of the inserts would go to the 'front' of the table, some at
> the end. Some we would see, some we would not.

Sorry, but that's not the problem.

> Actually, in Informix, you will see changes other transactions have
> made and committed.

No. If I've started a RR-transaction nobody can change my sight of the rows I've accessed - except me.

> As you are scrolling through your cursor, you might hit a row that
> you updated way in the beginning and 5 other people have likewise
> modified since.
>
> this is a really bad feature of the default informix isolation mode
> (committed read).

Sorry, but you've forgotten to say that you can choose between different  isolation levels - another capability Oracle lacks. But that's offtopic too.

[...Bank example]
Using the right isolation level will prevent these errors.

We don't talk of unskilled users and their possibilites to commit errors. We've a problem with consistency and we try to find a solution - not a justification or comparison of Oracle and Informix.

> Oracle doesn't suffer from the above (called inconsistent analysis).
> We would return the correct aggregation of 100,000.

Informix too, if you choose the right isolation level. But that was, as already mentioned above, not the question.

Nevertheless, thanks for your answer. But the problem is still unsolved.

Perhaps there is anybody who can give us a clue?

-- 
Sincerely, Dirk Straka                    Due to email spammers, reply
                                          address is invalid -- Please
Dr. Brunthaler GmbH, Berlin               change NOSPAM to Insel. Thnx