Re: oracle - mysql comparison

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0407200737.367df069_at_posting.google.com...
> "VC" <boston103_at_hotmail.com> wrote in message
news:<LDUJc.105848$Oq2.2189_at_attbi_s52>...
> >
>
> You mean, this discussion:
>
>

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=KAnf3.108267%24_m 4.1357169%40news2.giganews.com&rnum=17&prev=/groups%3Fq%3Dg:thl2804310228d%2 6dq%3D%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DKAnf3.108267%2524_m4.1357169% 2540news2.giganews.com%26rnum%3D17
>

Yes, that's the one.

> Well, serializability in ANSI92 definition is probably impossible for
> a
> big database with big number of concurrent sessions (and airline
> reservation
> system is a good example).

As I showed in another posting, any locking database handles the reservation example OK, without locking the entire table.

>As for example of overbooking, it's total
> crap.
> Good design for airline reservation system would just have additional
> table for each seat on each flight, and reserve seats (as in real
> life),
> not pieces of an airplane. No need for serializability at all here. No
> problem
> with overbooking.

Your appeal to design aspect is disingenuous but irrelevant. The point is that a certain class of transaction histories similar to the reservation example cannot be handled *correctly* in Oracle's 'SERIALIZABLE', contrary to what you claimed elsewhere (" As for correct concurrency model, I remember one project").

Here's another textbook example for you: ==
There are two linked accounts (id=1 and id=2) in a bank. A transaction might look as follows:

• withdraw from 1 select amount into x from accounts where id=1 select amount into y from accounts where id=2 if x+y >= withdrawal then update accounts set amount=amount-withdrawal where id=1 commit

  ---

Any commercial locking scheduler will handle the scenario correctly. Oracle won't.

> > What's wrong with those solutions for long running reports, quick
queries
> > not being a problem ? Besides, the situation is not very much
different
> > from running a long report under Oracle where the results won't be
actual
> > due to the very nature of the beast -- 'read consistency'.
> >
>
> What do you mean, not actual? They are actual for the point of time.

Sorry, an unfortunate choice of words. I used 'actual' in the sense of 'current'. A 15 minute long Oracle report is 15 minutes out of date whilst a locking scheduler report result is current for the moment the query ended.

> As for real situation, reports didn't run very long time, just
> something
> between 5 and 15 minutes. You wouldn't notice those if running on
> Oracle.

Those are *long* reports. Besides, for the last time I am repeating myself, no one is disputing Oracle concurrecy being better in this situation. .

> And quick queries on read-locking databases is a big problem, if they
> need to lock significant number of rows (especially when locks are
> escalated).

Somehow TPC-C results (
http://www-306.ibm.com/software/data/db2/benchmarks/021704.html ) do not bear you out.

>
> > There is no argument that a multiversioning scheduler provides higher
> > concurrency in many cases, after all that's its raison d'etre, but one
> > should not forget about trade-offs/pitfalls and think that alternative,
> > locking, approaches won't work.
> >
> > >
> > > > VC
Received on Tue Jul 20 2004 - 13:12:01 CDT