Re: oracle - mysql comparison

"VC" <boston103_at_hotmail.com> wrote in message news:<p_XKc.135317$XM6.125873_at_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
>

I've said in another post that it's a crappy example. A little bit more on that.

This model doesn't reserve seats. It reserves pieces of flight. You said that the flight has 50 pieces and written it down in application code. Proper design would be to have a table flight_seats, which has columns flight_number, date, passenger_id. Then you can book not a piece of a flight, but a seat. And there is no need for serialization.

You can use real seat numbers here, or you can map abstract seats to real seats later, at check-in. This model also makes plane replacement easy. Just add or remove seats from a flight.

Putting logic and constraints into code instead of a database is a sign of a bad design.

> 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 Tue Jul 20 2004 - 16:38:45 CDT