Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can MySql rollback a sql transaction over multiple tables?

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).

like image 963
Jalal El-Shaer Avatar asked Jun 22 '10 15:06

Jalal El-Shaer


2 Answers

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.

like image 137
Peter Bailey Avatar answered Sep 30 '22 04:09

Peter Bailey


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.

like image 29
Eric Petroelje Avatar answered Sep 30 '22 04:09

Eric Petroelje