Transaction: commit() vs rollBack()

Question

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.

rollback() function
commit() function

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 */
?>

Imagine rollBack is the cancel button and commit is the save button — JimL, Jun 04 '16 at 17:44

score 0 · Answer 1 · answered Jun 04 '16 at 17:46

0

You use commit to perform the transaction, and rollback is the opposite, you use rollback when you want to keep all unchanged (for example if you have detected some error during some step of the transaction).

answered Jun 04 '16 at 17:46

Alejandro Winkler

51
4

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

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

edited May 23 '17 at 11:52

Community

1
1

answered Jun 04 '16 at 17:48

Rahul

76,197
13
71
125

What do you mean *"If it's successful and you want to persist the change"*? Still I don't know what's the difference .. – Shafizadeh Jun 04 '16 at 17:51
ok first of all please tell me should I use both of them in my script of one of them? – Shafizadeh Jun 04 '16 at 17:55
@Shafizadeh, both of them, perform the transaction ... catch error and if any error then rollback else commit. Try reading about transaction basics to have better understanding. – Rahul Jun 04 '16 at 17:56

Transaction: commit() vs rollBack()

2 Answers2