Re: oracle - mysql comparison

From: VC <boston103_at_hotmail.com>
Date: Mon, 19 Jul 2004 22:32:53 GMT
Message-ID: <p_XKc.135317$XM6.125873@attbi_s53>

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1090240148.855788_at_yasure...
> VC wrote:
>
> > "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> > news:1090216505.569999_at_yasure...
> >
> >>I think your understanding of Oracle is rather slight based on what I
> >>assume you mean.
> >
> >
> > Whilst the conclusion may very well be true, your argument is unsound.
> > Please substitute a real premis for the vague 'based on what I assume
you
> > mean'.
>
> Ok bluntly ... I don't think you know what you are talking about. A lot
> less vague but I'd have preferred not to say it in those words.

An example of my 'not knowing what I am talking about' would be nice. Always eager to learn from my betters.

>
> >>How in mysql can you obtain a result consistent to point-in-time without
> >>table locking all resources.
> >
> > Please go and re-read my original response to Alex Filonov who claimed
that
> > Oracle consistency is somehow more correct than that of DB2/MSSQL.
We've
> > discussed DB2/MSSQL vs. Oracle consistency issues, not mysql concurrency
> > control.
>
> Same statement is true for both DB2 and MS SQL. You can not create a
> point-in-time consistent read without full table locks on all resources.

I'll kill two birds with one stone here if you don't mind. Firstly, I'll describe a scenario where DB2/MSSQL would *not* need to lock an entire table in order to yield a consistent result in SERIALIZABLE IL. Secondly, I'll claim that Oracle won't be able to produce consistent data in Oracle SERIALIZABLE under the same circumstances.

---

Imagine a simple flight reservation system represented by a single table containing flight numbers and passenger information:

create table PASSENGERS(FLIGHT_NUMBER ..., INFO ...); create index P_Idx on PASSENGERS(FLIGHT_NUMBER);

Let's say that a flight can accomodate maximum 50 people. Our transaction in pseudo-code might look like this:

set transaction isolation level serializable ...
select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=999; -- check the number of sold tickets
if l_cnt < 50 insert into PASSENGERS(999, <info>...); -- if there is a vacancy insert a row
commit;

Under DB2/MSSQL, a transaction will lock only a subset of rows (not the whole table) for a given flight thus ensuring consistent results

P.S. I've reproduced, to the best of my recollection, the 'overbooking' example discussed a couple of years ago,

---

.
>
> >>And in Oracle ... I would like a demonstration of how would go about not
> >>having a point-in-time consistent result without intentionally
> >>corrupting a query result.
> >
> > I am not sure what you are trying to say here. Please elaborate.
> >
> > VC
>
> I'm saying you can't, without intent, create a query result that is not
> read consistent to a point-in-time.

Under Oracle, the above reservation scenario will lead to overbooked flights. Figuring out why and how to fix the problem is left as an exercise for the interested student.

>
> > As to mysql, the answer to your question is truly simple: MYSQL has
(and
> > has had for quite a while) an implementation of Oracle style 'read
> > consistency' with rollback segments and all.
>
> Since what version?

Since Version 3.23 (March 2001). Received on Mon Jul 19 2004 - 17:32:53 CDT