如何在MySQL事务中回滚所有语句？

Question

我需要更新一个表 (bigtable) 中另一张表的 id (外键约束为 oldsmalltable) 所在的特定列，使其指向另一个表的 id (外键约束为 newsmalltable)。基本上就是这样做：

DELIMITER //

CREATE PROCEDURE updatebigtable ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK;

    START TRANSACTION;
        ALTER TABLE bigtable DROP FOREIGN KEY bigtable_ibfk_1,
            MODIFY smalltable_id SMALLINT ;
        UPDATE bigtable SET smalltable_id=CASE smalltable_id
                WHEN 1 THEN 1592
                WHEN 2 THEN 1593
                WHEN 3 THEN 1602
                ...
                ELSE 0
            END;
        ALTER TABLE bigtable ADD CONSTRAINT bigtable_ibfk_1
            FOREIGN KEY(smalltable_id) REFERENCES newsmalltable(id);
    COMMIT;
END//
DELIMITER ;

CALL updatebigtable();
DROP PROCEDURE updatebigtable;

我需要确保如果由于某些原因新的外键约束失败（例如，使用不同类型的列，错误将出现在最后一个alter table语句上），则UPDATE和第一个ALTER TABLE也应该回滚，即它们应该保持最初的状态。

根据MySQL文档，通过使用START TRANSACTION，该事务的自动提交模式被禁用，这将不允许：

只要您执行更新（修改）表的语句，MySQL就会将更新存储在磁盘上以使其永久化。

我只发现这个问题与我的问题有最少的关联： 如何在MySQL存储过程中使用事务？ 如果在事务内发生了我提到的错误，则先前的语句已经执行，并且更新已经“永久地存储在磁盘上”...

我还尝试在创建过程之前放置SET autocommit=0;，但行为仍然相同... 我错过了什么吗？还是这是MySQL事务回滚的预期行为？

如果有任何区别，我正在使用MySQL v.5.6.17。

- Armfoot

---

检查：13.3.3 导致隐式提交的语句。 - wchiquito

Answer 1

ALTER TABLE语句总是会导致隐式提交 (MySQL文档第13.3.3节, 感谢 wchiquito)，这意味着即使它们在一个START TRANSACTION; ... COMMIT;块中，也将有与该块中执行的更改数量一样多的提交。

锁定表也不是一个选择，因为(ALTER TABLE的问题):

如果你在事务性表上使用ALTER TABLE或者你使用Windows系统，ALTER TABLE会解除之前通过LOCK TABLE所设置的锁。这是因为InnoDB和这些操作系统无法删除正在使用的表。

唯一剩下的选择就是模拟所有ALTER TABLE步骤来避免在执行修改和更新语句时出现不必要的读写:

1. 创建一个名为A-xxx的新表，并带有所请求的结构更改。
2. 将原始表中的所有行复制到A-xxx。
3. 将原始表重命名为B-xxx。
4. 将A-xxx重命名为你的原始表名称。
5. 删除B-xxx。

这样，更新可以在新表中完成 (第2步之后)，只有在执行第3和第4步(重命名)时bigtable才不可用。

- Armfoot

Answer 2

使用TRY CATCH块，在BEGIN TRY之前使用BEGIN TRAN，CATCH块内使用ROLLBACK TRAN。

- Piotr Sowiak

---

try-catch语句 -> common-schema? - wchiquito

MySQL，而不是MS SQL。 - Marc L.