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.
[cc lang=”php”]
For Deducting
Update account set amount=amount-500 WHERE
For adding to another account
Update account set amount=amount+500 WHERE
[/cc]
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
[cc lang=”php”]
// Begin transaction
If(query 1 success)
{
If(query2 success)
{
// Commit the transaction
}
else
{
// Roll back the transaction
}
}
else
{
// Rollback the transaction
}
[/cc]
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.
[cc lang=”php”]
beginTransaction();
?>
[/cc]
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.
[cc lang=”php”]
beginTransaction();
/* Change the database schema */
$sth = $dbh->exec(“DROP TABLE fruit”);
/* Commit the changes */
$dbh->commit();
/* Database connection is now back in autocommit mode */
?>
[/cc]
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.
[cc lang=”php”]
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 */
?>
[/cc]
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?
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;
[cc lang=”php”]
$dbh->beginTransaction();
try {
$dbh->exec(“UPDATE dessert SET name = ‘donut”);
$dbh->commit();
} catch(Exception $ex) {
//something bad happened…
$dbh->rollBack();
}
[/cc]
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.