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)
2 thoughts on “MySQL Procedure Pattern for Nested Transaction”
Super, thanks