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).
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With