Are nested transactions allowed in MySQL?

Question

Does MySQL Allow Nested Transactions?

Answer 1

InnoDB supports SAVEPOINTS .

You can do the following:

 CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; START TRANSACTION; INSERT INTO t_test VALUES (1); SELECT * FROM t_test; id --- 1 SAVEPOINT tran2; INSERT INTO t_test VALUES (2); SELECT * FROM t_test; id --- 1 2 ROLLBACK TO tran2; SELECT * FROM t_test; id --- 1 ROLLBACK; SELECT * FROM t_test; id --- 

Answer 2

From the MySQL documentation:

Transactions cannot be nested. This is a consequence of an implicit commit made for any current transaction when issuing a START TRANSACTION instruction or one of its synonyms. https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

Answer 3

If you use php, then you may be interested in https://github.com/Enelar/phpsql It supports mysql and pgsql and extends to other connectors.

 function TransferMoney() { // Nested transaction code could not even know that he nested $trans3 = db::Begin(); if (!db::Query("--Withdraw money from user", [$uid, $amount], true)) return $trans3->Rollback(); db::Query("--Deposit money to system"); return $trans3->Commit(); } $trans = db::Begin(); db::Query("--Give item to user inventory"); $trans2 = $trans->Begin(); $trans2->Query("--Try some actions and then decide to rollback"); $trans2->Rollback(); // Commit or rollback depending on money transfer result return $trans->Finish(TransferMoney());