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

Question

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.

Answer 1

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.

Answer 2

Certain statements trigger an implicit commit. ALTER TABLE is definitely one of them.

See my post row locking within ACID transaction innodb on all statements that do this.

With regard to your transaction, any of the updates that fail will rollback previous updates. You need to create a flag that checks whether or not the transaction went to completion. You need to setup Dynamic SQL to pull that off. Perhaps something like this:

SET @OK_TO_ALTER = 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' .......;
SET @OK_TO_ALTER = 1;
COMMIT;
SET @sql = 'SELECT 1 DoNothing';
SET @sql = IF(@OK_TO_ALTER=1,'ALTER TABLE `table2` ADD COLUMN `.....` bool NOT NULL',@sql);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

BTW you do not need set autocommit = 0; for a START TRANSACTION; ... COMMIT; block.