Mysql transactions within transactions

Solution 1:

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:

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.

Solution 3:

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

No.