Managing Transaction in PDO

  • Avinash
  • 2
  • Sep 03, 2010
  • Tutorial

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:

  • Manikandan

    HI,

    Is the transaction available as common for two different connections?

    Example:

    Am created one PDO connection with databaseA,and started transaction. Then am created another one PDO connection with databaseB to do some operations and commit the transaction. For me both the operations commits same time, eventhough i set autocommit TRUE for the second connection. How PDO transaction behave in above situation?

  • http://www.gopromotional.com Richard Larson

    I am curious as to why your examples are not using exceptions. I know that PDO exception error mode is optional, but wouldn’t it be better to use transactions in the scope of an exception? E.g;

    $dbh->beginTransaction(); try { $dbh->exec("UPDATE dessert SET name = 'donut"); $dbh->commit(); } catch(Exception $ex) { //something bad happened... $dbh->rollBack(); }
    $dbh->beginTransaction();
    try {
    $dbh->exec("UPDATE dessert SET name = 'donut");
    $dbh->commit();
    } catch(Exception $ex) {
    //something bad happened...
    $dbh->rollBack();
    }

    This way, you’re only every going to have to worry about doing a rollBack when you catch the exception, rather than relying on some if/else error handling logic such as in your example.