Re: Article about supposed "murky" future for Oracle

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1080632385.558367_at_yasure>...
> Database Guy wrote:
>
> > Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1080534018.934078_at_yasure>...
> >
> >
> >>it is impossible for SQL Server to produce a result set consistent to a
> >>point-in-time.
> >
> >
> > Yeah? Can you prove this?
> >
> >
> > DG
>
> Yes I can: The example can be found in Tom Kyte's book
> "Expert one-on-one Oracle" so I would suggest you read it.
> Then if you have any specific questions direct them to
> http://asktom.oracle.com.

Well, I didn't prove that really -- the point I made on page 35 was that

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

In SQLServer, you can get consistent results "as of the time the query completes" using shared read locks (preventing others from modifying the data you are reading and have read until you commit)

In Oracle, you get consistent results "as of the time the query begins", without using any locks of any sort. No one is prevented from modifying the data.

SQLServer defaults to read committed -- in their model, that is an inconsistent read. You can select ... holdlock or up the level to repeatable read to get read consistent result sets.

Oracle defaults to read committed -- in our model, that is a consistent read.

So, you can get read consistent results in databases without multi-versioning, you just need to be willing to have queries deadlock with modifications, queries block modifications and modifications block queries (you always have that last one in those databases) Received on Tue Mar 30 2004 - 12:48:44 CST