Re: oracle - mysql comparison

"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0407210723.6a1dd87f_at_posting.google.com...
> "VC" <boston103_at_hotmail.com> wrote in message
news:<7thLc.147195$Oq2.90613_at_attbi_s52>... ...
> > 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*.
> >
>
> Don't see any difference between "locking scheduler" and for update here.

Well, one would imagine the difference is quite obvious -- the locking database will run the transaction happily *in parallel* whilst Oracle will do the same *serially*.

> In both cases parent record is locked for insert/update of child records.
>

That's true. The only difference is that Oracle relies on exclusive locks thus preventing parallel 'select for updates' and the locking database uses shared locks that do not inhibit concurrent selects of the parent row.

VC Received on Wed Jul 21 2004 - 16:32:03 CDT