Can MySql rollback a sql transaction over multiple tables?

Question

I have searched the MySql documentation and found nothing. I have tried this ... the Rollback doesn't seem to cancel the inserts made in table1 and table2

Start Transaction;

INSERT INTO Table1(field1,field2) VALUES (value1, value2);

INSERT INTO Table2(field3,field4) VALUES (value3, value4);

INSERT INTO Table3(field5,field6) VALUES (value5, value6);

Rollback;

UPDATE: Thanks for all answers, but I forgot to tell that the 3rd statement throws an exception (Constraint Violation).

Answer 1

Try adding this to the beginning of your script:

SET autocommit=0;

By default, MySQL will issue a commit after each statement, unless you tell it otherwise.

Answer 2

Yes, but only for tables that support transactions. To check if your tables are compatible, do this

SELECT table_name
     , engine
  FROM information_schema.tables t
 WHERE t.table_name IN('Table1','Table2','Table3');

If any of them are MyISAM, they are not transaction compatible. You'll need to change them all to InnoDB

ALTER TABLE [TableName] ENGINE=InnoDB;

But be warned - MyISAM supports some features that InnoDB does not, such as FULLTEXT searches.

Answer 3

As of current version of MySql v5.1 ... it does not support rollback over multiple tables ... That might change (I hope) in the near future!