oracle - mysql comparison

[Daniel Morgan]

Re: oracle - mysql comparison

Dan wrote:[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090378179 .508307@yasure. ..
>[color=green]
>>VC wrote:
>>
>>[color=darkred]
>>>As I've already mentioned several times, no one disputes the fact that[/color][/color]
>
> in
>[color=green][color=darkred]
>>>certain cases Oracle provides higher concurrency due to MVCC.[/color]
>>
>>Agreed.
>>
>>[color=darkred]
>>>I also said that there are several solutions to the reporting problem in
>>>locking databases, such as a replicated or stand-by database.[/color]
>>
>>Many believe this but it is patently false. What do you do about
>>transaction s that take place while you are replicating the database?
>>
>>You either lock the table while replicating or the replication is also
>>not consistent to a point-in-time. You can not have it both ways.
>>
>> There is
>>[color=darkred]
>>>another solution, namely triple mirroring of the OLTP database. SAN[/color][/color]
>
> vendor
>[color=green][color=darkred]
>>>harware can "split off" the third mirrored drive set creating almost
>>>instantaneou sly a clone of the original database (e.g. EMC BCV) at a[/color][/color]
>
> given
>[color=green][color=darkred]
>>>point in time. It's interesting to notice, that the same technique is
>>>widely used for Oracle databases as well in order to off-load the main
>>>instance. The clone is used both for reporting and backups.[/color]
>>
>>Almost instantly means ALMOST consistent to a point-in-time. But now you
>>are talking about data consistency by hardware intervention which is
>>just as valid if we were talking about 3x5 cards and a photocopier.
>>
>>[color=darkred]
>>>>Serialize to your hearts content ... you aren't going to do it without
>>>>a full table lock ...
>>>[/color][/color]
>[color=green][color=darkred]
>>>As I've demonstrated, only a subset of rows involved in the transaction[/color][/color]
>
> has
>[color=green][color=darkred]
>>>to be locked which naturally can be the whole table.[/color]
>>
>>Patently false. You can not lock rows that have not yet been inserted
>>while the transaction is taking place. And you have no means of keeping
>>them out of your result set except a full table lock.
>>[/color]
>
>
> Absolutely not true.
>
> There is a critical difference between the notion of 'serialized' and the
> term 'serializable'. Perhaps you could look it up? If not, Phil Bernstein,
> the guy that literally wrote the book on the subject is a real professor at
> the University of Washington. You might be able to catch a class...
>
> Once you understand the difference in terminology, then perhaps you'll
> understand why you don't need a full table lock to ensure a serializable
> schedule even when a concurrent transaction inserts a row (again! in
> contrast to serialized).
>
>[color=green]
>>Daniel Morgan
>>[/color]
>
> - Dan[/color]

No doubt you thought you understood what I intended. I did not once
mention serialized or serializable did I?

Daniel Morgan

[Daniel Morgan]

Re: oracle - mysql comparison

Dan wrote:
[color=blue]
> Where was VC wrong?[/color]
[color=blue]
> - Dan[/color]

When you respond to what I wrote rather than what you wrote I wrote I
will gladly respond.

From where I am reading ... you came into a conversation in the middle
and are not tracking on the point I have been trying to make which
relates to point-in-time accuracy. I have NOT once made reference to
any type of cereal: Not Corn Flakes, Not Cheerios, Not Oat Meal.
Hope you now have a sense of how I feel reading what you wrote. Hope
you are thinking "I wrote serial not cereal."

Daniel Morgan

[Alex Filonov]

Re: oracle - mysql comparison

"VC" <boston103@hotm ail.com> wrote in message news:<7thLc.147 195$Oq2.90613@a ttbi_s52>...[color=blue]
> "Alex Filonov" <afilonov@yahoo .com> wrote in message
> news:336da121.0 407201338.62eab 435@posting.goo gle.com...[color=green]
> > "VC" <boston103@hotm ail.com> wrote in message[/color]
> news:<p_XKc.135 317$XM6.125873@ attbi_s53>...[color=green][color=darkred]
> > > 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*.
>[/color]

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.
[color=blue]
> VC[/color]

[michael newport]

Re: oracle - mysql comparison

such as ?

[Jim Kennedy]

Re: oracle - mysql comparison


"VC" <boston103@hotm ail.com> wrote in message
news:SgrLc.1542 60$XM6.64529@at tbi_s53...[color=blue]
>
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090378179 .508307@yasure. ..[color=green]
> > VC wrote:[color=darkred]
> > > As I've demonstrated, only a subset of rows involved in the[/color][/color][/color]
transaction[color=blue]
> has[color=green][color=darkred]
> > > to be locked which naturally can be the whole table.[/color]
> >
> > Patently false. You can not lock rows that have not yet been inserted
> > while the transaction is taking place.[/color]
>
> As a matter of fact, you can. In the reservation example:
>
> create table PASSENGERS(FLIG HT_NUMBER ..., INFO ...);
> create index P_Idx on PASSENGERS(FLIG HT_NUMBER);
> set transaction isolation level serializable
>
> ... the statement below will lock not only the existing rows satisfying[/color]
the[color=blue]
> FLIGHT_NUMBER=9 99 predicate, but also the relevant index (P_Idx) range[/color]
thus[color=blue]
> preventiing potential *inserts*:
>
> select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=9 99; --
>
> This mechanism is called key-range locking. It allows to avoid full table
> locks and is implemented in all the major locking databases. MYSQL, by[/color]
the[color=blue]
> way, has it too.
>[color=green]
> >And you have no means of keeping
> > them out of your result set except a full table lock.[/color]
>
> See above.
>
> VC
>
>[/color]
And if it is a page locking database then it will lock whole pages of
indexes (as the index is traversed) witch will effectively lock the entire
table (or about 95% of it)
Jim

[VC]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090378179 .508307@yasure. ..[color=blue]
> VC wrote:[color=green]
> > As I've demonstrated, only a subset of rows involved in the transaction[/color][/color]
has[color=blue][color=green]
> > to be locked which naturally can be the whole table.[/color]
>
> Patently false. You can not lock rows that have not yet been inserted
> while the transaction is taking place.[/color]

As a matter of fact, you can. In the reservation example:

create table PASSENGERS(FLIG HT_NUMBER ..., INFO ...);
create index P_Idx on PASSENGERS(FLIG HT_NUMBER);
set transaction isolation level serializable

.... the statement below will lock not only the existing rows satisfying the
FLIGHT_NUMBER=9 99 predicate, but also the relevant index (P_Idx) range thus
preventiing potential *inserts*:

select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=9 99; --

This mechanism is called key-range locking. It allows to avoid full table
locks and is implemented in all the major locking databases. MYSQL, by the
way, has it too.
[color=blue]
>And you have no means of keeping
> them out of your result set except a full table lock.[/color]

See above.

VC

[VC]

Re: oracle - mysql comparison


"Alex Filonov" <afilonov@yahoo .com> wrote in message
news:336da121.0 407210723.6a1dd 87f@posting.goo gle.com...[color=blue]
> "VC" <boston103@hotm ail.com> wrote in message[/color]
news:<7thLc.147 195$Oq2.90613@a ttbi_s52>...
....[color=blue][color=green]
> > 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[/color][/color]
entering[color=blue][color=green]
> > 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[/color][/color]
cannot[color=blue][color=green]
> > use referential integrity and cannot delete the parent row. You'd[/color][/color]
rather[color=blue][color=green]
> > 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[/color][/color]
happens[color=blue][color=green]
> > 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[/color][/color]
similar[color=blue][color=green]
> > master-detail model.
> >
> > An Oracle solution to the problem would be to use the "for update"[/color][/color]
clause[color=blue][color=green]
> > 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*.
> >[/color]
>
> Don't see any difference between "locking scheduler" and for update here.[/color]

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

[color=blue]
> In both cases parent record is locked for insert/update of child records.
>[/color]

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

[Volker Hetzer]

Re: oracle - mysql comparison


"Dan" <guntermannxxx@ verizon.com> schrieb im Newsbeitrag news:OylLc.3304 2$lz2.13858@nwr ddc03.gnilink.n et...[color=blue]
> There is a critical difference between the notion of 'serialized' and the
> term 'serializable'. Perhaps you could look it up? If not, Phil Bernstein,
> the guy that literally wrote the book on the subject is a real professor at
> the University of Washington. You might be able to catch a class...[/color]
Looks like this thread was worth catching up...
Which book do you mean,
http://www.amazon.com/exec/obidos/tg...books&n=507846 ?

And what about

http://www.amazon.com/exec/obidos/tg/detail/-/1558601902/qid=1090405674/sr=1-2/ref=sr_1_2/102-7103545-0914529?v=glance&s=books

and

http://www.amazon.com/exec/obidos/tg/detail/-/1558605088/qid=1090405995/sr=8-1/ref=sr_8_xs_ap_i1_xgl14/102-7103545-0914529?v=glance&s=books&n=507846

.. Any opinion on those? I have no problem with the price but I wouldn't like to waste money either. I work as DBA and developer.

Lots of greetings and thanks!
Volker

[Niall Litchfield]

Re: oracle - mysql comparison

"VC" <boston103@hotm ail.com> wrote in message
news:7thLc.1471 95$Oq2.90613@at tbi_s52...[color=blue]
>
> "Alex Filonov" <afilonov@yahoo .com> wrote in message
> news:336da121.0 407201338.62eab 435@posting.goo gle.com...[color=green]
> > "VC" <boston103@hotm ail.com> wrote in message[/color]
> news:<p_XKc.135 317$XM6.125873@ attbi_s53>...[color=green][color=darkred]
> > > Under DB2/MSSQL, a transaction will lock only a subset of rows (not[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > 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[/color][/color]
on[color=blue][color=green]
> > that.[/color]
>
> You are attacking a strawman -- the design is not what's being discussed
> here, concurrency control/consistency is...[/color]

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

[color=blue]
>
> 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[/color]
cannot[color=blue]
> 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[/color]
happens[color=blue]
> 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
>
>[/color]

[VC]

Re: oracle - mysql comparison


"Jim Kennedy" <kennedy-downwithspammer sfamily@attbi.n et> wrote in message
news:3szLc.1579 06$XM6.2871@att bi_s53...[color=blue]
>
> "VC" <boston103@hotm ail.com> wrote in message
> news:SgrLc.1542 60$XM6.64529@at tbi_s53...[color=green]
> >
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1090378179 .508307@yasure. ..[color=darkred]
> > > VC wrote:
> > > > As I've demonstrated, only a subset of rows involved in the[/color][/color]
> transaction[color=green]
> > has[color=darkred]
> > > > to be locked which naturally can be the whole table.
> > >
> > > Patently false. You can not lock rows that have not yet been inserted
> > > while the transaction is taking place.[/color]
> >
> > As a matter of fact, you can. In the reservation example:
> >
> > create table PASSENGERS(FLIG HT_NUMBER ..., INFO ...);
> > create index P_Idx on PASSENGERS(FLIG HT_NUMBER);
> > set transaction isolation level serializable
> >
> > ... the statement below will lock not only the existing rows satisfying[/color]
> the[color=green]
> > FLIGHT_NUMBER=9 99 predicate, but also the relevant index (P_Idx) range[/color]
> thus[color=green]
> > preventiing potential *inserts*:
> >
> > select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=9 99; --
> >
> > This mechanism is called key-range locking. It allows to avoid full[/color][/color]
table[color=blue][color=green]
> > locks and is implemented in all the major locking databases. MYSQL, by[/color]
> the[color=green]
> > way, has it too.
> >[color=darkred]
> > >And you have no means of keeping
> > > them out of your result set except a full table lock.[/color]
> >
> > See above.
> >
> > VC
> >
> >[/color]
> And if it is a page locking database then it will lock whole pages of
> indexes (as the index is traversed) witch will effectively lock the entire
> table (or about 95% of it)
> Jim[/color]

That was so ten years ago, but is no true longer today:

=======
MSSQL:

1> create table flight(number int, seat int)
2> go
1> insert into flight(1,1)
2> go
1> insert into flight values(1,1)
2> go
1> insert into flight values(1,2)
2> go
1> insert into flight values(1,3)
2> go
1> insert into flight values(2,1)
1> set implicit_transa ctions on
2> go
1> create index f_idx on flight(number)
2> go
1>

We have 3 rows for Flight 1 and 1 row for Flight 2.

Then:

Session 1:
----------
1> set transaction isolation level serializable
2> go
1> set transaction isolation level serializable
2> go
1> select count(*) from flight where number=1
2> go

-----------
3
1>


Session 2:
----------
1> set transaction isolation level serializable
2> go
1> set transaction isolation level serializable
2> go

We can read all the rows:

1> select * from flight
2> go
number seat
----------- -----------
1 1
1 2
1 3
2 1


We can add seats to Flight 2::

1> insert into flight values(2,2)
2> go

.... we can see the old and new rows::

(1 rows affected)
1> select * from flight
2> go
number seat
----------- -----------
1 1
1 2
1 3
2 1
2 2

(5 rows affected)

... but we cannot add any new seats to Flight 1 thanks to key-range locking
caused by 'select count(*) from flight where number=1'.

1> insert into flight values(1,4)
2> go
** blocked **

Apparently, no page/table level locking happens here ...

VC[color=blue]
>
>[/color]

[VC]

Re: oracle - mysql comparison

Hi,

"Niall Litchfield" <niall.litchfie ld@dial.pipex.c om> wrote in message
news:40fee44b$0 $6444$cc9e4d1f@ news-text.dial.pipex .com...
....[color=blue][color=green]
> > You are attacking a strawman -- the design is not what's being discussed
> > here, concurrency control/consistency is...[/color]
>
> Two sides of the same coin surely? Decide how your platform behaves and[/color]
code[color=blue]
> for its strengths (and weaknesses). Coding deciding that all database
> systems should be treated as if they had the feature set of ms access[/color]
would[color=blue]
> surely be an error.[/color]

But of course, I do not have any argument with what you've just said. My
first response in this thread was intended to show that the claim that
Oracle's *consistency* is somehow superior to the alternative approaches is
not true at all. In some cases, even concurrency can be worse that that of
a locking database.


Regards.

VC
[color=blue]
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>[color=green]
> >
> > 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[/color][/color]
entering[color=blue][color=green]
> > 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[/color]
> cannot[color=green]
> > use referential integrity and cannot delete the parent row. You'd[/color][/color]
rather[color=blue][color=green]
> > 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[/color]
> happens[color=green]
> > 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[/color][/color]
similar[color=blue][color=green]
> > master-detail model.
> >
> > An Oracle solution to the problem would be to use the "for update"[/color][/color]
clause[color=blue][color=green]
> > 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
> >
> >[/color]
>
>[/color]

[Daniel Morgan]

Re: oracle - mysql comparison

VC wrote:
[color=blue]
> 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[/color]

Since when does your ignorance translate into Oracle's weakness?

You still haven't addressed how you could handle the bank transaction
question I posted many days ago.

Daniel Morgan

[VC]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090474999 .284806@yasure. ..[color=blue]
> VC wrote:
>[color=green]
> > That's true. The only difference is that Oracle relies on exclusive[/color][/color]
locks[color=blue][color=green]
> > thus preventing parallel 'select for updates' and the locking database[/color][/color]
uses[color=blue][color=green]
> > shared locks that do not inhibit concurrent selects of the parent row.
> >
> > VC[/color]
>
> Since when does your ignorance translate into Oracle's weakness?[/color]

I'll skip the above utterance as a non sequitur.
[color=blue]
>
> You still haven't addressed how you could handle the bank transaction
> question I posted many days ago.[/color]

I've already addressed the reporting issue more than once.
[color=blue]
>
> Daniel Morgan
>[/color]

[Alex Filonov]

Re: oracle - mysql comparison

"VC" <boston103@hotm ail.com> wrote in message news:<7thLc.147 195$Oq2.90613@a ttbi_s52>...[color=blue]
> "Alex Filonov" <afilonov@yahoo .com> wrote in message
> news:336da121.0 407201338.62eab 435@posting.goo gle.com...[color=green]
> > "VC" <boston103@hotm ail.com> wrote in message[/color]
> news:<p_XKc.135 317$XM6.125873@ attbi_s53>...[color=green][color=darkred]
> > > 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*.
>[/color]

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.
[color=blue]
> VC[/color]

[D Guntermann]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090387704 .737703@yasure. ..[color=blue]
> Dan wrote:
>[color=green]
> > Where was VC wrong?[/color]
>[color=green]
> > - Dan[/color]
>
> When you respond to what I wrote rather than what you wrote I wrote I
> will gladly respond.
>
> From where I am reading ... you came into a conversation in the middle
> and are not tracking on the point I have been trying to make which
> relates to point-in-time accuracy.[/color]

I think you are missing the point. There is a formal definition and
criteria for "point-in-time" accuracy in the face of concurrent manipulation
of data. Following from this there is a definition and a criteria for
"point-in-time" accuracy that allows for interleaving of transactions,
called serializability . Saying "I use multi-versioned read consistency" and
a snapshot in time is not necessarily it.

If one gets the definition right, then it becomes much easier for everyone
to talk at the same level understanding in terms of "point-in-time"
consistency and perhaps even reach consensus on understanding the
trade-offs. Your previous posts have given me the impression that you
confuse the terms 'serialized' and 'serializable'. If this is indeed the
case, then the argument will have a tendency to become circular in related
conversations such as concurrency control mechanisms and implementations
(locking, timestamp algorthings, optiimistic vs. pessimistic, 2PL, etc). ,
which it has to a degree, because you are using a premise as a basis of
definition that is fundamentally flawed, or at the very least, different.
The Oracle Concepts document does have some misleading wording and has a
very narrow scope, and if that is what you use as your Bible, then it is not
surprising.
[color=blue]
> any type of cereal: Not Corn Flakes, Not Cheerios, Not Oat Meal.
> Hope you now have a sense of how I feel reading what you wrote. Hope
> you are thinking "I wrote serial not cereal."[/color]

Whatever this means...great.[color=blue]
>
> Daniel Morgan
>[/color]