Managing Transaction in PDO

Transaction is very useful portion of the database operation when we are making number of queries and all the queries are depends on each other. Transaction works on this method “All or Nothing”.

For example if I fire two queries, one for deducting some amount from one account and another query to deposit that amount to another account. So both my query will look like below.

For Deducting Update account set amount=amount-500 WHERE For adding to another account Update account set amount=amount+500 WHERE
For Deducting
  Update account set amount=amount-500 WHERE
For adding to another account
  Update account set amount=amount+500 WHERE

If both of the above queries run properly then it will transfer some amount from account 1 to account 2.

But what if first query runs properly and some error occurred in second query. In this case transaction comes into the play.

In transaction query result is not actually applied to database. Results only apply if we commit the transaction. And if any error occurs then we can rollback the transaction to the previous state. Check below demo code to be clearer:

Flow of Using Transaction for DB operations

// Begin transaction If(query 1 success) { If(query2 success) { // Commit the transaction } else { // Roll back the transaction } } else { // Rollback the transaction }
// Begin transaction
If(query 1 success)
{
   If(query2 success)
   {
      // Commit the transaction
   }
   else
   {
      // Roll back the transaction
   }
}
else
{
   // Rollback the transaction
}

Now PHP have a nice extension called PDO. In which we can easily manage the transaction.

PDO::beginTransaction() – initiate the transaction

PDO::beginTransaction() used to begin the transaction. This function will return true on success and false on error. By calling this function autocommit mode will gets turned off. And any changed made to DB via PDO object will be committed only by calling PDO::commit(). Calling PDO::rollback() will rollback all change made to database after calling beginTransaction statement and return the connection to autocommit mode.

beginTransaction(); ?>
<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();
?>

PDO::commit – commit the transaction

This will return true on success and false on error. Basically this will commit a transaction and move the connection to autocommit mode until the next beginTransaction call.

beginTransaction(); /* Change the database schema */ $sth = $dbh->exec("DROP TABLE fruit"); /* Commit the changes */ $dbh->commit(); /* Database connection is now back in autocommit mode */ ?>
<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();
/* Change the database schema */
$sth = $dbh->exec("DROP TABLE fruit");
/* Commit the changes */
$dbh->commit();
/* Database connection is now back in autocommit mode */
?>

PDO::rollBack – Roll back a Transaction

This function will rollback any changes made to database after initiating the transaction by beginTransaction. This also return true on success and false on error.

beginTransaction(); /* Change the database schema and data */ $sth = $dbh->exec("DROP TABLE fruit"); $sth = $dbh->exec("UPDATE dessert SET name = 'hamburger'"); /* Recognize mistake and roll back changes */ $dbh->rollBack(); /* Database connection is now back in autocommit mode */ ?>
<?php
/* Begin a transaction, turning off autocommit */
$dbh->beginTransaction();
/* Change the database schema and data */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert SET name = 'hamburger'");
/* Recognize mistake and roll back changes */
$dbh->rollBack();
/* Database connection is now back in autocommit mode */
?>

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: