Re: Article about supposed "murky" future for Oracle

"rkusenet" <rkusenet_at_sympatico.ca> wrote in message news:<c4ekds$2i73rg$1_at_ID-75254.news.uni-berlin.de>...
> "Thomas Kyte" <thomas.kyte_at_oracle.com> wrote
>
> > SCROLL and WITH HOLD do not provide that.
> >
> > Repeatable Read -- that would
> > Serializable -- that would
>
> But of course. It seems I should have specified . I now understand
> your confusion. Just to recap and agree, all this is possible only with
> isolation level to repeatable read or serializable. Unforutantely I thought
> it is too trivial and obvious to mention. My bad.
>
>
> rk-

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>

you need neither scroll, nor with hold to get a read consistent result set.

What you do need is read locks -- for as long as it takes to run all of your queries. To bring scroll and with hold into the conversation and never mention "oh yeah, you need repeatable read or serializable" doesn't make sense. You don't need either of the options you put forth, you need something you never mentioned (and I wonder how many informix, sqlserver, db2, etc programmers actually even *know* that)

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.

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".

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..)

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:

1. 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)
2. 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" Received on Wed Mar 31 2004 - 14:35:21 CST