Are nested transactions allowed in MySQL?

Question

Does MySQL allow the use of nested transactions?

Maigre

mysql does not support nested transactions – Sane

Answer 1

No, but

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
---

Infant

this was not the question, "savepoints" are one thing "nested transaction" support was the real question. See this link – Titrant

@arod: could you please explain the difference in a single-threaded context? Thanks! – Infant

@Infant I believe the commands issued to the DB differ, don't they? I might be mistaken – Titrant

@arod: the link you gave does not mention any commands. Savepoints are a way to implement nested transactions in single-threaded context. – Infant

@Infant the link I provided was to show that nested transactions are now supported. Now, savepoints are powerful, but they're not the same as nested BEGIN,COMMIT/ROLLBACK (programatically speaking, command-wise) although you seem pretty sure that in a "single-threaded context" they're equivalent. I intended to address the question directly (3 years later :) ... – Titrant

@arod: you know that the link you provided has nothing to do with MySQL, don't you? – Infant

@Titrant that link documents nested transaction support for berkely db. AFAIK the only difference between the two is ROLLBACK TO does not release the row locks. – Nevertheless

@Titrant This answer is good, the only thing it's missing is a "No, but...", if you really want it to satisfy the question completely. – Maggi

@Infant is right. It's for "Berkely DB" as Steve adds. #brainfart? – Titrant

@sasino: makes sense, added – Infant

@Titrant Savepoints are exactly what allows for 'nested transactions'. What is a 'nested' set of instructions, if not a call stack where the operations done in any frame can be canceled and sent back to the last frame? Here's my example using PHP. It's quite simple in code to implement nested or recursive transactions using savepoints. https://mcmap.net/q/57784/-mysql-transactions-within-transactions – Einkorn

Answer 2

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

Gonidium