Mysql transactions within transactions

Question

In a PHP script working with a mysql database, I recently had the need to use a transaction at a point that happened to be inside another transaction. All my tests seem to indicate this is working out fine, but I can't find any documentation on this usage.

I want to be sure - are transactions within transactions valid in mysql? If so, is there a way to find out how many levels deep you are in nested transactions? (ie. how many rollbacks it would take to return to normal)

Thanks in advance, Brian

Buttle Butkus · Answer 1 · 2022-08-03T06:21:03.587

74

Contrary to everyone else's answer, you can effectively create transactions within transactions and it's really easy. You just create SAVEPOINT locations and use ROLLBACK TO savepoint to rollback part of the transaction, where savepoint is whatever name you give the savepoint. Link to MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/savepoint.html And of course, none of the queries anywhere in the transaction should be of the type that implicitly commit, or the whole transaction will be committed.

Examples:

START TRANSACTION;

# queries that don't implicitly commit

SAVEPOINT savepoint1;

# queries that don't implicitly commit

# now you can either ROLLBACK TO savepoint1, or just ROLLBACK to reverse the entire transaction.

SAVEPOINT savepoint2;

# queries that don't implicitly commit

# now you can ROLLBACK TO savepoint1 OR savepoint2, or ROLLBACK all the way.
# e.g.

ROLLBACK TO savepoint1;
COMMIT; # results in committing only the part of the transaction up to savepoint1

In PHP I have written code like this, and it works perfectly:

foreach($some_data as $key => $sub_array) {
  $result = mysql_query('START TRANSACTION'); // note mysql_query is deprecated in favor of PDO
  $rollback_all = false; // set to true to undo whole transaction
  for($i=0;$i<sizeof($sub_array);$i++) {
    if($sub_array['set_save'] === true) {
      $savepoint = 'savepoint' . $i;
      $result = mysql_query("SAVEPOINT $savepoint");
    }
    $sql = 'UPDATE `my_table` SET `x` = `y` WHERE `z` < `n`'; // some query/queries
    $result = mysql_query($sql); // run the update query/queries

    $more_sql = 'SELECT `x` FROM `my_table`'; // get data for checking
    $result = mysql_query($more_sql);
    
    $rollback_to_save = false; // set to true to undo to last savepoint
    while($row = mysql_fetch_array($result)) {
      // run some checks on the data
      if( /*some check says to go back to savepoint */) {
        $rollback_to_save = true; // or just do the rollback here.
      }
      if( /* some check says to rollback entire transaction */ ) {
        $rollback_all = true;
      }
    }
    if($rollback_all === true) {
      mysql_query('ROLLBACK'); // rollback entire transaction
      break; // break out of for loop, into next foreach
    }
    if($rollback_to_save = true) {
      mysql_query("ROLLBACK TO $savepoint"); // undo just this part of for loop
    }
  } // end of for loop
  mysql_query('COMMIT'); // if you don't do this, the whole transaction will rollback
}

edited Aug 03 '22 at 06:21

answered Aug 13 '12 at 20:04

Buttle Butkus

9,206
13
79
120

7

Didn't test it, but I felt the need to comment on how beautiful this is. – Marco Aurélio Deleu Oct 17 '13 at 20:11
2

