Re: Re Returning result of a simple "select * " from a stored procedure without using cursor

Hello Joe,

> > [VC]
> > > > > Oracle currently does not implement the SERIALIZABLE isolation
level
> > > > > in the strict sense of the word. What is known as the Oracle
> > > > > serializable isolation level, is commonly considered to be the
> > > > > SNAPSHOT IL.
> > > >
> >
> > [JW]
> > > > I agree, but many oracle customers seem to be surprised when I
describe
> > > > what actually happens, and the odd bit of dissembling that oracle
employs
> > > > in accomplishing a repeatable read.
> >
> > Please clarify what you mean by the above. Oracle's SERIALIZABLE does
> > ensure repeatable reads without resorting to 'select for update'.
>
> Oracle's SERIALIZABLE mode fakes repeatable reads by copying the data
> when it is first read, and then answering from the copy if a read is
repeated during
> the transaction. Most people I've described this to are shocked. They
expect
> the repeat read to be the *real* data. Ie: they expect it to have been
locked.

As I pointed out above, the misunderstanding is caused partly by Oracle's misnaming the IL. Should the IL have been named properly, i.e. SNAPSHOT, there'd've been perhaps less confusion.

[VC]
> > I ran both the original version verbatim and the more compact
> > electrons-saving one, and could not reproduce under Oracle 9.2:
> >
>

[JW]
> That's excellent news, but just to be sure, could you end the script
> by verifying that all rows were updated to have a 'val' of 200?

Sorry, I forgot to mention that I did check, and the result was indeeed 200.

[JW]
> Running that query in serializable mode should lock all the data, but
unless the whole
> table is locked, any inserts that occurred in the interim could change the
truth.
> MS has other simpler problems, in that it (to my last info) collects the
running
> sum as the same numeric type as the column, and if the sum of the data
> exceeds the capacity of the column type, you can get an overflow, even if
> you are asking for the *average*, which they compute by dividing the sum
> by the number. They should instead obtain the count first, and then make a
sum
> of each value/number (in a floating point format).
>
> Joe
>

No argument about the rest.

Rgds. Received on Tue Oct 21 2003 - 13:26:27 CDT