Can I use transactions with ALTER TABLE?
I'm a beginner (actually newbie) to SQL transactions, so I may be missing something obvious.
I have this SQL code, that I'm trying to run through phpMyAdmin:
START TRANSACTION;
INSERT INTO `users` VALUES(NULL, 'User A', '[email protected]', '4', 'User A');
INSERT INTO `users` VALUES(NULL, 'User B', '[email protected]', '3', 'User B');
ALTER TABLE `users` CHANGE `level` `level` TINYINT(3) UNSIGNED NOT NULL;
ALTER TABLE `users` CHANGE `number` `number` INT(10) UNSIGNED NOT NULL;
ALTER TABLE `users` ADD COLUMN `number` INT(10) UNSIGNED NOT NULL AFTER `id`;
COMMIT;
Second ALTER causes #1054 - Unknown column 'number' in 'users' error.
But, when it happens in phpMyAdmin, I can see, that first two INSERTs (or the entire transaction) is not rolled back. The users table does contain two new records.
What am I missing? phpMyAdmin doesn't support transactions? Or I don't understand, how transactions actually work, and this is pretty normal, that these two INSERTs are not rolled back in case of error?
Answer
Some statements (most notably DDL) in MySQL cause an implicit commit before they are executed and cannot be rolled back - as such this prevents the prior DML changes from being rolled back as well.
The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement. As of MySQL 5.5.3, most of these statements also cause an implicit commit after executing; for additional details, see the end of this section.
Since ALTER TABLE is one of the affected statements, the the SQL batch is effectively treated as:
START TRANSACTION;
INSERT INTO `users` VALUES(NULL, 'User A', '[email protected]', '4', 'User A');
COMMIT; -- prevents ROLLBACK of insert(s), even if DDL fails
ALTER TABLE `users` CHANGE `level` `level` TINYINT(3) UNSIGNED NOT NULL;
The suggested solution is to keep DDL and DML separated. The documentation says:
You should design your [DML] transactions not to include such [DDL] statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.