Re: oracle - mysql comparison
"Alex Filonov" <afilonov@yahoo .com> wrote in message
news:336da121.0 407201338.62eab 435@posting.goo gle.com...[color=blue]
> "VC" <boston103@hotm ail.com> wrote in message[/color]
news:<p_XKc.135 317$XM6.125873@ attbi_s53>...[color=blue][color=green]
> > Under DB2/MSSQL, a transaction will lock only a subset of rows (not the
> > whole table) for a given flight thus ensuring consistent results
> >[/color]
>
> I've said in another post that it's a crappy example. A little bit more on
> that.[/color]
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
"Alex Filonov" <afilonov@yahoo .com> wrote in message
news:336da121.0 407201338.62eab 435@posting.goo gle.com...[color=blue]
> "VC" <boston103@hotm ail.com> wrote in message[/color]
news:<p_XKc.135 317$XM6.125873@ attbi_s53>...[color=blue][color=green]
> > Under DB2/MSSQL, a transaction will lock only a subset of rows (not the
> > whole table) for a given flight thus ensuring consistent results
> >[/color]
>
> I've said in another post that it's a crappy example. A little bit more on
> that.[/color]
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
Comment