@MarcoAurélioDeleu thank you! I don't think I've ever received such a compliment for any of my code. It works perfectly in my application, and it seems a few other people have gotten it to work too, judging by the upvotes. – Buttle Butkus Oct 19 '13 at 01:58
That's right, one can set savepoint and rollback to this savepoint inside one transaction. But the point here is that nesting of transactions is not possible because starting inner transaction will implicitly commit previous outer transaction statements and that's probably something that you do not want. This can be problematic: for example if one has class House and another class Room each having its own transaction for DB create statement; here creating Room object inside House object will break outer House's transaction!!! – sbrbot Jul 19 '16 at 00:31
@sbrbot "A rose by any other name would smell as sweet." You nest `START TRANSACTION` statements, but the `SAVEPOINT` method I crudely outlined in my answer achieves exactly what a nested transaction would. Suppose you have an array of `room` objects you want to add to a `house` object. You could actually use database constraints to decide when the house is "full". You can try adding each room, and get back a `house full` exception once it's full, which can coincide with the database model spitting back an error and rolling back to "savepointX". "Could not add 5th bathroom. Full House." – Buttle Butkus Jul 19 '16 at 00:54
@sbrbot Moreover, since you can make as many SAVEPOINTs as you need, you can nest as deeply as you need. E.g. rooms in a wing of a house, wings in a house, houses in a block. You can roll back to the last room, the last wing, or the last house. Suppose you're adding your 10th house of the block, and you add several rooms but once you add a bathroom you get an error "too much water demand for one block, house canceled." You would `ROLLBACK TO house9complete` and then start the next block. You can have multiple `ROLLBACK` events in a single run through the program. And in fact, that is what I do – Buttle Butkus Jul 19 '16 at 00:59
Sometimes I want to create House with all Rooms in it within one single transaction (transaction started inside House class method create). Sometimes I want to create Room only (Room class has its own transaction started in create method). So if I start House transaction and intend to create Rooms in it, Room's start of its (inner) transaction will implicitly commit outer House transaction). Room knows nothing about it is created inside an outer transaction in order to set savepoint. – sbrbot Jul 19 '16 at 01:03
@sbrbot There are several ways to solve that. One way is to not have DB queries inside of each class. Whenever any object uses its `save()` method, it would be saved through a `db` object that would have a `transactionLevel` property. That way, any object saved could create a roll back-able savepoint. The `db` object takes care of all the savepoint names and nesting. If no transactions are started, it issues `START TRANSACTION`, otherwise it creates a `SAVEPOINT`. There are other solutions as well. But using the `SAVEPOINT` in any solution will allow for much better DB consistency. – Buttle Butkus Jul 19 '16 at 01:11
1

I do have a db object (using shared DB connection) and used by all my classes (objects). Actually it's an abstract class with db as wrapper for all my classes. I got the idea from your comment with _transactionalLevel_ property - should db start a transaction or not (yes by default). When I create Room inside a House, I should set this property and tell DB not to start transaction since it has been started in outer class. – sbrbot Jul 19 '16 at 01:36
The problem with savepoints is that you have to manage them at client side (i.e. track if transaction is active, track savepoint names, etc.). This is different from nested transactions, which are managed from server side. – AnrDaemon Sep 23 '22 at 12:45
@AnrDaemon You mean client code outside of MySQL? I implemented it in PHP, but it seems like you could also implement it in a stored procedure. No need to track global savepoint names. "A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored." https://dev.mysql.com/doc/refman/8.0/en/savepoint.html – Buttle Butkus Sep 23 '22 at 21:23

score 61 · Accepted Answer · edited Aug 16 '18 at 23:45

61

This page of the manual might interest you : 12.3.3. Statements That Cause an Implicit Commit; quoting a few sentences :

The statements listed in this section (and any synonyms for them) implicitly end a transaction, as if you had done a COMMIT before executing the statement.

And, a bit farther in the page :

Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.

See also this paragraph :

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.

edited Aug 16 '18 at 23:45

halfer

19,824
17
99
186

answered Sep 29 '09 at 06:35

Pascal MARTIN

395,085
80
655
663

3

`Transactions cannot be nested` in the same connection, or also in differents? – Rafael Barros Jul 02 '14 at 18:59
5

General logic says that different connections know nothing about each other. So, transaction in one connection doesn't know about another transaction in different connection. Not tested, but should be true. – Alex Lomakin Mar 07 '15 at 11:47

score 14 · Answer 3 · answered Sep 29 '09 at 06:41

14

I want to be sure - are transactions within transactions valid in mysql?

No.

answered Sep 29 '09 at 06:41

Vladislav Rastrusny

29,378
23
95
156

score 11 · Answer 4 · answered Sep 29 '09 at 06:46

