MySQL trouble on rollback with update

[tourneur]

Hi!

I'm relatively new with MySQL. I already worked a bit with pgsql, Oracle or O2 and I really got surprised when MySQL couldn't roll back the effect of a single update run in a transaction.

I'm currently using MySQL 4.0.18 and here is the thing:

The code works on a table named process_lock defined with

CREATE TABLE process_lock (
type INT NOT NULL,
count INT NOT NULL,
PRIMARY KEY (type)
)

and contains a single row defined with

INSERT INTO process_lock(type, count) VALUES (0, 0);

Then I ran these three commands below. See the history:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)

mysql> UPDATE process_lock SET count=1 WHERE type=0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> ROLLBACK;
ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back

I ran a select to check:

mysql> SELECT * FROM process_lock;
+------+-------+
| type | count |
+------+-------+
| 0 | 1 |
+------+-------+
1 row in set (0.00 sec)

I tried these in all available session transaction isolation levels without success.

Does someone have a clue about that. Turned another way, what are transactions for on MySQL??

Anymay, thanks if someone cares to answer.

T.Tourneur