MySQL Procedure Pattern for Nested Transaction
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 TRANSACTIONstatement 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
# 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
# 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
# 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) |
Print Friendly
