PDO :: commit () success or failure

Question

The PHP documentation PDO :: commit () indicates that the method returns TRUE on success or FALSE on failure. Does this relate to the success or failure of statements 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 fails, will the commit () method return false due to "all or nothing" atomic transactions?

Answer 1

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

Completed requests within the transaction itself will be successful or unsuccessful. Using the example of Mr.Tk, the transaction will be committed, if possible, and when executing queries in the "try" block, and throw an error if an error occurred in the "try" block.

When only evaluating the executed requests in the "try" block, I personally will try to catch a PDOException instead of the usual Exception.

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

Answer 2

The key part is to set PDO in exception mode , while trying to catch only try to roll back is not required. So your code is fine, you don’t need to change it if all you need is a rollback on failure, if you have this line somewhere:

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

If the script fails, the connection is closed and mysql will be happy to cancel the transaction for you.

If you still want a manual rollback, you have to do it right, not like the other answers say. Make sure that

• you are catching an Exception , not a PDOException , since it doesn't matter which particular exception aborts the execution
• you throw an exception after rolling back to receive a notification about the problem
• also that the table engine supports transactions (that is, for Mysql it should be InnoDB, not MyISAM).

This checklist is taken from my article , which may be useful in this or many other aspects.

Answer 3

I always did it like this:

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

And it always worked like a charm! :)

So, I think that in your case, if the insertion failed, the application should throw an exception, and commit will not even run.