[Gllug] MySQL Woes

[Matthew Kirkwood]

On 15 Jun 2002, Nix wrote:

> > Oracle had MVCC before postgres.
>
> I see no significant sign of it. Old-style row-level
> block-whenever-you- can is the algorithm it seems to use to me.

Updates block other updates, but that's all.  Readers don't
block writers, and vice-versa.

Try this (you should see the same results in both Oracle and
Postgres):

conn0> create table foo (a numeric(2));
conn0> insert into foo select rownum from all_objects where rownum < 10;
conn0> commit;

conn1> select * from foo;
[1 - 9]

conn2> update foo set a = a + 1;
[non-MVCC engines would block here until a commit or rollback from conn1]

conn1> select * from foo;
[1 - 9]

conn2> select * from foo;
[2 - 10]
conn2> commit;

conn1> select * from foo;
[2 - 10]

> MVCC tries to avoid blocking and hits you with errors at
> commit/rollback time...

The usual MVCC commit-time error is "snapshot too old".  Anything
"update"d or "select for update"d still gets locked.

There are looser forms of synchronisation, where two transactions
can upate the same row, but the second one to commit will fail,
but that's not very useful in an OLTP environment.

Matthew.


-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug