如果另一个代码块失败，则PDO回滚

Question

I'm trying to rollBack a PDO transaction based on the results of another block of code. This works great if the DB insert fails but if the "other stuff" throws an exception, it's not rolling back the commit. What am I doing wrong?

PHP version 5.4, database is MS SQL

<?php
    try {   
        $dbh = new PDO($dataSource);
        $dbh->beginTransaction();
        $sql = "INSERT INTO $table
                    (field1, field2, etc)
                VALUES
                    (:field1, :field2, :etc)"
        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(':field1', $data["field1"]);
        $stmt->bindParam(':field2', $data["field2"]);
        $stmt->bindParam(':etc', $data["etc"]);
        $stmt->execute();
        $dbh->commit();

        //do some other stuff which can throw an Exception
    } catch (Exception $e) {
        //make sure we have something to roll back
        try { $dbh->rollBack(); } catch (Exception $e2) {}
        $log->logFatal("Error: controller.inc.php: " . $e->getMessage());
    }   
?>

Answer 1

Calling commit() effectively completes the open transaction begun earlier by beginTransaction(), so following the call to commit() there is no database action remaining to rollBack().

If you have other code you want to run following a successful commit, you can check its return value in an if() block. PDO::commit() will return FALSE if the commit action itself fails, so you can prevent your mail action when it does by throwing another exception.

try {
  $dbh = new PDO($dataSource);
  $dbh->beginTransaction();
  // etc...
  // etc...

  // Attempt to commit, and do other actions if successful
  if ($pdo->commit()) {
    // Do other actions
    // mail, etc...
  }
  else {
    // Otherwise, throw another exception which your catch {} will handle
    throw new Exception('Transaction was not committed');
  }
} catch (Exception $e) {
  // Docs are unclear on whether rollBack() will throw an error on failure
  // or just return false. It is documented to throw an exception if 
  // no transaction is actually active.
  try { $pdo->rollBack(); } catch (Exception $e2) {}
    // Log your error, either a normal PDO error, or failed commit()...
    $log->logFatal("Error: controller.inc.php: " . $e->getMessage());
  }
}