MySql 存储过程、事务和回滚

发布于 2024-09-25 01:16:12 字数 816 浏览 9 评论 0原文

我找不到在 MySql 存储过程中使用事务的最佳方法。如果出现任何问题，我想ROLLBACK：

BEGIN

    SET autocommit=0;
    START TRANSACTION;

    DELETE FROM customers;
    INSERT INTO customers VALUES(100);
    INSERT INTO customers VALUES('wrong type');

    COMMIT;
END

1) 是否需要autocommit=0？

2) 如果第二个 INSERT 中断（当然确实如此），第一个 INSERT 不会回滚。该过程只是继续到COMMIT。我怎样才能防止这种情况发生？

3）我发现我可以DECLARE HANDLER，我应该使用这个指令还是有一个更简单的方法来表示如果任何命令失败，存储过程应该ROLLBACK并且也失败了？

DECLARE HANDLER 工作正常，但由于我有 MySql 版本 5.1，所以无法使用 RESIGNAL。因此，如果发生错误，调用者将不会收到通知：

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
    ROLLBACK; 
    -- RESIGNAL; not in my version :(
END;

START TRANSACTION;

原文

I can't find an optimal way to use transactions in a MySql Stored Procedure. I want to ROLLBACK if anything fails:

BEGIN

    SET autocommit=0;
    START TRANSACTION;

    DELETE FROM customers;
    INSERT INTO customers VALUES(100);
    INSERT INTO customers VALUES('wrong type');

    COMMIT;
END

1) Is autocommit=0 required?

2) If the second INSERT breaks (and it does of course) the first INSERT is not rolled back. The procedure simply continues down to the COMMIT. How can I prevent this?

3) I've found I can DECLARE HANDLER, should I use this instruction or is there a simpler way to say that if any command fails, the stored procedure should ROLLBACK and fail too?

DECLARE HANDLER works fine, but since I have MySql version 5.1 I can't use RESIGNAL. So if an error occurs, the caller won't be notified:

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
    ROLLBACK; 
    -- RESIGNAL; not in my version :(
END;

START TRANSACTION;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

櫻之舞 2024-10-02 01:16:12

答案1：不需要设置autocommit=0

使用 START TRANSACTION，自动提交将保持禁用状态，直到您结束
使用 COMMIT 或 ROLLBACK 进行事务处理。然后恢复自动提交模式
恢复到之前的状态。

http://dev.mysql.com/doc/refman/5.6/en/commit.html< /a>

回复收藏 0 原文

聚集的泪 2024-10-02 01:16:12

回答 2 的不同方法：您可以使用布尔变量来知道是否应该提交或回滚。例如：

BEGIN

DECLARE `should_rollback` BOOL DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `should_rollback` = TRUE;

START TRANSACTION;

DELETE FROM customers;
INSERT INTO customers VALUES(100);
INSERT INTO customers VALUES('wrong type');

IF `should_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END

或者，您可以使用非常有用的 3)

Different Approach to Answer 2: You could use a boolean variable to know if you should COMMIT or ROLLBACK. For example:

BEGIN

DECLARE `should_rollback` BOOL DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `should_rollback` = TRUE;

START TRANSACTION;

DELETE FROM customers;
INSERT INTO customers VALUES(100);
INSERT INTO customers VALUES('wrong type');

IF `should_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END

Or, you could use your very usefull 3)

回复收藏 0 原文

~没有更多了~