Saturday, March 17, 2012

How to use Mysql Transactions with PHP

Web applications are more popular today than ever with the increasing number of internet users. Most of the standard alone applications converted as web based applications or at least they try to provide a web interface for users. PHP and Mysql are two leading technologies which allow uses on rapid development of web based systems. "Transaction" is a powerful concept which comes with Mysql 4.0 and above versions. Lets explore that.

                                             

Web based applications have different types of requirements and some of them prefer more consistency on its data than others. As an example Banking software expect its data to be more secure and consistency. Assume a situation where one person (Bob) is transferring some amount of money (XXX) to his friends bank account (Alice) directly form account to account. The queries for the banking software database on this transaction will be as follows.

  1. Deduct XXX amount form Bobs account.
  2. Add XXX amount for Alices account.
If both queries executed successfully, it will be great. But assume a situation where the first query executed successfully and then the database connection get lost. At that situation the second query will not run on top of that database, so nothing will add to the Alices account. The deducted amount form the Bobs account will also not get recovered. These will make a disaster for Bob, Alice and for the Bank.

"Transactions" can solve this problem with a great solution which will ensure the consistency of the database states. Simply with Transactions the database can be role back to the initial state where it was before the deduction query executed on the database. Its like this.

  1. Transaction Start
  2. Deduct XXX amount form Bobs account.
  3. Add XXX amount for Alices account.
  4. If both queries run successfully then commit the changes
  5. Otherwise role back to the initial state.
For these operations Mysql provide several commands with it and lets see how we can implement that with PHP.

For support Mysql transactions PHP 5.0 and above provides set of methods which are very similar to the normal mysql functions but with an additional "i" letter with them. The most important functions out of them are as follows.

  • mysqli_connect() - connect with mysql
  • mysqli_select_db() - select a database
  • mysqli_autocommit() - enable and disable the auto commit option
  • mysqli_query() - run the mysql query
  • mysqli_rollback() - role back the database to the initial status
  • mysqli_commit() - commit all the changes to the database
  • mysqli_close() - close the mysql connection
Lets look at how you implement a transaction as a solution for the above problem.

Since transaction only supports from InnoDB storage engine you have to make sure all the database table are with InnoDB engine in your database.


$host = 'localhost';
$user = 'username';
$password = 'password';
$db = 'transaction';

$con = mysqli_connect($host, $user, $password);
mysqli_select_db($con, $db);

//Cancel auto commit option in the database
mysqli_autocommit($con, FALSE);

//You can write your own query here
$query1 = "query for deduct XXX amount form Bobs account";
$results[] = mysqli_query($con, $query1);

//You can write your own query here
$query2 = "query for add XXX amount for Alices account";
$results[] = mysqli_query($con, $query2);
$sucess = true;

foreach( $results as $result) {
if(!$result) {
$sucess = false;
}
}

if(!$sucess) {
mysqli_rollback($con);
} else {
mysqli_commit($con);
}
mysqli_close($con);

In above code the changes will done temporary because we have disabled the auto commit option in the database. After the two queries executed it will check for the results of those queries. If some thing went wrong with the database connection in the middle of the operations the changes will not be permanently applied to the database. And at the end it checks for the success of all queries and if all went fine it will commit the changes to the database.

With this code you will be able to make a consistent database operation for the money transfer action in your database. So your done with set of great consistent database operations on your database with PHP and Mysql.

Still Mysql allows only few operations such as update, insert and delete to be role back using its "Transactions". Following operations cannot be rolled back using Mysql.

  • CREATE DATABASE
  • ALTER DATABASE
  • DROP DATABASE
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • RENAME TABLE
  • TRUNCATE TABLE
  • CREATE INDEX
  • DROP INDEX
  • CREATE EVENT
  • DROP EVENT
  • CREATE FUNCTION
  • DROP FUNCTION
  • CREATE PROCEDURE
  • DROP PROCEDURE
You have to be aware on these limitations before you use "Transactions" for your application. Have a great time with Mysql "Transactions".

References :

