Trouble using MySQL transactions with loops in PHP

Question

Hello all,

I am trying to set up a MySQL transaction such that I can loop through a bunch of queries (see below) and if any of them fail, rollback all of the changes. I am finding however that if one fails, not all of the queries are rolled back. Am I doing anything wrong here?

mysql_query("START TRANSACTION");

     foreach($array1 as $arr){

          // loop SQL query

          if(mysql_error()){
               $failed = "...";
          }
     }


     foreach($array2 as $arr){

          // loop SQL query

          if(mysql_error()){
               $failed = "...";
          }
     }

     if(isset($failed)){
          mysql_query("ROLLBACK");
     } else {
          mysql_query("COMMIT");
     }

Thanks!

Answer 1

Are you using a transaction-safe table type? The default MyISAM does not support transactions, I recommend using InnoDB.

Answer 2

The reason is that if a query fails (due to error), the transaction is automatically rolled back and terminated. So you should stop looping if a query fails, because anything executing after the failed query will autocommit (Or at least be in another transaction if AUTOCOMMIT is off)...