Re: oracle - mysql comparison

"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...
>
> 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. In both cases parent record is locked for insert/update of child records.

> VC
Received on Wed Jul 21 2004 - 10:23:04 CDT