Assume I have a procedure called prodInner that has an error handler like so

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        SELECT @p1 as RETURNED_SQLSTATE, @p2 as MESSAGE_TEXT;
        ROLLBACK;
    END;

and an outer prod called prodOuter with the same error handler. Say there's an issue when running the inner handler and the sqlexception catches it. Will the outer procedure also "fail" and rollback any and all changes (in addition to changes made in other procedure calls)?

Edit

    DROP PROCEDURE TEST_INNER;
CREATE PROCEDURE TEST_INNER()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            ROLLBACK;
            RESIGNAL;
        END;

    START TRANSACTION;
        INSERT INTO TEST_TABLE VALUES (1,0);
        INSERT INTO TEST_TABLE VALUES(1); # throws an error because two values are required
    COMMIT;
end;

CREATE PROCEDURE TEST_OUTER()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            ROLLBACK;
            RESIGNAL;
        END;

    START TRANSACTION;
        INSERT INTO TEST_TABLE VALUES (-1,0);
        CALL TEST_INNER();
    COMMIT;
end;

CALL TEST_OUTER()
CALL TEST_INNER()

Edit 2, is this the best solution?

CodePudding user response：

I think I've figured out the issue but my understanding is very minimal. When the outer procedure is called, a transaction is created. The commit in the inner-procedure however will commit any and all changes in the inner procedure even if the changes in the inner procedure gets rolled back.

CodePudding user response：

Statements That Cause an Implicit Commit

Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.

You may investigate this in details: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=76bc26fa4e96b5ea3643aabe8161feea