我是SQL交易的初学者(实际上是新手),所以我可能缺少明显的东西.
I'm a beginner (actually complete newbie) to SQL transactions, so I may be missing something obvious.
我有此SQL代码,正在尝试通过 phpMyAdmin 运行:
I have this SQL code, that I'm trying to run through phpMyAdmin:
START TRANSACTION; INSERT INTO `users` VALUES(NULL, 'User A', 'user.a@example', '4', 'User A'); INSERT INTO `users` VALUES(NULL, 'User B', 'user.b@example', '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'错误.
As you may see, second ALTER causes #1054 - Unknown column 'number' in 'users' error.
但是,当它发生在 phpMyAdmin 中时,我可以看到,前两个INSERT(或整个事务)没有回滚. users表确实包含两个新记录.
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.
我想念什么? phpMyAdmin不支持交易吗?还是我不明白交易实际上是如何工作的,这很正常,如果发生错误,这两个INSERT不会回滚?
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?
推荐答案某些语句(最值得注意的是 DDL )在MySQL 之前,它们将被执行并无法回滚-这样可以防止先前的DML更改也回滚.
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.
本节中列出的语句(及其任何同义词)隐式结束当前会话中任何活动的事务,就好像您在执行该语句之前执行了COMMIT 一样.从MySQL 5.5.3开始,大多数这些语句在执行后也会引起隐式提交.有关更多详细信息,请参阅本节末尾.
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.
由于ALTER TABLE是受影响的语句之一,因此SQL批处理有效地视为:
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', 'user.a@example', '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分开. 文档说:
The suggested solution is to keep DDL and DML separated. The documentation says:
您应设计[DML]事务,使其不包含此类[DDL]语句.如果您在事务中提早发布了一个无法回滚的语句,然后又有另一个语句失败,则在这种情况下,通过发出ROLLBACK语句就无法回滚事务的全部效果.
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.
更多推荐
我可以在ALTER TABLE中使用事务吗?
发布评论