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 :

MySQL Transaction Gotchas & Good Parts

Implement MySQl-based Transactions with a new set of PHP extensions