Are nested transactions allowed in MySQL?
InnoDB
supports SAVEPOINTS
.
You can do the following:
CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;START TRANSACTION;INSERTINTO t_testVALUES (1);SELECT *FROM t_test; id--- 1SAVEPOINT tran2;INSERTINTO t_testVALUES (2);SELECT *FROM t_test; id--- 1 2ROLLBACK TO tran2;SELECT *FROM t_test; id--- 1ROLLBACK;SELECT *FROM t_test; id---
From MySQL documentation:
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