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: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Wed, 21 Jul 2004 22:47:30 +0100
Message-ID: <40fee44b$0$6444$cc9e4d1f@news-text.dial.pipex.com>


"VC" <boston103_at_hotmail.com> wrote in message news:7thLc.147195$Oq2.90613_at_attbi_s52...
>
> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0407201338.62eab435_at_posting.google.com...
> > "VC" <boston103_at_hotmail.com> wrote in message
> news:<p_XKc.135317$XM6.125873_at_attbi_s53>...
> > > 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.
>
> 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.

-- 
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 - 16:47:30 CDT

Original text of this message

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