oracle - mysql comparison

[Jim Kennedy]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090027361 .387590@yasure. ..[color=blue]
>
>
> michael newport wrote:
>[color=green]
> > Jon,
> >
> > check out www.ca.com for Ingres, its the same strength as Oracle but
> > will shortly be OpenSource.
> >
> > Regards
> > Michael Newport[/color]
>
> And by 'strength' you mean?
>
> Daniel Morgan
>[/color]
I think he means it is good at locking. It has a strong locking mechanism.
Last time I used Ingress (quite a few years ago) an insert into a table
would lock the whole table! Why? It had page level locks and inserting a
record into a table with a primary key locks the entire index which means
only 1 person at a time could insert records. You had to commit to release
the locks. So don't have long running transactions. Leads to bad
programming practices; developers putting the system into autocommit mode.
(yeach)
Jim

[Steve]

Re: oracle - mysql comparison

John Hurley wrote:[color=blue]
> Try a google search "mysql comparison oracle" or similar.
>
> Obviously oracle has financial resources to devote to enhancements
> that mysql doesn't but it's amazing to see how far along mysql has
> evolved over the last several versions.[/color]

Still no views, though.

Steve
( Just been caught by that one! )

[Steve]

Re: oracle - mysql comparison

John Hurley wrote:[color=blue]
> Try a google search "mysql comparison oracle" or similar.
>
> Obviously oracle has financial resources to devote to enhancements
> that mysql doesn't but it's amazing to see how far along mysql has
> evolved over the last several versions.[/color]

Still no views, though.

Steve
( Just been caught by that one! )

[VC]

Re: oracle - mysql comparison

Please see below:

