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: VC <boston103_at_hotmail.com>
Date: Wed, 21 Jul 2004 23:03:25 GMT
Message-ID: <1DCLc.157938$Oq2.134334@attbi_s52>


Hi,

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:40fee44b$0$6444$cc9e4d1f_at_news-text.dial.pipex.com... ...
> > You are attacking a strawman -- the design is not what's being discussed
> > here, concurrency control/consistency is...
>
> Two sides of the same coin surely? Decide how your platform behaves and
code
> for its strengths (and weaknesses). Coding deciding that all database
> systems should be treated as if they had the feature set of ms access
would
> surely be an error.

But of course, I do not have any argument with what you've just said. My first response in this thread was intended to show that the claim that Oracle's *consistency* is somehow superior to the alternative approaches is not true at all. In some cases, even concurrency can be worse that that of a locking database.

Regards.

VC

>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> >
> > Here's another simple problem:
> >
> > ===
> > Let's assume we have two tables, P ( parent) and C (child):
> >
> > create table P(pid int primary key, ...);
> > create table C(..., pid references P, ...);
> >
> > We'd like to insert some child rows but, at the same time, avoid
entering
> > data in vain if someone happens to delete a parent row during our data
> > entry.
> >
> > In a locking scheduler, this sequence would run OK:
> >
> > select count(*) into l_cnt from P where pid=1;
> > if l_cnt > 0 insert into C values(..., 1, ...);
> > commit;
> >
> > A variation of this scenario might be that, for whatever reason, you
> cannot
> > use referential integrity and cannot delete the parent row. You'd
rather
> > mark the parent row inactive and prevent insertions if the parent is
> > inactive. Our transaction would look:
> >
> > select count(*) into l_cnt from P where pid=1 and status='ACTIVE';
> > if l_cnt > 0 insert into C values(..., 1, ...);
> > commit;
> >
> > ===
> >
> > Needless to say that in the second case Oracle won't ensure the correct
> > outcome in any of its isolation levels if a concurrent transaction
> happens
> > to change the parent row status to 'inactive'. In the first case, a
> > non-serializable history will be avoided thanks to RI, however, the
> > entered data will be lost. The above example can be applied to any
similar
> > master-detail model.
> >
> > An Oracle solution to the problem would be to use the "for update"
clause
> > instead of just select. This will result in *worse* concurrency under
> > Oracle than under a locking scheduler since all inserts will in fact be
> > *serial*.
> >
> > VC
> >
> >
>
>
Received on Wed Jul 21 2004 - 18:03:25 CDT

Original text of this message

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