mysql Rollback and begin

[amahmood]

I am trying to learn transactions. to simplify thins I put a code like this:

mysql_query("BEGIN");

$sql = "INSERT INTO customer VALUES('$name','$code')";
mysql_query($sql);

mysql_query("ROLLBACK");

I expected that the last line canceled all queries but it didn't have any effect. What have I missed?

[phpnovice]

My read of MySQL tells me that it does not support transactional SQL.

[Stephen Philbin]

MySQL does support foreign keys and transactions, however you must be working on tables that are of the Innodb type. You can use transactions on a few other table types, but I forget their names and I don't think they support foreign keys.

If your database has recently been upgraded, then BEGIN will not work. Beging is depreciated and is replaced with the SQL standard of "START TRANSACTION". So if it does not recognise "BEGIN" as a transaction starter, then auto commit mode will remain active and "ROLLBACK" will appear to have no effect.

To declare a table as Innodb type, you must specify it at the time of creation:

CREATE TABLE IF NOT EXISTS folks (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL DEFAULT '',
PRIMARY KEY (id))ENGINE=Innodb DEFAULT CHARSET=utf8;


Be sure to use the instruction "ENGINE=" rather than the depreciated "TYPE=" instruction. Once your tables are of the right type and you're using current (My)SQL you should be pretty much fine. Just remember that you can't send multiple queries in one go when you're using PHP though. PHP rather annoyingly assumes you are trying to do something you are not if you try, and consequently screws the entire thing up.

Good luck with the learning and come back if you have more questions. ;)