MySQL 嵌套事务及回滚

Question

有人能告诉我是否可以在一个过程中调用另一个过程，并且如果任何一个过程的任何部分失败，都会回滚所有内容吗？

如果可能的话，能否举个小例子来说明如何实现？

编辑：过程“b”失败了，但是过程“a”仍向表“a”插入了一行。 据我所知，如果插入的任何部分失败，则会回滚所有内容（包括两个插入），但这里并没有发生。 问题是为什么？

“过程a”：

BEGIN
  DECLARE b INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

  START TRANSACTION;
    INSERT INTO a(a)
    VALUES(iA);

     CALL b(iB,LAST_INSERT_ID(),@b);
     SELECT @b INTO b;

     IF b !=1 THEN
        ROLLBACK;
      ELSE
        COMMIT;
  END IF;
END

"b"过程

BEGIN
  DECLARE b INT DEFAULT 0;
  DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

  START TRANSACTION;
    INSERT INTO b VALUES(iB,id);
    SET b=1;
  COMMIT;
END;

- NaN

Answer 1

您需要在两个过程中处理事务，但是调用另一个过程的过程应该检查返回值并根据其回滚事务。以下是内部过程的示例：

如何在MySQL存储过程中检测回滚？

然后，您将检查p_return_code并回滚父事务。

编辑：

我认为发生的情况是内部SP COMMIT或ROLLBACK会影响外部SP TRANSACTION。如果内部SP失败，则此代码对我有效，它将回滚两个插入语句。对ab()的第一次调用有效，新用户记录被插入并插入了新游戏记录，如果我们从游戏表中删除记录并再次运行ab()，因为用户ID已经存在，它将回滚游戏表插入：

create procedure ab()
BEGIN
  START TRANSACTION;
    INSERT INTO games (title) VALUES ('bad game');
    CALL ba(@ret);
    IF @ret!=0 THEN
      ROLLBACK;
    ELSE
      COMMIT;
    END IF;
END;


create procedure ba(OUT return_value tinyint unsigned)
BEGIN
  DECLARE exit handler for sqlexception
  BEGIN
    set return_value = 1;
  END;

  INSERT INTO users (id) VALUES(1);
  set return_value = 0;
END;

测试使用 call ab();

- iouri

---

iouri，很遗憾这个方法不起作用。当第二个过程失败时，第一个插入操作永远不会回滚。我甚至尝试使用第二个过程的输出值在第一个过程中进行测试，并尝试根据此进行回滚。但是无论我尝试什么都不起作用。第一个过程的插入操作总是会发生。 - NaN

第一个程序中的插入语句是否在START TRANSACTION;语句内？ - iouri

iouri，你有没有想法为什么当第二个过程失败时我不能回滚第一个过程？我需要一个保存点吗？ - NaN

当然，iouri。我会把它发布在顶部。谢谢你帮我看一下这个。 - NaN

嗨，iouri，感谢您提供的示例。昨晚，我想到了有关内部过程提交外部过程的相同理论。当我从内部过程中删除事务时，我也能够使其正常工作。由于内部过程有时可能会被单独调用，如果可能的话，我希望保留它的事务。您知道是否有一种方法可以这样做，同时将其用作内部过程吗？ - NaN

也许可以添加一个条件布尔参数来启动或不启动自己的事务？您总是可以创建另一个过程并自行调用它。 - iouri