Shafizadeh Shafizadeh - 5 months ago 9
PHP Question

Transaction: commit() vs rollBack()

I have some queries at one script and I want to execute either all of them or none of them ..! I've searched about that and I figured out I have to use transaction.

Actually I want to use PDO::beginTransaction. Now there is two approaches.



So what's the difference between them? Both of them seems identical to me, So when should I use which one?

<?php

$dbh->beginTransaction();

$sth1 = $dbh->exec("DROP TABLE fruit");
$sth2 = $dbh->exec("UPDATE dessert SET name = 'hamburger'");
$sth3 = $dbh->exec("INSERT INTO names(id, name) VALUES (NULL, 'peter')");

// which one?
$dbh->commit();
// or
$dbh->rollBack();
// ??

/* Database connection is now back in autocommit mode */
?>

Answer

Both of them seems identical to me

That's wrong. Transaction by definition is Atomic in nature means either it will happen and succeed executing all commands in the group or none at all. If it's successful and you want to persist the change then COMMIT else if any of the statement in the group fails then ROLLBACK to get back to pristine state.

So in your case, you would want to have all the below statement execute successfully and if that then COMMIT to persist the change but if any of the statement fails for any so called reason then it may end up giving a undesired result which you don't want to persist and so ROLLBACK and get back to previous consistent state.

$sth1 = $dbh->exec("DROP TABLE fruit");
$sth2 = $dbh->exec("UPDATE dessert SET name = 'hamburger'");
$sth3 = $dbh->exec("INSERT INTO names(id, name) VALUES (NULL, 'peter')");

Read about Transaction and also see this another post PHP + MySQL transactions examples

Comments