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 :

6 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