Heikki,
Heikki Tuuri schrieb:
>
> Hi!
>
> ----- Original Message -----
> From: ""Orr, Steve"" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.mysql
> Sent: Thursday, September 26, 2002 9:56 PM
> Subject: RE: MyISAM or InnoDB
>
> > <PROVOCATIVE RANT>
> >
> > MySQL doesn't provide "native" support for transactions but is dependent
> on
> > the Berkeley or InnoDB table types.
> >
> > Does MySQL really provide TRUE row level locking? That's the claim but is
> it
>
> Does Oracle really provide TRUE row level locking? A Microsoft pamphlet said
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Yes, always!
> that if you set the transaction isolation level SERIALIZABLE in Oracle, then
> Oracle uses an optimistic validation method which is page level. I have not
^^^^^^^^^^^
No, in Oracle there is no lock escalation - ever!
This 'optimistic validation method' probably refers to the fact that in
serializable mode Oracle provides the same level of concurrency as usual but
there is a price to pay: if during your transaction someone else modifies data
that you also want to modify you will not be allowed to do so. That's because
your transaction must be guaranteed to get always the data as it were at the
point in time the transaction started.
> checked from the latest Oracle manual if this is still true. Or you must use
Should you have the time and opportunity look at the book "expert 101 - Oracle",
written by Oracle's Tom Kyte. He has also a fantastic web site:
http://asktom.oracle.com/
> table level manual locks.
>
> Does MS SQL Server really provide TRUE row level locking? If you set many
> locks, then it escalates row lock first to pages, and eventually to tables.
>
> > valid? The InnoDB web site seems to be more accurate by explaining that
> > locking is a next-key value locking mechanism that is indexed based.
> Here's
> > what the InnoDB manual says, "InnoDB does the row level locking so that
> when
> > it searches or scans an index of a table, it sets shared or exclusive
> locks
> > on the index records in encounters. Thus the row level locks are more
> > precisely called index record locks."
> >
> > I developed some of my own benchmark tests which intentionally stress
> > transactions in an artificial manner. The results were that MySQL with
> > InnoDB didn't even come close to the performance of Oracle. Maybe my tests
> > weren't very realistic and maybe a real world application won't encounter
> > locking problems but I wouldn't just assume that InnoDB will scale like
> > Oracle or meet the needs of your application. You've got to test it!
>
> Since a big customer requested this, MySQL/InnoDB-4.0.5 will feature the
> following READ COMMITTED isolation level which more closely imitates Oracle:
My organization would also prefer this isolation level!
>
> <cut>
> > > > > >
> > > > > > READ COMMITTED
> > > > > >
> > > > > > which will switch off next-key locks from
> > > > > >
> > > > > > 1) SELECT ... FOR UPDATE;
> > > > > > 2) SELECT ... LOCK IN SHARE MODE;
> > > > > > 3) any UPDATE or DELETE which touches only a single row
> > > through a
> > > > > unique
> > > > > > index.
> > > > > > 4) Furthermore, a plain consistent read SELECT will
> > > read the latest
> > > > > > snapshot
> > > > > > like in Oracle.
> > > > > >
> > > > > > It is NOT possible to switch off next-key locks from a
> > > range DELETE or
> > > > > > UPDATE, because phantom rows would break MySQL replication and
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> > > > > recovery.
That's good to know!
> <cut>
>
> > "Open-source database providers readily admit that their databases are not
> > yet geared toward powering high-end enterprise systems, such as ERP and
> CRM
> > applications." From an Infoworld article about open-source databases
> > entitled "Finding an opening..."
> > http://www.infoworld.com/articles/pl/xml/02/09/23/020923pldatabses.xml
> >
> > One more thing to rant on... Regarding benchmark test...
> > http://www.mysql.com/doc/en/MySQL-PostgreSQL_bench_marks.html
> > Quoting from this page: "We know of two benchmark tests that claim that
> > PostgreSQL performs better than MySQL Server. These both where multi-user
> > tests, a test that we here at MySQL AB haven't had time to write and
> include
> > in the benchmark suite, mainly because it's a big task to do this in a
> > manner that is fair to all databases."
> >
> > It's "funny" how MySQL AB just hasn't had time to write a multi-user
> > benchmark. Based on the age of the link they haven't had time for a long
> > time now. Yet they say benchmarking is important? Give me a break!
>
> We have run pgbench and compared it to similar mysqlbench.
>
> At 5 users MyISAM-4.0.2 was 1.3 times as fast as Pg-7.2.1, and InnoDB was 5
> times as fast.
> At 100 users MyISAM was 2 times as fast as Pg and InnoDB was 5 times as
> fast.
>
> I have been hoping some independent party would run these tests and publish
> the numbers.
>
> > What we desperately need are some TPC benchmarks for the open-source
> > databases. Benchmarks which are independenttly audited and verified.
>
> eWEEK ran a multiuser benchmark on DB2, SQL Server, Oracle, Sybase,
> MySQL/InnoDB:
> http://www.eweek.com/article2/0,3959,293,00.asp
>
> There are few independent benchmarks on databases because running a big
> benchmark requires time, and consequently money. Furthermore, the licenses
> of Oracle and several other databases forbid publication of benchmarks
> without a permission from the vendor.
>
> > </PROVOCATIVE RANT>
>
> Best regards,
>
> Heikki Tuuri
> Innobase Oy
> ---
--cut
Kind Regards,
--
Dr. Frank Ullrich, Netzwerkadministration
Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover
E-Mail: [EMAIL PROTECTED]
Phone: +49 511 5352 587; FAX: +49 511 5352 538
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
