Могу ли я использовать транзакции с ALTER TABLE?

Question

Могу ли я использовать транзакции с ALTER TABLE?

Я новичок (на самом деле полный новичок) в транзакциях SQL, поэтому, возможно, я упускаю что-то очевидное.

У меня есть этот SQL-код, который я пытаюсь запустить через 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;

Как вы можете видеть, второй ALTER вызывает ошибку #1054 - Unknown column 'number' in 'users'.

Но, когда это происходит в phpMyAdmin, я вижу, что первые два INSERT (или вся транзакция) не откатываются. Таблица users содержит две новые записи.

Чего мне не хватает? phpMyAdmin - администратор не поддерживает транзакции? Или я не понимаю, как на самом деле работают транзакции, и это довольно нормально, что эти два INSERT не откатываются в случае ошибки?

4

mysql sql transactions phpmyadmin rollback

Author: user2864740, 2014-04-02

Source

1 answers

score 6 · Accepted Answer

Некоторые операторы (в первую очередь DDL) в MySQL вызывают неявную фиксацию до они выполняются и не могут быть откатаны - таким образом, это также предотвращает откат предыдущих изменений DML.

Инструкции, перечисленные в этом разделе (и любые синонимы для них) , неявно завершают любую транзакцию, активную в текущем сеансе, как если бы вы выполнили ФИКСАЦИЮ перед выполнением инструкции . Начиная с MySQL 5.5.3, большинство эти инструкции также вызывают неявную фиксацию после выполнения; дополнительные сведения см. в конце этого раздела.

Поскольку ALTER TABLE является одним из затронутых операторов, пакет SQL эффективно обрабатывается как:

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;

Предлагаемое решение состоит в том, чтобы разделять DDL и DML. В документации говорится:

Вы должны разработать свои транзакции [DML] таким образом, чтобы они не включали такие операторы [DDL]. Если вы выпишете выписку в начале транзакции это не может быть откатано, а затем другой оператор позже завершится неудачей, в таких случаях полный эффект транзакции не может быть откатан путем выдачи инструкции ОТКАТА.