MySql doesn't support nested transactions. There are a few ways that you can emulate it though. First, you can use savepoints as a form of transaction, so that gives you two levels of transactions; I've used this for testing, but I'm not sure about the limitations, if you use it in production code. A simpler solution is to ignore the second begin transaction and instead increase a counter. For each commit, you decrease it. Once you hit zero, you do an actual commit. There are obvious limitations of this; Eg. a rollback will roll all transactions back, but for a case where you only use transactions for error-handling, that may be acceptable.

score 8 · Answer 5 · answered Oct 15 '13 at 21:38

There are some great answers in this thread, however, if you use innoDB as your MySQL storage engine and are using MySQL 5.0.3 or higher, you get nested transactions right out of the box without the need for any extra work on your part or any of the fancy techniques described by others in this thread.

From the MySQL docs on XA Transactions:

MySQL 5.0.3 and up provides server-side support for XA transactions. Currently, this support is available for the InnoDB storage engine. The MySQL XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at http://www.opengroup.org/public/pubs/catalog/c193.htm. Limitations of the current XA implementation are described in Section E.5, “Restrictions on XA Transactions”.

My XA Transaction Example Just For You:

# Start a new XA transaction
XA START;

    # update my bank account balance, they will never know!
    UPDATE `bank_accounts` SET `balance` = 100000 WHERE `id` = 'mine';

    # $100,000.00 is a bit low, I'm going to consider adding more, but I'm not sure so 
    # I will start a NESTED transaction and debate it...
    XA START;

        # max int money! woo hoo!
        UPDATE `bank_accounts` SET `balance` = 2147483647 WHERE `id` = 'mine';

    # maybe thats too conspicuous, better roll back
    XA ROLLBACK;


# The $100,000 UPDATE still applies here, but the max int money does not, going for it!
XA COMMIT;

# Oh No!  Sirens!  It's the popo's!!! run!!
# What the hell are they using ints for money columns anyway!  Ahhhh!

MySQL Documentation For XA Transactions:

I <3 XA Transactions 4 Eva!

Tried on MySQL 5.6. The "XA" commands require a string xid, and trying to start a nested one gives this: "ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state" — HectorJ, Nov 09 '15 at 09:51
I just ran into the same issue as @HectorJ. I wish this worked, but it does not. — Ryan Ewen, May 13 '21 at 20:25
@RyanEwen Anything wrong with my `SAVEPOINT` solution? Works perfectly for me. — Buttle Butkus, Mar 10 '22 at 02:36
@ButtleButkus Yes unfortunately for me it wasn't a proper solution. I can't quite remember why. I think it had to do with nested functions and/or queries that implicitly committed. I ended up realizing that I shouldn't use transactions in my applications functions as they can get nested, and rather only use transactions at the highest level of code where I can wrap the entire process in a transaction without any conflict — Ryan Ewen, Mar 11 '22 at 17:01
@RyanEwen it would work if your application keeps track of transaction nesting level to determine whether to issue a ‘START TRANSACTION’ or a ‘SAVEPOINT savepoint-n’. Of course, you can’t use DDL statements like add columns, tables, etc during a transaction. You can sometimes use temporary tables, though. In my case, I create a temporary table before starting the transaction. https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html — Buttle Butkus, Mar 11 '22 at 23:22

score 1 · Answer 6 · answered Sep 29 '09 at 06:41

1

You might want to check your testing methadology. Outside of MaxDB, MySQL doesn't support anything remotely like nested transactions.

answered Sep 29 '09 at 06:41

Alana Storm

164,128
91
395
599

3

"MySQL doesn't support anything remotely like nested transactions." Wouldn't you say that savepoints are anything like nested transactions? – Buttle Butkus Feb 10 '13 at 01:54

score -1 · Answer 7 · answered Dec 10 '09 at 16:02

-1

It does: http://dev.mysql.com/doc/refman/5.0/en/xa.html

answered Dec 10 '09 at 16:02

K. Norbert

10,494
5
49
48

2

are these really nested transactions? got some samples? – William Denniss Nov 20 '10 at 10:15

Mysql transactions within transactions

7 Answers7

Linked

Related