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.
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
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.
/* 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.
/* 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.
/* 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 */
?>
Get Connected:
One Comment
Join the conversation and post a comment.
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?