Re: oracle - mysql comparison

"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*.

VC Received on Tue Jul 20 2004 - 17:59:15 CDT