Mysql – Transaction has set autocommit = 0, but still the update happens before I commit

ddlMySQLtransaction

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of statements:

set autocommit = 0;
START TRANSACTION;
use database1;
update `table1` set tkey = '3' .....;
update `table1` set tkey = '4' .......;
update `table1` set tkey = '5' .......;
update `table1` set tkey = '6' .......;
ALTER TABLE `table2` ADD COLUMN `.....` bool NOT NULL; -- This fails
COMMIT;

In above transaction the alter against table2 fails, but still the updates to table 1 have happened, how can I avoid that? I want a rollback even if one statement fails.

Best Answer

In MySQL most DDL statements, including ALTER TABLE, implicitly issue a COMMIT before executing. It's described in the manual.

One way to do what you want is reorder your statements, placing updates after the alter.

Related Question