"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090027247 .885352@yasure. ..[color=blue]
> VC wrote:
>[color=green]
> > Hello Daniel,
> >
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1089945278 .134355@yasure. ..
> >[color=darkred]
> >>VC wrote:
> >>
> >>
> >>>A correct concurrency control/model ensures that all the permitted
> >>>transactio ns are serializable. In this sense, databases like DB2 or[/color]
> >
> > MSSQL
> >[color=darkred]
> >>>implement a correct concurrency model albeit at the expense of lower
> >>>concurrenc y in some circumstances. Funnily enough, none of the[/color][/color][/color]
Oracle[color=blue][color=green][color=darkred]
> >>>isolation levels can make the same promise, i.e. ensure serializable
> >>>transactio n histories, in any of its isolation levels. Usually, it's[/color]
> >
> > quite
> >[color=darkred]
> >>>easy to obtain correct results by augmenting an isolation level with
> >>>something like 'select for update', though..
> >>
> >>And if this is true why, exactly, would anyone care at the expense of
> >>being able to extract an accurate answer from a database without
> >>performing table locks on all resources?
> >>[/color]
> >
> >
> > I am not sure what specific part of my message the word *this* ('if this[/color][/color]
is[color=blue][color=green]
> > true') refers to, but assuming it's the last sentence then you surely[/color][/color]
know[color=blue][color=green]
> > that 'select for update' takes write (TX) lock on all the rows involed[/color][/color]
A[color=blue][color=green]
> > locking scheduler would take *read* row level locks in similar[/color][/color]
circumstances[color=blue][color=green]
> > automatically. If my interpretaion is incorrect, please elaborate.
> >
> > VC[/color]
>
> My point is that in all of the other databases to which you have
> referred it is impossible to get a result set consistent to a
> point-in-time without locking the resources (not at the row level but at
> the table level).
>
> Something I would think far more important than anything you brought up.
>[/color]

Assuming you are talking here about the locking database's SERIALIZABLE ,
no, it's not always necessary to lock the whole table(s) in order to obtain
a consistent result. Sometimes, yes, the whole table has to be locked,
but not always. I can give an example or two if you care.

On the other hand, Oracle's concurrency control makes solving some problems
harder than in a locking environment precisely because of Oracle locking
mecanism simplicity or some may say deficiency, e.g. absence of row-level
read locks, key-range locking, etc.

As I mentioned in my reply to another poster, in many situations Oracle
undoubtedly provides higher concurrency than locking database engines, after
all it was designed to do so although certain compromises have been made.

Regards.

VC

[VC]

Re: oracle - mysql comparison

Please see below:

"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090027247 .885352@yasure. ..[color=blue]
> VC wrote:
>[color=green]
> > Hello Daniel,
> >
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1089945278 .134355@yasure. ..
> >[color=darkred]
> >>VC wrote:
> >>
> >>
> >>>A correct concurrency control/model ensures that all the permitted
> >>>transactio ns are serializable. In this sense, databases like DB2 or[/color]
> >
> > MSSQL
> >[color=darkred]
> >>>implement a correct concurrency model albeit at the expense of lower
> >>>concurrenc y in some circumstances. Funnily enough, none of the[/color][/color][/color]
Oracle[color=blue][color=green][color=darkred]
> >>>isolation levels can make the same promise, i.e. ensure serializable
> >>>transactio n histories, in any of its isolation levels. Usually, it's[/color]
> >
> > quite
> >[color=darkred]
> >>>easy to obtain correct results by augmenting an isolation level with
> >>>something like 'select for update', though..
> >>
> >>And if this is true why, exactly, would anyone care at the expense of
> >>being able to extract an accurate answer from a database without
> >>performing table locks on all resources?
> >>[/color]
> >
> >
> > I am not sure what specific part of my message the word *this* ('if this[/color][/color]
is[color=blue][color=green]
> > true') refers to, but assuming it's the last sentence then you surely[/color][/color]
know[color=blue][color=green]
> > that 'select for update' takes write (TX) lock on all the rows involed[/color][/color]
A[color=blue][color=green]
> > locking scheduler would take *read* row level locks in similar[/color][/color]
circumstances[color=blue][color=green]
> > automatically. If my interpretaion is incorrect, please elaborate.
> >
> > VC[/color]
>
> My point is that in all of the other databases to which you have
> referred it is impossible to get a result set consistent to a
> point-in-time without locking the resources (not at the row level but at
> the table level).
>
> Something I would think far more important than anything you brought up.
>[/color]

Assuming you are talking here about the locking database's SERIALIZABLE ,
no, it's not always necessary to lock the whole table(s) in order to obtain
a consistent result. Sometimes, yes, the whole table has to be locked,
but not always. I can give an example or two if you care.

On the other hand, Oracle's concurrency control makes solving some problems
harder than in a locking environment precisely because of Oracle locking
mecanism simplicity or some may say deficiency, e.g. absence of row-level
read locks, key-range locking, etc.

As I mentioned in my reply to another poster, in many situations Oracle
undoubtedly provides higher concurrency than locking database engines, after
all it was designed to do so although certain compromises have been made.

Regards.

VC

[Daniel Morgan]

Re: oracle - mysql comparison

VC wrote:
[color=blue]
> Please see below:
>
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090027247 .885352@yasure. ..
>[color=green]
>>VC wrote:
>>
>>[color=darkred]
>>>Hello Daniel,
>>>
>>>"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
>>>news:1089945 278.134355@yasu re...
>>>
>>>
>>>>VC wrote:
>>>>
>>>>
>>>>
>>>>>A correct concurrency control/model ensures that all the permitted
>>>>>transactio ns are serializable. In this sense, databases like DB2 or
>>>
>>>MSSQL
>>>
>>>
>>>>>implemen t a correct concurrency model albeit at the expense of lower
>>>>>concurrenc y in some circumstances. Funnily enough, none of the[/color][/color]
>
> Oracle
>[color=green][color=darkred]
>>>>>isolatio n levels can make the same promise, i.e. ensure serializable
>>>>>transactio n histories, in any of its isolation levels. Usually, it's
>>>
>>>quite
>>>
>>>
>>>>>easy to obtain correct results by augmenting an isolation level with
>>>>>somethin g like 'select for update', though..
>>>>
>>>>And if this is true why, exactly, would anyone care at the expense of
>>>>being able to extract an accurate answer from a database without
>>>>performin g table locks on all resources?
>>>>
>>>
>>>
>>>I am not sure what specific part of my message the word *this* ('if this[/color][/color]
>
> is
>[color=green][color=darkred]
>>>true') refers to, but assuming it's the last sentence then you surely[/color][/color]
>
> know
>[color=green][color=darkred]
>>>that 'select for update' takes write (TX) lock on all the rows involed[/color][/color]
>
> A
>[color=green][color=darkred]
>>>locking scheduler would take *read* row level locks in similar[/color][/color]
>
> circumstances
>[color=green][color=darkred]
>>>automaticall y. If my interpretaion is incorrect, please elaborate.
>>>
>>>VC[/color]
>>
>>My point is that in all of the other databases to which you have
>>referred it is impossible to get a result set consistent to a
>>point-in-time without locking the resources (not at the row level but at
>>the table level).
>>
>>Something I would think far more important than anything you brought up.
>>[/color]
>
>
> Assuming you are talking here about the locking database's SERIALIZABLE ,
> no, it's not always necessary to lock the whole table(s) in order to obtain
> a consistent result. Sometimes, yes, the whole table has to be locked,
> but not always. I can give an example or two if you care.
>
> On the other hand, Oracle's concurrency control makes solving some problems
> harder than in a locking environment precisely because of Oracle locking
> mecanism simplicity or some may say deficiency, e.g. absence of row-level
> read locks, key-range locking, etc.
>
> As I mentioned in my reply to another poster, in many situations Oracle
> undoubtedly provides higher concurrency than locking database engines, after
> all it was designed to do so although certain compromises have been made.
>
> Regards.
>
> VC[/color]

I think your understanding of Oracle is rather slight based on what I
assume you mean.

How in mysql can you obtain a result consistent to point-in-time without
table locking all resources.

And in Oracle ... I would like a demonstration of how would go about not
having a point-in-time consistent result without intentionally
corrupting a query result.

Daniel Morgan

[Daniel Morgan]

Re: oracle - mysql comparison

VC wrote:
[color=blue]
> Please see below:
>
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090027247 .885352@yasure. ..
>[color=green]
>>VC wrote:
>>
>>[color=darkred]
>>>Hello Daniel,
>>>
>>>"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
>>>news:1089945 278.134355@yasu re...
>>>
>>>
>>>>VC wrote:
>>>>
>>>>
>>>>
>>>>>A correct concurrency control/model ensures that all the permitted
>>>>>transactio ns are serializable. In this sense, databases like DB2 or
>>>
>>>MSSQL
>>>
>>>
>>>>>implemen t a correct concurrency model albeit at the expense of lower
>>>>>concurrenc y in some circumstances. Funnily enough, none of the[/color][/color]
>
> Oracle
>[color=green][color=darkred]
>>>>>isolatio n levels can make the same promise, i.e. ensure serializable
>>>>>transactio n histories, in any of its isolation levels. Usually, it's
>>>
>>>quite
>>>
>>>
>>>>>easy to obtain correct results by augmenting an isolation level with
>>>>>somethin g like 'select for update', though..
>>>>
>>>>And if this is true why, exactly, would anyone care at the expense of
>>>>being able to extract an accurate answer from a database without
>>>>performin g table locks on all resources?
>>>>
>>>
>>>
>>>I am not sure what specific part of my message the word *this* ('if this[/color][/color]
>
> is
>[color=green][color=darkred]
>>>true') refers to, but assuming it's the last sentence then you surely[/color][/color]
>
> know
>[color=green][color=darkred]
>>>that 'select for update' takes write (TX) lock on all the rows involed[/color][/color]
>
> A
>[color=green][color=darkred]
>>>locking scheduler would take *read* row level locks in similar[/color][/color]
>
> circumstances
>[color=green][color=darkred]
>>>automaticall y. If my interpretaion is incorrect, please elaborate.
>>>
>>>VC[/color]
>>
>>My point is that in all of the other databases to which you have
>>referred it is impossible to get a result set consistent to a
>>point-in-time without locking the resources (not at the row level but at
>>the table level).
>>
>>Something I would think far more important than anything you brought up.
>>[/color]
>
>
> Assuming you are talking here about the locking database's SERIALIZABLE ,
> no, it's not always necessary to lock the whole table(s) in order to obtain
> a consistent result. Sometimes, yes, the whole table has to be locked,
> but not always. I can give an example or two if you care.
>
> On the other hand, Oracle's concurrency control makes solving some problems
> harder than in a locking environment precisely because of Oracle locking
> mecanism simplicity or some may say deficiency, e.g. absence of row-level
> read locks, key-range locking, etc.
>
> As I mentioned in my reply to another poster, in many situations Oracle
> undoubtedly provides higher concurrency than locking database engines, after
> all it was designed to do so although certain compromises have been made.
>
> Regards.
>
> VC[/color]

I think your understanding of Oracle is rather slight based on what I
assume you mean.

How in mysql can you obtain a result consistent to point-in-time without
table locking all resources.

And in Oracle ... I would like a demonstration of how would go about not
having a point-in-time consistent result without intentionally
corrupting a query result.

Daniel Morgan

[VC]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090216505 .569999@yasure. ..[color=blue]
>
> I think your understanding of Oracle is rather slight based on what I
> assume you mean.[/color]

Whilst the conclusion may very well be true, your argument is unsound.
Please substitute a real premis for the vague 'based on what I assume you
mean'.
[color=blue]
>
> How in mysql can you obtain a result consistent to point-in-time without
> table locking all resources.[/color]

Please go and re-read my original response to Alex Filonov who claimed that
Oracle consistency is somehow more correct than that of DB2/MSSQL. We've
discussed DB2/MSSQL vs. Oracle consistency issues, not mysql concurrency
control.

As to mysql, the answer to your question is truly simple: MYSQL has (and
has had for quite a while) an implementation of Oracle style 'read
consistency' with rollback segments and all.
[color=blue]
>
> And in Oracle ... I would like a demonstration of how would go about not
> having a point-in-time consistent result without intentionally
> corrupting a query result.[/color]

I am not sure what you are trying to say here. Please elaborate.

VC

[Daniel Morgan]

Re: oracle - mysql comparison

VC wrote:
[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090216505 .569999@yasure. ..
>[color=green]
>>I think your understanding of Oracle is rather slight based on what I
>>assume you mean.[/color]
>
>
> Whilst the conclusion may very well be true, your argument is unsound.
> Please substitute a real premis for the vague 'based on what I assume you
> mean'.[/color]

Ok bluntly ... I don't think you know what you are talking about. A lot
less vague but I'd have preferred not to say it in those words.
[color=blue][color=green]
>>How in mysql can you obtain a result consistent to point-in-time without
>>table locking all resources.[/color]
>
> Please go and re-read my original response to Alex Filonov who claimed that
> Oracle consistency is somehow more correct than that of DB2/MSSQL. We've
> discussed DB2/MSSQL vs. Oracle consistency issues, not mysql concurrency
> control.[/color]

Same statement is true for both DB2 and MS SQL. You can not create a
point-in-time consistent read without full table locks on all resources.
[color=blue]
> As to mysql, the answer to your question is truly simple: MYSQL has (and
> has had for quite a while) an implementation of Oracle style 'read
> consistency' with rollback segments and all.[/color]

Since what version?
[color=blue][color=green]
>>And in Oracle ... I would like a demonstration of how would go about not
>>having a point-in-time consistent result without intentionally
>>corrupting a query result.[/color]
>
> I am not sure what you are trying to say here. Please elaborate.
>
> VC[/color]

I'm saying you can't, without intent, create a query result that is not
read consistent to a point-in-time.

Daniel Morgan

[VC]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090216505 .569999@yasure. ..[color=blue]
>
> I think your understanding of Oracle is rather slight based on what I
> assume you mean.[/color]

Whilst the conclusion may very well be true, your argument is unsound.
Please substitute a real premis for the vague 'based on what I assume you
mean'.
[color=blue]
>
> How in mysql can you obtain a result consistent to point-in-time without
> table locking all resources.[/color]

Please go and re-read my original response to Alex Filonov who claimed that
Oracle consistency is somehow more correct than that of DB2/MSSQL. We've
discussed DB2/MSSQL vs. Oracle consistency issues, not mysql concurrency
control.

As to mysql, the answer to your question is truly simple: MYSQL has (and
has had for quite a while) an implementation of Oracle style 'read
consistency' with rollback segments and all.
[color=blue]
>
> And in Oracle ... I would like a demonstration of how would go about not
> having a point-in-time consistent result without intentionally
> corrupting a query result.[/color]

I am not sure what you are trying to say here. Please elaborate.

VC

[Daniel Morgan]

Re: oracle - mysql comparison

VC wrote:
[color=blue]
> "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> news:1090216505 .569999@yasure. ..
>[color=green]
>>I think your understanding of Oracle is rather slight based on what I
>>assume you mean.[/color]
>
>
> Whilst the conclusion may very well be true, your argument is unsound.
> Please substitute a real premis for the vague 'based on what I assume you
> mean'.[/color]

Ok bluntly ... I don't think you know what you are talking about. A lot
less vague but I'd have preferred not to say it in those words.
[color=blue][color=green]
>>How in mysql can you obtain a result consistent to point-in-time without
>>table locking all resources.[/color]
>
> Please go and re-read my original response to Alex Filonov who claimed that
> Oracle consistency is somehow more correct than that of DB2/MSSQL. We've
> discussed DB2/MSSQL vs. Oracle consistency issues, not mysql concurrency
> control.[/color]

Same statement is true for both DB2 and MS SQL. You can not create a
point-in-time consistent read without full table locks on all resources.
[color=blue]
> As to mysql, the answer to your question is truly simple: MYSQL has (and
> has had for quite a while) an implementation of Oracle style 'read
> consistency' with rollback segments and all.[/color]

Since what version?
[color=blue][color=green]
>>And in Oracle ... I would like a demonstration of how would go about not
>>having a point-in-time consistent result without intentionally
>>corrupting a query result.[/color]
>
> I am not sure what you are trying to say here. Please elaborate.
>
> VC[/color]

I'm saying you can't, without intent, create a query result that is not
read consistent to a point-in-time.

Daniel Morgan

[michael newport]

Re: oracle - mysql comparison

.....strength - meaning, Ingres does anything that Oracle can.

CA have seen the light and are taking Ingres OpenSource.

The DB market is saturated with products that can do the job.

The advantage now is that a major database will be free of charge.

Regards
Mike

[michael newport]

Re: oracle - mysql comparison

.....strength - meaning, Ingres does anything that Oracle can.

CA have seen the light and are taking Ingres OpenSource.

The DB market is saturated with products that can do the job.

The advantage now is that a major database will be free of charge.

Regards
Mike

[Niall Litchfield]

Re: oracle - mysql comparison

"michael newport" <michaelnewport @yahoo.com> wrote in message
news:63b202d.04 07191054.58eeb8 54@posting.goog le.com...[color=blue]
> ....strength - meaning, Ingres does anything that Oracle can.
>
> CA have seen the light and are taking Ingres OpenSource.
>
> The DB market is saturated with products that can do the job.
>
> The advantage now is that a major database will be free of charge.[/color]

mySQL and PostGres *are* free of charge, and it would seem to me likely to
remain so and attract community development. If CA are really saying 'nope
we can't take the competition lets leave the market', then they should say
so.


--
Niall Litchfield
Oracle DBA

http://www.niall.litchfield.dial.pipex.com

[VC]

Re: oracle - mysql comparison


"Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
news:1090240148 .855788@yasure. ..[color=blue]
> VC wrote:
>[color=green]
> > "Daniel Morgan" <damorgan@x.was hington.edu> wrote in message
> > news:1090216505 .569999@yasure. ..
> >[color=darkred]
> >>I think your understanding of Oracle is rather slight based on what I
> >>assume you mean.[/color]
> >
> >
> > Whilst the conclusion may very well be true, your argument is unsound.
> > Please substitute a real premis for the vague 'based on what I assume[/color][/color]
you[color=blue][color=green]
> > mean'.[/color]
>
> Ok bluntly ... I don't think you know what you are talking about. A lot
> less vague but I'd have preferred not to say it in those words.[/color]

An example of my 'not knowing what I am talking about' would be nice.
Always eager to learn from my betters.
[color=blue]
>[color=green][color=darkred]
> >>How in mysql can you obtain a result consistent to point-in-time without
> >>table locking all resources.[/color]
> >
> > Please go and re-read my original response to Alex Filonov who claimed[/color][/color]
that[color=blue][color=green]
> > Oracle consistency is somehow more correct than that of DB2/MSSQL.[/color][/color]
We've[color=blue][color=green]
> > discussed DB2/MSSQL vs. Oracle consistency issues, not mysql concurrency
> > control.[/color]
>
> Same statement is true for both DB2 and MS SQL. You can not create a
> point-in-time consistent read without full table locks on all resources.[/color]

I'll kill two birds with one stone here if you don't mind. Firstly, I'll
describe a scenario where DB2/MSSQL would *not* need to lock an entire table
in order to yield a consistent result in SERIALIZABLE IL. Secondly, I'll
claim that Oracle won't be able to produce consistent data in Oracle
SERIALIZABLE under the same circumstances.

========
Imagine a simple flight reservation system represented by a single table
containing flight numbers and passenger information:

create table PASSENGERS(FLIG HT_NUMBER ..., INFO ...);
create index P_Idx on PASSENGERS(FLIG HT_NUMBER);

Let's say that a flight can accomodate maximum 50 people. Our transaction
in pseudo-code might look like this:

set transaction isolation level serializable
....
select count(*) into l_cnt from PASSENGERS where FLIGHT_NUMBER=9 99; --
check the number of sold tickets
if l_cnt < 50 insert into PASSENGERS(999, <info>...); -- if there is a
vacancy insert a row
commit;

Under DB2/MSSQL, a transaction will lock only a subset of rows (not the
whole table) for a given flight thus ensuring consistent results

P.S. I've reproduced, to the best of my recollection, the 'overbooking'
example discussed a couple of years ago,
========
..[color=blue]
>[color=green][color=darkred]
> >>And in Oracle ... I would like a demonstration of how would go about not
> >>having a point-in-time consistent result without intentionally
> >>corrupting a query result.[/color]
> >
> > I am not sure what you are trying to say here. Please elaborate.
> >
> > VC[/color]
>
> I'm saying you can't, without intent, create a query result that is not
> read consistent to a point-in-time.[/color]

Under Oracle, the above reservation scenario will lead to overbooked
flights. Figuring out why and how to fix the problem is left as an exercise
for the interested student.
[color=blue]
>[color=green]
> > As to mysql, the answer to your question is truly simple: MYSQL has[/color][/color]
(and[color=blue][color=green]
> > has had for quite a while) an implementation of Oracle style 'read
> > consistency' with rollback segments and all.[/color]
>
> Since what version?[/color]

Since Version 3.23 (March 2001).