PDO::commit() success or failure

Question

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?

Answer 1

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.

Answer 2

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();
}