Re: PgSQL MVCC vs MySQL InnoDB

Hello

Harrison Fisk from MySQL claims in this thread:

http://forums.mysql.com/read.php?35,3981,4245#msg-4245

That there are no major differences between InnoDB and MVCC concurrency.

Is this true?

Thank you.

Tim

On 10/25/2004 11:53 AM, nd02tsk(at)student(dot)hig(dot)se wrote:

> Hello
>
> Harrison Fisk from MySQL claims in this thread:
>
> http://forums.mysql.com/read.php?35,3981,4245#msg-4245
>
> That there are no major differences between InnoDB and MVCC concurrency.
>
> Is this true?

From a functional point of view, the two appear to do the same thing.
The implementation is certainly different and has pros and cons into
both directions. The statement is absolutely accurate and also explains
why Postgres never had any sort of file structure repair tools. I would
even go further in that you don't want to repair any corrupted files, as
those repair operations might introduce breakage of referential
integrity of your data.

Jan

>
> Thank you.
>
> Tim
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

On Mon, Oct 25, 2004 at 01:15:33PM -0400, Jan Wieck wrote:
> On 10/25/2004 11:53 AM, nd02tsk(at)student(dot)hig(dot)se wrote:
>
> >Is this true?
>
> From a functional point of view, the two appear to do the same thing.

Well, except for one difference. InnoDB will allow you refer to
tables not controlled by the InnoDB table handler, whereas we don't
have that problem with MVCC. So under MVCC, by definition, you can't
have partial transaction failures. (Or, more precisely, any such
partial failure is a bug in PostgreSQL, but in MySQL it might be a
feature.)

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland

On 10/25/2004 2:42 PM, Andrew Sullivan wrote:

> On Mon, Oct 25, 2004 at 01:15:33PM -0400, Jan Wieck wrote:
>> On 10/25/2004 11:53 AM, nd02tsk(at)student(dot)hig(dot)se wrote:
>>
>> >Is this true?
>>
>> From a functional point of view, the two appear to do the same thing.
>
> Well, except for one difference. InnoDB will allow you refer to
> tables not controlled by the InnoDB table handler, whereas we don't

That is a (mis)feature of MySQL itself, not of the InnoDB storage engine
if used in a mixed table type query by MySQL. InnoDB does not control
what is done in the "upper management" with the rows it returns from a
scan. This kind of abuse is as much InnoDB's fault as it is your fault
when your quite accurate work is merge-joined with marketing material
for decision making.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Andrew Sullivan wrote:
> On Mon, Oct 25, 2004 at 01:15:33PM -0400, Jan Wieck wrote:
>
>>On 10/25/2004 11:53 AM, nd02tsk(at)student(dot)hig(dot)se wrote:
>>
>>
>>>Is this true?
>>
>>From a functional point of view, the two appear to do the same thing.
>
>
> Well, except for one difference. InnoDB will allow you refer to
> tables not controlled by the InnoDB table handler, whereas we don't
> have that problem with MVCC.

From MySQL gotchas:

1) And the same "feature" allow also to start a transaction, mix the two
tables and have a warning only after the "rollback" about the inability
destroy the updates done on non INNODB tables.

2) Create or delete and index or alter a table will recreate the entire
table.

3) Our rollback is a O(1) operation not O(N) where N is the operations
performed during the transaction

Regards
Gaetano Mendola

On Mon, Oct 25, 2004 at 03:45:40PM -0400, Jan Wieck wrote:
> That is a (mis)feature of MySQL itself, not of the InnoDB storage engine
> if used in a mixed table type query by MySQL.

Sure, but I think this difference is very far from plain in the
marketing literature promoting MySQL with InnoDB. You actually need
to understand the difference between MySQL and its storage engines to
understand why this is the case, and one can't really expect people
who are evaluating different systems to know all that sort of detail
in advance.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland