title: rollback if one of multple inserts fails using prepared statements
body: <p>I have a form script that inserts data in multiple tables. But if one fails it has to revert everything back. I've been looking for this but I didn't find a question that uses multiple (mysqli) prepared statements and rollback.</p>
  
  <pre><code>$mysqli = new mysqli(/* connection details */);
  $mysqli-&gt;autocommit(FALSE);
  
  // rollback should revert here
  $mysqli-&gt;begin_transaction();
  
  if ($stmt_one = $mysqli-&gt;prepare('INSERT INTO main_table (one, two, three) VALUES (?, ?, ?)')) {
      $stmt_one-&gt;bind_param('sss', $one, $two, $three);
      $stmt_one-&gt;execute();
  
      $id = (int) $mysqli-&gt;insert_id;
  
      if ($stmt_two = $mysqli-&gt;prepare('INSERT INTO sub_table_one (id, four) VALUES (?, ?)')) {
          $stmt_two-&gt;bind_param('is', $id, $four);
          $stmt_two-&gt;execute();
      }
  
      if ($stmt_three = $mysqli-&gt;prepare('INSERT INTO sub_table_two (id, five) VALUES (?, ?)')) {
          $stmt_three-&gt;bind_param('is', $id, $five);
          $stmt_three-&gt;execute();
      }
  }
  
  if ($mysqli-&gt;commit()) {
      // everything ok
      header('Location: /');
  } else {
      // something went wrong, we have to rollback
      $mysqli-&gt;rollback();
      // and display the error message
      echo $stmt_one-&gt;error;
      echo $stmt_two-&gt;error;
      echo $stmt_three-&gt;error;
  }
  </code></pre>
  
  <p>I'm not sure about this, can I do something like that? Or do I have to check every <code>$stmt_*</code> for errors?</p>
  
  <p><hr>
  <strong>UPDATE</strong></p>
  
  <p><strong>(The problem was that I was using DB Engine MyISAM instead of InnoDB)</strong></p>
  
  <p>I've upgraded to PHP 7 and now rollback is not working. I've tried the following:</p>
  
  <pre><code>if (!$mysqli-&gt;rollback()) $log .= 'no rollback :(';
  </code></pre>
  
  <p>But I don't see that message in my log...</p>
  
  <p>My current code looks like:</p>
  
  <pre><code>public function Upload() {
  
      try {
  
          // rollback should revert here
          $mysqli-&gt;begin_transaction();
  
          // multiple prepared statements
  
          if ($mysqli-&gt;commit()) {
              $exit = $log;
          } else {
              throw new Exception('Transaction commit failed. Property ID: ' . $this-&gt;id);
          }
  
      } catch (Exception $e) {
  
          try {
  
              $test = $this-&gt;owner['id'] ? 'property' : ($this-&gt;applicant ? 'demand' : 'Fatal Error: PropertyFromInput() contact error (no owner, no applicant)');
  
              $log = 'Rolling back new ' . $test . ' upload' . "\n";
  
              if (!$mysqli-&gt;rollback()) $log .= 'no rollback...' . "\n";
  
              if ($test == 'property') $log .= $this-&gt;cleanup_prop() ? 'property successfully cleaned up' . "\n" : 'error while cleaning up property' . "\n";
  
              $err_msg = $e-&gt;getMessage();
  
          } catch (Exception $f) {
  
              $err_msg .= $f-&gt;getMessage();
  
          }
  
          $usr_msg = $upload_err ? $err_msg : 'Se ha producido un error. Por favor contacte con un administrador.';
  
          $log .= 'User triggered an error while uploading a new ' . $test . ".\n" . 'Error message: ' . $err_msg;
  
          $exit = array($log, $usr_msg);
  
      }
  
      $mysqli-&gt;autocommit(TRUE);
  
      return $exit;
  }
  </code></pre>
  
  <p>My log looks like:</p>
  
  <pre><code>Logging new property upload...
  
  Rolling back new property upload
  property successfully cleaned up
  User triggered an error while uploading a new property.
  Error message: Upload Error: $thumbnailsPath is false and/or not a dir: ". Property ID: 200
  </code></pre>
  
  <p>That means <code>rollback()</code> was executed/ didn't return false but the tables were inserted anyways...</p>
  
  <p>Now about the new php features since 5.4:</p>
  
  <ul>
  <li>5.5 added the finally block, do I just use it for setting autocommit to true?</li>
  <li>I read php 7 improved the errors handling but I really have no idea how to take advantage of this.</li>
  </ul>
  
  <p>I need this fixed because I wouldn't like my production db ending full of broken/unused data, who would?</p>