Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle - mysql comparison

Re: oracle - mysql comparison

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 20 Jul 2004 08:37:35 -0700
Message-ID: <336da121.0407200737.367df069@posting.google.com>


"VC" <boston103_at_hotmail.com> wrote in message news:<LDUJc.105848$Oq2.2189_at_attbi_s52>...
> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0407160845.6603c310_at_posting.google.com...
> > "VC" <boston103_at_hotmail.com> wrote in message
> news:<l9DJc.98700$XM6.25133_at_attbi_s53>...
> > > "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> > > news:336da121.0407150733.5db6de1_at_posting.google.com...
> > >
> > > >As for correct concurrency
> > > > model,
> > >
> > > A correct concurrency control/model ensures that all the permitted
> > > transactions are serializable. In this sense, databases like DB2 or
> MSSQL
> > > implement a correct concurrency model albeit at the expense of lower
> > > concurrency in some circumstances. Funnily enough, none of the Oracle
> > > isolation levels can make the same promise, i.e. ensure serializable
> > > transaction histories, in any of its isolation levels. Usually, it's
> quite
> > > easy to obtain correct results by augmenting an isolation level with
> > > something like 'select for update', though..
> > >
> >
> > Hm, what about SERIALIZABLE isolation level in Oracle? I don't see that
> > it's any different from similar isolation level in MSSQL.
> >
>
> Oracle does not have SERIALIZABLE despite their assurances to the contrary.
> It's trivial to imagine a transaction history where Oracle's SERIALIZABLE
> won't produce correct results. For starters, try 'insert into t1 select
> max(ID)+1 from t1'. The issue with Oracle's serializable being not quite
> what it's labeled was discussed extensively in this newsgroup a couple of
> years ago. Unfortunately, I do not remeber the subject name -- something
> to do with airline reservations.
>

You mean, this discussion:

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

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 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. If anybody thinks that such table of seats is too big,
they didn't work with real databases.

> > > 2. On the other hand, if the implementation was a mixed OLTP/DSS
> project,
> > > then judging by your story, the database architect was not qualified to
> > > design a system like that. His/her being surprised that a locking
> > > scheduler's reads block writes, and vice versa, is akin to a swimmer's
> being
> > > amazed that water is wet. There are several well-known approaches to the
> > > reporting problem such as replicating the main database to a reporting
> > > database, transaction log shipping, scheduling reports off-hours, etc.
> > >
> >
> > Yeah, right. Now, tell me where to find managers who understand what
> > kind of DB architects they really need. And who prefer architects
> > proposing more expensive, although correct, solutions.
>
> Well, one can hardly blame the hammer if one wants to use it as a
> screwdriver can one ? Oracle has quite a few pecularities of its own that
> can jeopardize a project if those responsible for design/implementation have
> no clue as to what they are doing.
>

The problem is that most of current IT/IS management don't know hammer from
a screwdriver. Not literally, of course, but on a level a little bit higher
that 2 + 2 = 4.

> >And, of course,
> > offline reporting (and all solutions you propose are actually variants of
> > it) is not always what customer wants.
>
> 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. 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.
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).

> 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 - 10:37:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US