transactions rollback and functionality question

[adrian vda]

Hi all,

i think i am not getting something right with transactions. It simply doesn't do what i expect it to do. Here are some statements which in my opinion should result in an empty table. instead the table "test" has after executing the statement 1 row.

<code>
mysql> delete from test;select * from test;set autocommit=0;select @@autocommit;start transaction;insert into test (name) values ('a');rollback;select * from test;
Query OK, 1 row affected (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

+----+------+
| id | name |
+----+------+
| 20 | a |
+----+------+
1 row in set (0.00 sec)

</code>


I expect that with autocommit=false and after the rollback statement the insert statement to be rolled back so the table should be empty.

What am i doing wrong?
Please help!

mysql server: mysql Ver 14.12 Distrib 5.0.67, for debian-linux-gnu (i486) using readline 5.2



Edited 2 time(s). Last edit at 01/19/2009 04:07PM by adrian vda.