MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEST_TABLE (column_1 INT, column_2 INT);
INSERT INTO TEST_TABLE VALUES (123,456);
CREATE PROCEDURE TEST_INNER()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
SELECT *, 'inner rollback' FROM TEST_TABLE;
            ROLLBACK;
            RESIGNAL;
        END;

    START TRANSACTION;
SELECT *, 'inner starts' FROM TEST_TABLE;
        INSERT INTO TEST_TABLE VALUES (1,0);
SELECT *, 'inner insert 1' FROM TEST_TABLE;
        INSERT INTO TEST_TABLE VALUES(1); # throws an error because two values are required
SELECT *, 'inner complete' FROM TEST_TABLE;
    COMMIT;
end;
CREATE PROCEDURE TEST_OUTER()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
SELECT *, 'outer rollback' FROM TEST_TABLE;
            ROLLBACK;
        END;

    START TRANSACTION;
SELECT *, 'outer starts' FROM TEST_TABLE;
        INSERT INTO TEST_TABLE VALUES (-1,0);
SELECT *, 'call inner' FROM TEST_TABLE;
        CALL TEST_INNER();
SELECT *, 'outer complete' FROM TEST_TABLE;
    COMMIT;
end;
CALL TEST_INNER()


column_1
column_2
inner starts


123
456
inner starts




column_1
column_2
inner insert 1


123
456
inner insert 1

1
0
inner insert 1




column_1
column_2
inner rollback


123
456
inner rollback

1
0
inner rollback



Column count doesn't match value count at row 1


SELECT * FROM TEST_TABLE


column_1
column_2


123
456


CALL TEST_OUTER()


column_1
column_2
outer starts


123
456
outer starts




column_1
column_2
call inner


123
456
call inner

-1
0
call inner




column_1
column_2
inner starts


123
456
inner starts

-1
0
inner starts




column_1
column_2
inner insert 1


123
456
inner insert 1

-1
0
inner insert 1

1
0
inner insert 1




column_1
column_2
inner rollback


123
456
inner rollback

-1
0
inner rollback

1
0
inner rollback




column_1
column_2
outer rollback


123
456
outer rollback

-1
0
outer rollback


SELECT * FROM TEST_TABLE


column_1
column_2


123
456

-1
0