MySQL transaction isolation levels broken?

Question

I can't seem to get MySQL 5.0.32 on 32bit x86 Debian to honour transaction isolation levels.

I've reduced my problem to its simplest form, tested with the mysql command-line client:

-- On node writer:
--

DROP TABLE test;
CREATE TABLE test (
    name VARCHAR(255)
);

set autocommit=0;
set transaction isolation level read committed;
begin;

-- On node reader:
--

set autocommit=0;
set transaction isolation level read committed;
begin;

-- On node writer:
--

INSERT INTO test VALUES ('bob');

-- On node reader:
--

SELECT * from test;
-- Returns the row with bob in it!!!

Probably related, I've noticed that the row remains even after a rollback!

So my quess is that autocommit isn't really disabled, and that transaction isolation levels are thus effectively ignored?

Ciao, Sheldon.

Answer 1

Your tables seem to be created in MyISAM by default.

It doesn't support transactions.

Could you please run the following:

SELECT @@storage_engine

Answer 2

Sorry for question, but are you sure you are usign innodb table? You have to check your default storage engine.