CODEWITHSUNDEEP
phpmysqlpdotransactions
Ben Guest
Ben Guest

Reputation: 1568

PDO::commit() success or failure

The PHP PDO::commit() documentation states that the method returns TRUE on success or FALSE on failure. Does this refer to the success or failure of the statement executions between beginTransaction() and commit()?

For example, from the documentation:

$dbh->beginTransaction();
$sql = 'INSERT INTO fruit (name, colour, calories) VALUES (?, ?, ?)';
$sth = $dbh->prepare($sql);

foreach ($fruits as $fruit) {
    $sth->execute([
        $fruit->name,
        $fruit->colour,
        $fruit->calories,
    ]);
}

$dbh->commit();

If any of the above executions fail, will the commit() method return false due to the "all-or-nothing basis" of atomic transactions?

Upvotes: 9

Views: 11263

Answers (3)

DigiLive
DigiLive

Reputation: 1103

The return value is based on pdo::commit itself, not the transaction you're trying to commit. It returns FALSE when there's no transaction active, but it's not very clear whenever it should return TRUE or FALSE.

The executed queries within the transaction itself will success or fail on it's own. Using the Mr.Tk's example, the transaction will be committed if possible and no error occured while executing the queries in the "try" block and rolled back if an error did occur within the "try" block.

When only evaluating the executed queries within the "try" block, personally I would try to catch a PDOException instead of a normal Exception.

$dbh->beginTransaction();
try {
    // insert/update query
    $dbh->commit();
} catch (PDOException $e) {
    $dbh->rollBack();
}

Upvotes: 7

Your Common Sense
Your Common Sense

Reputation: 157981

The key part is to set PDO in exception mode, while having try-catch only to do a rollback is unnecessary. Thus, your code is all right, no need to change it if all you want is rollback on failure, as long as you have this line somewhere:

$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

In case of failure the script will be terminated, connection closed and mysql will be happy to roll back the transaction for you.

In case you still want to rollback manually, you should be doing it properly, not like it is said in the other answers. Make sure that

  • you are catching Exception, not PDOException, as it doesn't matter what particular exception aborted the execution
  • you are re-throwing an exception after rollback, to be notified of the problem
  • also that a table engine supports transactions (i.e. for Mysql it should be InnoDB, not MyISAM).

This checklist is taken from my article which you may find useful in this or many other aspects as well.

Upvotes: 7

Mr.TK
Mr.TK

Reputation: 1836

I've always done it like that:

$dbh->beginTransaction();

try {
     // insert/update query

     $dbh->commit();
} catch (Exception $e) {
     $dbh->rollBack();
}

And always worked as charm! :)

So I think that in Your case if insert would fail application should throw an exception and commit wouldn't even trigger.

Upvotes: 0

Related Questions