110

Does MySQL allow the use of nested transactions?

random
  • 9,774
  • 10
  • 66
  • 83
Alix Axel
  • 151,645
  • 95
  • 393
  • 500

2 Answers2

94

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
---
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 34
    this was not the question, "savepoints" are one thing "nested transaction" support was the real question. See [this link](http://docs.oracle.com/cd/E17076_02/html/gsg_txn/C/nestedtxn.html) – arod Sep 02 '12 at 20:41
  • 2
    @arod: could you please explain the difference in a single-threaded context? Thanks! – Quassnoi Sep 02 '12 at 21:20
  • @Quassnoi I believe the commands issued to the DB differ, don't they? I might be mistaken – arod Sep 03 '12 at 02:03
  • @arod: the link you gave does not mention any commands. Savepoints are a way to implement nested transactions in single-threaded context. – Quassnoi Sep 03 '12 at 06:53
  • 2
    @Quassnoi 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 :) ... – arod Sep 04 '12 at 03:39
  • 18
    @arod: you know that the link you provided has nothing to do with MySQL, don't you? – Quassnoi Sep 04 '12 at 05:09
  • @arod 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. – Steve Buzonas Feb 08 '15 at 08:57
  • 2
    @arod This answer is good, the only thing it's missing is a "No, but...", if you really want it to satisfy the question completely. – Sasino Dec 27 '21 at 16:19
  • @Quassnoi is right. It's for "Berkely DB" as Steve adds. #brainfart? – arod Dec 28 '21 at 17:46
  • @sasino: makes sense, added – Quassnoi Dec 28 '21 at 19:48
  • @arod 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://stackoverflow.com/a/11941449/631764 – Buttle Butkus Aug 03 '22 at 06:22
60

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

bancer
  • 7,475
  • 7
  • 39
  • 58