Re: Article about supposed "murky" future for Oracle

"Thomas Kyte" <thomas.kyte_at_oracle.com> wrote
>
> really -- hmmm, you said clearly "informix provides scoll and with
> hold and that does this"....
>
> <quote>
> true. But in Informix we use scroll cursor to do it.
>
> BEGIN WORK
> DECLARE SCROLL WITH HOLD CURSOR FOR SELECT ....
> COMMIT WORK
> </quote>

yeah I said it. If I had known the kind of audience I am dealing with, may be I had to clarify that SCROLL CURSOR is just a way to refer to data. It has nothing to do with isolation level.

> So, tell me -- how do you do a report that gives -- oh say --
> financial data (and hence must sort of return an answer that actually
> existed in the database at some point in time) that consists of
> multiple queries -- each of which must be read consistent not only
> individually -- but with respect to eachother.

the one and only way is to use serializable read.

> and how many of your systems do you actually run in repeatable
> read/serializable? the overhead of reads blocking writes, reads
> deadlocking writes is "pretty sizeable".

at the end what counts is perception of user. Unless all this results in transaction outage, or inordinate time to complete the task, all this is under the hood as far as the user is considered, 'pretty sizeable' overhead notwithstanding.

> The locks are not held "for a very short duration". They are held for
> as long as your transaction goes on for -- which is sometimes short,
> sometimes long. I would say that you cannot permit a long running
> report to get read consistent answers in a database that is also
> processing transactions if you are using read locks. That is a
> database that would lock up faster than I can type database (and
> database is easy to type - only requires one hand even..)

fully agreed and I think I made the same clarification earlier too. From my experience this has never been a problem for thousands of rows and I had to add that application has to be designed accordingly.

> Bottom line -- this is an issue in every enterprise RDBMS that uses
> read locks to get consistent results. You have to make the choice of:
>
> a) getting an answer that never actually existed in the database at
> any point in time (but getting it without blocking writes -- albeit
> getting blocked by writes)
>
> b) maybe getting an answer that actually existed -- if you don't
> deadlock with an update, if you don't freeze the transactional part of
> the system.
>
>
> These are choices you don't make in Oracle -- that was my point. That
> is the "hype".

Versioning has its own problem too. I am sure you would have gone thru this:-

http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsistency.pdf Received on Wed Mar 31 2004 - 15:16:33 CST