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. Nice post. Parents are parents, not Gods. Of course they cannot have all the answers. The way you explain things to your kids should do all the work…it all comes under parenthood and your people skills.
    PHP Traning

    ReplyDelete
  3. 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
  4. Very nice post Mr.Thilanka Kaushalya,

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

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

    ReplyDelete
  6. شركة اللمسة الأخيرة تقدم لك الحل الأمثل فلا حشرات بعد اليوم ولن تعود مرة أخرى. فنحن نستخدم أفضل المبيدات العالمية الفعالة صديقة البيئة التي لا تترك رائحة ولا سيوثر على صحة الأنسان ويقوم باستخدامها عمال مدربون يقومون برش المبيدات بشكل علمي مما يضمن لك الراحة التامة نرجو التواصل على هذا الرقم 0580002467
    شركة رش مبيدات بأبها
    شركة مكافحة حشرات بأبها
    شركة مكافحة النمل الابيض بأبها
    شركة رش مبيدات بخميس مشيط
    شركة مكافحة حشرات بخميس مشيط
    شركة مكافحة النمل الابيض بخميس مشيط
    شركة رش مبيدات بالقصيم
    شركة مكافحة حشرات بالقصيم
    شركة مكافحة حشرات بجازان
    شركة رش مبيدات بجازان

    ReplyDelete
  7. شركة اللمسة الأخيرة تقدم لك الحل الأمثل فلا حشرات بعد اليوم ولن تعود مرة أخرى. فنحن نستخدم أفضل المبيدات العالمية الفعالة صديقة البيئة التي لا تترك رائحة ولا سيوثر على صحة الأنسان ويقوم باستخدامها عمال مدربون يقومون برش المبيدات بشكل علمي مما يضمن لك الراحة التامة نرجو التواصل على هذا الرقم 0580002467
    شركة رش مبيدات بأبها
    شركة مكافحة حشرات بأبها
    شركة مكافحة النمل الابيض بأبها
    شركة رش مبيدات بخميس مشيط
    شركة مكافحة حشرات بخميس مشيط
    شركة مكافحة النمل الابيض بخميس مشيط
    شركة رش مبيدات بالقصيم
    شركة مكافحة حشرات بالقصيم
    شركة مكافحة حشرات بجازان
    شركة رش مبيدات بجازان

    ReplyDelete
  8. هل تبحث عن شركة متخصصة فى خدمات التنظيف بالطائف بافضل المعدات والسوائل وثقة تمة فى العمل ودقة فى النتائج كل هذه المميزت توفرها شركة الخليج الشركة الافضل والامثل فى الخدمات المنزلية بالطائف وبما اننا الشركة الافضل والامثل بدون منافس سوف نسعى لتوفر افضل الخدمات باقل تكلفة وبقدر كبير من الاهتمام والدقة عزيزى اينما كنت فى اى منطقة ا وحى تابع لمدينة الطائف اتصل بنا وسوف نصلك فى الحال شركة الخليج للخدمات المنزلية شركة تنظيف منازل بالطائف
    شركة تنظيف فلل بالطائف
    شركة تنظيف خزانات بالطائف
    شركة تسليك مجارى بالطائف
    شركة رش مبيدات بالطائف
    شركة مكافحة نمل ابيض بالطائف
    شركة مكافحة حشرات بالطائف
    شركة عزل اسطح بالطائف
    شركة عزل خزانات بالطائف

    ReplyDelete
  9. نحن نمتلك مجموعة من الخبراء والمتخصصين فى شركة تنظيف بابها ونحن نستطيع التعامل مع كافة المساحات المختلفة فلا يهم ان كنت تمتلك منزل او فيلا فأن لدينا خبرات كبيرة تمكنا من تقديم خدماتنا على اكمل وجه ولدينا عمال وفنيين محترفين ولهم خبرات مختلفة نقدم ايضآ تنظيف لواجهات الشركات والفنادق. فكل ما تحتاجه من معدات واجهزة ومنظفات ذات جودة عالمية موجودة بشركتنا فنحن نسعى فقط لارضاء العميل أولآ واخيرآ

    شركة عزل خزانات بخميس مشيط
    شركة مكافحة حشرات بخميس مشيط
    شركة غسيل مجالس بخميس مشيط
    شركة غسيل خزانات المياه بخميس مشيط
    شركة غسيل خزانات بخميس مشيط
    شركة تنظيف شقق بابها

    ReplyDelete
  10. 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
  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. Hey.. Well explained... provides a lot of knowledge... Thanks for sharing !!
    Check this out https://exploring2gether.com/red-sauce-pasta-recipe/

    ReplyDelete
  13. Very well explained and easy to understand. Thanks for sharing !!!!
    https://exploring2gether.com/shopping-places-in-delhi-that-are-worth-checking-out/

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

    ReplyDelete
  15. 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
  16. nice! thanks for great information.to learn german you can contact
    best german languge institute .
    they have certified trainer.

    ReplyDelete
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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

  22. Thanks for sharing such helpful information with us I appreciate your effort of writing a value able piece.
    i also write on Spanish Language Course in Delhi.
    Please share your review on that.

    ReplyDelete
  23. "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
  24. 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
  25. 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
  26. Thanks for sharing such valuable information with us I appreciate your effort of writing a value able piece. immigration consultants in Delhi

    ReplyDelete
  27. 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
  28. 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
  29. 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
  30. 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 Spanish language, Online german Language, Online French Language.





    ReplyDelete
  31. 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
  32. 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
  33. 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
  34. 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
  35. Thanks for sharing such a beautiful content with us. i would also like to introduce Japanese Classes for kids. They aim to provide high-quality education and cater to every child’s needs.

    ReplyDelete
  36. 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