45 comments:

  1. You might also want to investigate PDO-based Transaction handling, as many are using that interface today. It being a database-independent interface, it throws exceptions if the underlying driver does not support transactions.

    ReplyDelete
    Replies
    1. Thanks Dan. I'll go through that. I'm not experienced it yet.

      Delete
  2. Yeah, you are right there are so many databases available that we can use. MySql is one of them. it is defined in backend databases and mainly use with PHP.
    HTML Training in Jaipur

    ReplyDelete
  3. Very nice post Mr.Thilanka Kaushalya,

    With Transactions these two guys(Bob and his friend) wont become enemies

    ReplyDelete
  4. Really your content is so informative. So please share some more content ..
    PHP course in Delhi

    ReplyDelete
  5. Hey.. I checked your blog its really useful.. Provides lot of information.. Do check my blogs also https://exploring2gether.com/fascinating-places-near-dehradun/

    ReplyDelete
  6. Nice! its really very helpful. thanks for sharing here.
    we provide short term Course training in Delhi

    ReplyDelete
  7. Thanks for sharing such helpful information with us I appreciate your effort of writing a valuable piece. if you want to learn spanish languagge course. you can contact us.

    ReplyDelete
  8. nice! thanks for great information.to learn german you can contact
    best german languge institute .
    they have certified trainer.

    ReplyDelete
  9. Wonderfull information ! It was very informative. keep sharing it will help others too.
    if you want to learn French Lanaguage you can vist us at https://www.classesofprofessionals.com/french-language-institute-delhi

    ReplyDelete
  10. This is a very interesting web page and I have enjoyed reading many of the articles and posts contained on the website, keep up the good work and hope to read some more interesting content in the future. Absolutely this article is incredible. i would also invite to read my content on Digital Markteing COurse and share your feedback.

    ReplyDelete
  11. hey...It is highly comprehensive and elaborated. Thanks for sharing!

    Localebazar Your single guide for exploring delicious foods, travel diaries and fitness stories.

    Visit us for more-
    localebazar.com

    ReplyDelete
  12. I simply could not leave your site before sharing my reviews that i actually loved the information you shared here.i also would like to invite you to share your review on my content- IELTS coaching in Delhi

    ReplyDelete
  13. "Nice post, this post clears my doubtful question and abundance of knowledge. I would like to see more articles/blogs. I am also a content writer and writing a blog about the Canada Work Visa you can review it."

    ReplyDelete
  14. Very well explained and easy to understand. Thanks for sharing !!!!
    i also would like to share my content topic on French language course and German language course . please review and share your feedback.

    ReplyDelete
  15. Great insights on how to use Mysql Transactions with PHP.

    We will definitely incorporate this in our company for after school programs for kids at
    curio

    ReplyDelete
  16. Thanks for sharing the pretty post, if you focus the certification training to enhance your skill for attaining good job in IT industry, for that you Can take the valuable certification training of Language course German Language Course, French language Course, Spanish Language Course) for your career growth.

    ReplyDelete
  17. The one who does business and takes the responsibility of all the profits and losses is known as entrepreneurs. Here are tips on how to be an entrepreneur.

    For More Info Visit:-https://younity.in/2020/06/05/how-to-be-an-entrepreneur/

    ReplyDelete
  18. thanks for sharing such a informative content with us .i also want to share some info. about Online Spanish language Course, online German Language course and Online French language Course. Please share your review towards this

    ReplyDelete
  19. i really liked your blog. keep shaing these ind for valuable content with us. i also would like to tell you about some course which is very demanding now a days. courses are - Online IELTS Course, Online Short Term Course .

    ReplyDelete
  20. http://leadshire.com/blog/converting-your-stats-into-sales
    Consistent mentality > Moon-shot ideology

    The key understanding of these two ideologies is that the former breeds consistent and observable output to map your journey from “day one” while latter focuses entirely on one grand lottery project to change the picture of your business entirely. While believing in your campaigns and strategies is imperative, it is not wise to club all of your efforts with the chance at homerun or no-run. It may fit in the urban entrepreneurial definition but isn’t the smartest decision to make. The great part about investing in different strategies like your social media engagement, your website experience, your customer service and efficacy of your products and services is that there are enough resources to invest in them and develop them over a course of time. It builds a consistently growing experiences that is in sync with time and tech that produces regular and increasing ROI.

    ReplyDelete
  21. https://www.iraitech.com/blog/relevance-of-cyber-security-during-COVID-19
    Increasing Need For Better Cyber Security Strategies During Covid-19 Pandemic
    With more and more companies and business ventures shifting entirely to the online platform it is necessary to setup efficient and sufficient safety features that safeguard these live virtual infrastructure so as to not jeopardize the company’s sensitive data.

    The majority of IT companies and different service providers have incorporated work from home regime which has inundated a massive increase in these cyber-attacks to take advantage of the situation. In this situation of unrest and technological dysmorphia, it’s important to have your hands on all the available resources that can strengthen your virtual gates and keep the protocols up and running.

    ReplyDelete
  22. thanks for sharing such a informative content with us .i also want to share some info. about Spanish language for kids, online German Language for kids and Online French language for Kids. Please share your review towards this

    ReplyDelete
  23. Thanks for sharing such a beautiful content with us. i also find very good content for personality development for international jobs or opportunity you must read this.

    ReplyDelete
  24. Learning English for Being a beginner learner is not easy at all. It can be easy to feel inadequate and frustrated.We encourage children to talk about events that happened in school and during the week. That's where our teacher's speciality lies. They are trained to gain faith in younger kids and help them find their identity and create trust. English classes for kids is the secret to swift language learning. The conversational approach that we use instils trust in the kids that they can speak English even if they make a minor mistake or two.

    for more information you can call us at +91-8744978672

    ReplyDelete
  25. Thanks for sharing such a beautifull content with us. Our French classes for kids are open to all children aged 8 to 20. Whether you want your children to learn a new language or allow them to do better in school if they are already learning French there, we have the right course for them.

    for more details you can contact us - +91-8744978672

    visit us - https://www.classesofprofessionals.com/french-classes-for-kids

    ReplyDelete
  26. I read a lot of blog posts and I never heard of such a topic. I love the subject you have done about bloggers. Very simple. I am also writing a blog related to the best visa consultants, process and application. You can also see this.
    visa consultants in Delhi

    ReplyDelete
  27. awesome content you have shared on your blog
    you can check our GYC silicon straps high quality printing premium looking bands straps compatible for Mi Xiomi BAND 3 BAND 4. Click on the link given below

    CLICK HERE
    CLICK HERE
    CLICK HERE
    CLICK HERE

    ReplyDelete
  28. Thanks for sharing great content with us. I like reading your site's content more. I appreciate your writing skills and the way you are written. I am also a content writer and writing about a Malta work permit, please check and review that.

    ReplyDelete
  29. Good post, and thanks for sharing such an adorable post. I would love to see more articles / blogs. I am also writing about online GST course you can read this article.

    ReplyDelete
  30. I just like the helpful information you provide in your articles. I will bookmark your blog and take a look at it once more here regularly.
    I am somewhat certain I’ll be informed of plenty of new stuff right here! Good luck with the following! please check and review the best visa Consultants in Delhi

    ReplyDelete
  31. Hey ,

    Great Job . You Know what ?

    I read a lot of blog posts and I never heard of such a topic. I love the subject you have done about bloggers. Very simple. I am also writing a blog related to the ielts online coaching. You can also see this.

    ReplyDelete
  32. What a fantastic post! This is so chock full if useful information I can't wait to dig deep and start utilizing the resources you have given me. your exuberance is refreshing

    you've outdone yourself this time

    This is probably the best, most concise step by step guide i've evere seen on how to build a successful blog. i am also writing blog about the kindly review it personality development classes.

    ReplyDelete
  33. Thanks for sharing the pretty post, if you focus the certification training to enhance your skill for attaining good job in IT industry, for that you Can take the valuable certification training course PHP

    ReplyDelete
  34. valuable certification training course www.surat-training-course.com/php-training-course-surat"

    ReplyDelete
  35. I am very grateful that I found some helpful content during this post. After reading it, I feel that you simply have good knowledge. Thanks for posting it. Keep it up. php course in delhi

    ReplyDelete
  36. After reading the above posts, I got some useful and informative knowledge. Thanks for posting it. best php institute in delhi

    ReplyDelete
  37. Keep up the good job and I look forward to reading more intriguing information in the future. This is a really interesting website, and I have liked reading many of the articles and postings on the website. Without a doubt, this essay is amazing. I also invite you to read my articles on the Digital Marketing Course and provide comments.
    industrial training institute

    ReplyDelete
  38. This comment has been removed by the author.

    ReplyDelete