CODEWITHSUNDEEP
phpmysql
user3027531
user3027531

Reputation: 282

Transaction and rollback on this code

Following is my code in which I am making insertion in two different tables. My question is if due to bad server or bad connection or the like. The insertion to the first table being made but not to the second one. Then does the following code will rollback the insertion being done for the the first table and if not then how that can be done?

Note: My table type in Innodb

  $mysqli->autocommit(FALSE);
  $stmt_one = $mysqli->prepare("Insert into TABLE SET $var1 = ?, $var2 = ? ");
  $stmt_two = $mysqli->prepare("Insert into TABLE SET $var1 = ?, $var2 = ? ");
  $stmt_one->bind_param('is',$var1,$var2);
  $stmt_two->bind_param('is',$var1,$var2);
  $stmt_one->execute();
  $stmt_two->execute();
  $mysqli->commit();
  $stmt_one->close();
  $stmt_two->close();

Upvotes: 0

Views: 51

Answers (1)

voodoo417
voodoo417

Reputation: 12111

Look at:

try { 
  $mysqli->autocommit(FALSE);
  $stmt_one = $mysqli->prepare("Insert into TABLE SET $var1 = ?, $var2 = ? ");
  $stmt_two = $mysqli->prepare("Insert into TABLE SET $var1 = ?, $var2 = ? ");
  $stmt_one->bind_param('is',$var1,$var2);
  $stmt_two->bind_param('is',$var1,$var2);
  $stmt_one->execute();
  $stmt_two->execute();
  $mysqli->commit();
  $stmt_one->close();
  $stmt_two->close(); 
} catch (Exception $e) {
  $mysqli->rollBack();
  echo "Fallo: " . $e->getMessage();
}

For starting transaction you need turn off auto-committing DB modifications. You do it via $mysqli->autocommit(FALSE);. It means that all queries-changes DB must be "fixed" for "finish" transaction. Its doing via $mysqli->commit();. While you don't calling commit ("fix") - there will be no changes in DB.

In your case ( inserting into 2 tables ): if the insertion to the first table being made but not to the second , will be invoked exception with $mysqli->rollBack();. Since we have not caused сommit transaction (due to exception ), all changes will be roll back to start point (turning off auto-committing), in other words, we do rollback current transaction.

Upvotes: 1

Related Questions