MySQL Procedure Pattern for Nested Transaction

MySQL

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

Outer Procedure

Testing #1 : Call inner procedure individually

Testing #2 : Call outer procedure with no error

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

 


QR Code