The MySQL dose not supports ‘Nested Transaction’.

Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. (https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html)

But I want to use ‘Stored Procedure’ what work well with a transaction independently and with nested calling.
This pattern isn’t correct nested transactions, but enables the use of nested procedures.

The basic idea is to use the @@ AUTOCOMMIT environment variable. The procedure will determine whether the transaction is started from the outside. If a transaction was started from the outside, the procedure return results only without a ROLLBACK/COMMIT.

Inner Procedure

DELIMITER $$

CREATE PROCEDURE `sp_inner`(
	IN `i_key` INT,
	IN `i_value` INT,
	OUT `o_return` INT
)
body:BEGIN

	DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT;			-- Initial value of @@AUTOCOMMIT
	DECLARE require_rollback CONDITION FOR SQLSTATE '45000';	-- Custom condition for rollback
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN				-- SQL EXCEPTION
		SET o_return = -999; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1062 BEGIN						-- Duplicate entry for key
		SET o_return = -901; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1216,1452 BEGIN					-- Foreign key constraint fails of childs
		SET o_return = -902; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1217,1451 BEGIN					-- Foreign key constraint fails of parents
		SET o_return = -903; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR require_rollback BEGIN					-- Custom errors
		IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	
	-- Start transaction
	IF (v_autocommit = 1) THEN
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
		SET SESSION autocommit = 0;
		START TRANSACTION;
	END IF;

	-- Queries
	INSERT INTO test(`key`, `value`) VALUES (i_key, i_value);
	IF ROW_COUNT() <> 1 THEN
		SET o_return = -101;					-- Set return code of custom error
		SIGNAL require_rollback;				-- Raise custom error
	END IF;
	
	-- Commit
	IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN
		COMMIT;
		SET SESSION autocommit = 1;
	END IF;

	SET o_return = 0;						-- Set return code of success
	
END$$

DELIMITER ;

Outer Procedure

DELIMITER $$

CREATE PROCEDURE `sp_outer`(
	IN `i_key` INT,
	IN `i_value` INT,
	OUT `o_return` INT
)
body:BEGIN


	DECLARE v_autocommit BIT DEFAULT @@AUTOCOMMIT;			-- Initial value of @@AUTOCOMMIT
	DECLARE require_rollback CONDITION FOR SQLSTATE '45000';	-- Custom condition for rollback
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN				-- SQL EXCEPTION
		SET o_return = -999; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1062 BEGIN						-- Duplicate entry for key
		SET o_return = -901; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1216,1452 BEGIN					-- Foreign key constraint fails of childs
		SET o_return = -902; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR 1217,1451 BEGIN					-- Foreign key constraint fails of parents
		SET o_return = -903; IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	DECLARE EXIT HANDLER FOR require_rollback BEGIN					-- Custom errors
		IF (v_autocommit=1) AND (@@AUTOCOMMIT=0) THEN ROLLBACK; SET SESSION autocommit = 1; END IF;
	END;
	
	-- Start transaction
	IF (v_autocommit = 1) THEN
		SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
		SET SESSION autocommit = 0;
		START TRANSACTION;
	END IF;

	-- Call child procedure
	CALL sp_inner(1, 100, @rtn);
	IF @rtn <> 0 THEN
		SET o_return = -101;					-- Set return code of child error
		SIGNAL require_rollback;				-- Raise custom error
	END IF;	

	-- Queries
	INSERT INTO test(`key`, `value`) VALUES (i_key, i_value);
	IF ROW_COUNT() <> 1 THEN
		SET o_return = -201;					-- Set return code of custom error
		SIGNAL require_rollback;				-- Raise custom error
	END IF;
	
	-- Commit
	IF (v_autocommit = 1) AND (@@AUTOCOMMIT = 0) THEN
		COMMIT;
		SET SESSION autocommit = 1;
	END IF;

	SET o_return = 0;						-- Set return code of success
	
END$$

DELIMITER ;

Testing #1 : Call inner procedure individually

# Create InnoDB Table
mysql> CREATE TABLE `test` (`key` INT(10) NOT NULL, `value` INT(10) NOT NULL, PRIMARY KEY (`key`)) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

# Check AUTOCOMMIT environment
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

# Call sp_inner
mysql> CALL `sp_inner`(1, 100, @rtn); SELECT @rtn;
Query OK, 0 rows affected (0.01 sec)

+------+
| @rtn |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

# Check rows of table
mysql> SELECT * FROM `test`;
+-----+-------+
| key | value |
+-----+-------+
|   1 |   100 |
+-----+-------+
1 row in set (0.00 sec)

# Check AUTOCOMMIT environment
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Testing #2 : Call outer procedure with no error

# Truncate Table
mysql> TRUNCATE TABLE `test`;
Query OK, 0 rows affected (0.00 sec)

# Check AUTOCOMMIT environment
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

# Call sp_outer with different data of sp_inner
mysql> CALL `sp_outer`(2, 200, @rtn); SELECT @rtn;
Query OK, 0 rows affected (0.01 sec)

+------+
| @rtn |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

# Check rows of table
mysql> SELECT * FROM `test`;
+-----+-------+
| key | value |
+-----+-------+
|   1 |   100 |
|   2 |   200 |
+-----+-------+
1 row in set (0.00 sec)

# Check AUTOCOMMIT environment
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Testing #3 : Call outer procedure with a duplicate key error

# Truncate Table
mysql> TRUNCATE TABLE `test`;
Query OK, 0 rows affected (0.00 sec)

# Check AUTOCOMMIT environment
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

# Call sp_outer with same data of sp_inner
mysql> CALL `sp_outer`(1, 100, @rtn); SELECT @rtn;
Query OK, 0 rows affected (0.00 sec)

+------+
| @rtn |
+------+
| -901 |
+------+
1 row in set (0.00 sec)

# Check rows of table
mysql> SELECT * FROM `test`;
Empty set (0.00 sec)

# Check AUTOCOMMIT environment
mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)