存储过程嵌套调用时的事务嵌套问题

场景如下:在一个存储过程中调用另外一个存储过程,要求在内部存储过程抛出异常时,回滚外部存储过程的所有操作,经过测试,mysql在调用内部存储过程时,应该是自动提交了外部存储过程的事务,不知道mysql是否支持这种非扁平事务的嵌套,恳请高手指点迷津!

讨论数量: 4

MySQL 不支持直接事务嵌套。但是可以参考 Laravel 中的实现,使用 savepoint。

1年前 评论

我大概试过扁平化事务(业务代码在同一个存储过程中,不涉及存储过程嵌套)的savepoint,是没问题的,但是这种涉及存储过程嵌套调用的非扁平化事务,好像不行,以下是我的代码示例
CREATE DEFINER=shuju@% PROCEDURE p_test_outer(OUT result VARCHAR (200),
OUT resultinfo VARCHAR (10000))
BEGIN

Routine body goes here...

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS @cno = NUMBER;
        GET DIAGNOSTICS CONDITION @cno
            @result = RETURNED_SQLSTATE, @resultinfo = MESSAGE_TEXT;
        ## 需要在异常处理中回滚外部存储过程的所有操作,但是CALL p_bcp_scheduler_generate之前的insert操作已经被提交了
                    ROLLBACK;
        INSERT INTO business_log
            SELECT NULL,
                         NULL,
                         CONCAT('p_test_outer抛出异常:', @result, '-', @resultinfo),
                         CURRENT_TIMESTAMP;
        SET result = @result;
        SET resultinfo = @resultinfo;
    END;

    START TRANSACTION;
    INSERT INTO business_log
        SELECT NULL,
                     NULL,
                     'p_test_outer运行信息',
                     CURRENT_TIMESTAMP;
    CALL p_bcp_scheduler_generate(@result,@resultinfo);  ##此过程调用将抛出异常
    COMMIT;

END

1年前 评论

在 MySQL 中,存储过程和事务是两个独立的概念。MySQL 支持嵌套事务,但是默认情况下采用的是“非扁平事务模型”,也就是说,在嵌套事务中,默认情况下内部事务提交后不会自动提交外部事务,需要手动进行提交。

如果你希望在内部存储过程抛出异常时回滚外部存储过程的所有操作,可以尝试在外部存储过程中使用事务,并通过 SET autocommit = 0; 关闭自动提交功能。在这种情况下,外部事务只有在手动提交时才会生效,从而可以避免因为内部存储过程抛出异常而导致的未能回滚的问题。具体实现方式如下:

CREATE PROCEDURE outer_proc()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Error: Rollback all operations';
    END;

    -- 关闭自动提交
    SET autocommit = 0;

    -- 在事务中调用 inner_proc 存储过程
    START TRANSACTION;
    CALL inner_proc();
    COMMIT;

    -- 重新打开自动提交
    SET autocommit = 1;
END;

在上面的代码中,我们首先定义了一个外部存储过程 outer_proc(),并在其中声明了一个异常处理器。在开始执行之前,我们通过 SET autocommit = 0; 关闭了自动提交功能,并在事务中调用了内部存储过程 inner_proc()。如果内部存储过程执行过程中抛出异常,那么异常处理器会回滚事务并抛出异常,从而保证了所有的操作都被正确回滚。最后,在完成所有操作后,我们通过 SET autocommit = 1; 重新打开了自动提交功能。

总之,通过在外部存储过程中使用事务,并在其中手动控制事务的提交和回滚,你可以更好地控制内部存储过程的异常情况,避免因为内部存储过程抛出异常而导致的未能回滚的问题。

1年前 评论

@滚球兽进化
按照您给的思路进行了测试,如下:在outer过程的异常处理中没有commit和set auocommit=1(经测试set autocommit会自动提交当前事务),同时在inner过程的异常处理中对写日志的操作进行了commit(inner过程未对autocommit进行操作),在其它会话中查看执行结果是:outer过程中的正常写日志操作(p_test_outer运行信息)和inner过程异常处理中的写日志操作被提交了,由此推论在调用inner过程之前,对写“p_test_outer运行信息”的insert操作进行了强制提交;为了进一步验证,在inner过程中做了一个长延时,延时过程中在其它会话查看日志信息,发现outer过程中的正常写日志操作(p_test_outer运行信息)被提交了,由此进一步验证了上述推论,即:MySQL在嵌套调用存储过程时,会强制提交当前事务,上述推论是否合理,烦请指点
BEGIN

#Routine body goes here...
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS @cno = NUMBER;
        GET DIAGNOSTICS CONDITION @cno
            @result = RETURNED_SQLSTATE, @resultinfo = MESSAGE_TEXT;
        ROLLBACK;
        INSERT INTO business_log
            SELECT NULL,
                         NULL,
                         CONCAT('p_test_outer抛出异常:', @result, '-', @resultinfo),
                         CURRENT_TIMESTAMP(6);

– COMMIT;

        SET result = @result;
        SET resultinfo = @resultinfo;

– SET autocommit = 1; ##开启自动提交
END;

    SET autocommit = 0; ##关闭自动提交

    START TRANSACTION;
    INSERT INTO business_log
        SELECT NULL,
                     NULL,
                     'p_test_outer运行信息',
                     CURRENT_TIMESTAMP(6);

    CALL p_bcp_scheduler_generate(@result,@resultinfo);##此处抛出异常
    COMMIT;

    SET autocommit = 1; ##开启自动提交

END

11个月前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!