Mysql transactions within transactions

Php Problem Overview

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

Php Solutions

Solution 1 - Php

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
}

Solution 2 - Php

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.

Solution 3 - Php

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

No.

Solution 4 - Php

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.

Solution 5 - Php

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!

Solution 6 - Php

You might want to check your testing methadology. Outside of http://dev.mysql.com/tech-resources/articles/maxdb-transactions.html">MaxDB</a>;, MySQL doesn't support anything remotely like nested transactions.

Solution 7 - Php

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

Content Type	Original Author	Original Content on Stackoverflow
Question	Brian	View Question on Stackoverflow
Solution 1 - Php	Buttle Butkus	View Answer on Stackoverflow
Solution 2 - Php	Pascal MARTIN	View Answer on Stackoverflow
Solution 3 - Php	Vladislav Rastrusny	View Answer on Stackoverflow
Solution 4 - Php	troelskn	View Answer on Stackoverflow
Solution 5 - Php	pfuri	View Answer on Stackoverflow
Solution 6 - Php	Alan Storm	View Answer on Stackoverflow
Solution 7 - Php	K. Norbert	View Answer on Stackoverflow