oracle - mysql comparison

[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


"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

[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

D Guntermann wrote:
[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090387704 .737703@yasure. ..
>[color=green]
>>Dan wrote:
>>
>>[color=darkred]
>>>Where was VC wrong?[/color]
>>[color=darkred]
>>>- 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.[/color]

I think you are agreeing with me whether you realize it or not. But
perhaps you were responding to someone else.

Daniel Morgan

[D Guntermann]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090544822 .559004@yasure. ..[color=blue]
> D Guntermann wrote:
>[color=green]
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1090387704 .737703@yasure. ..
> >[color=darkred]
> >>Dan wrote:
> >>
> >>
> >>>Where was VC wrong?
> >>
> >>>- Dan
> >>
> >>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[/color][/color]
manipulation[color=blue][color=green]
> > 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"[/color][/color]
and[color=blue][color=green]
> > a snapshot in time is not necessarily it.[/color]
>
> I think you are agreeing with me whether you realize it or not. But
> perhaps you were responding to someone else.
>[/color]

:-) OK.
[color=blue]
> Daniel Morgan
>[/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]

[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]

[Daniel Morgan]

Re: oracle - mysql comparison

D Guntermann wrote:
[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090387704 .737703@yasure. ..
>[color=green]
>>Dan wrote:
>>
>>[color=darkred]
>>>Where was VC wrong?[/color]
>>[color=darkred]
>>>- 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.[/color]

I think you are agreeing with me whether you realize it or not. But
perhaps you were responding to someone else.

Daniel Morgan

[D Guntermann]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090544822 .559004@yasure. ..[color=blue]
> D Guntermann wrote:
>[color=green]
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1090387704 .737703@yasure. ..
> >[color=darkred]
> >>Dan wrote:
> >>
> >>
> >>>Where was VC wrong?
> >>
> >>>- Dan
> >>
> >>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[/color][/color]
manipulation[color=blue][color=green]
> > 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"[/color][/color]
and[color=blue][color=green]
> > a snapshot in time is not necessarily it.[/color]
>
> I think you are agreeing with me whether you realize it or not. But
> perhaps you were responding to someone else.
>[/color]

:-) OK.
[color=blue]
> Daniel Morgan
>[/color]

[JEDIDIAH]

Re: oracle - mysql comparison

["Followup-To:" header set to comp.databases. oracle.server.]
On 2004-07-21, michael newport <michaelnewport @yahoo.com> wrote:[color=blue]
> such as ?[/color]

Hash partitioning and active-active clustering.

Hot/Warm backups and Point-in-time recovery may also be issues.

However, I don't know enough about Ingres to comment in this.
Although this is clearly an area where MySql and Postgres clearly
are lacking.

--
It is not true that Microsoft doesn't innovate.

They brought us the email virus.

In my Atari days, such a notion would have |||
been considered a complete absurdity. / | \

[JEDIDIAH]

Re: oracle - mysql comparison

["Followup-To:" header set to comp.databases. oracle.server.]
On 2004-07-21, michael newport <michaelnewport @yahoo.com> wrote:[color=blue]
> such as ?[/color]

Hash partitioning and active-active clustering.

Hot/Warm backups and Point-in-time recovery may also be issues.

However, I don't know enough about Ingres to comment in this.
Although this is clearly an area where MySql and Postgres clearly
are lacking.

--
It is not true that Microsoft doesn't innovate.

They brought us the email virus.

In my Atari days, such a notion would have |||
been considered a complete absurdity. / | \