MySQL Transaction in PHP

Introduction

In the real world, a database may be used by several users at the same time. When it is, then what happens is that two users might try to update a single record at the same time. So this article describes how to handle this type of situation.

Now first I will explain what a is transaction is. "A transaction is a group of SQL statements that you combine into a single unit of work., By combining SQL statements you can prevent certain kinds of database errors." Before you begin using MySQL to work with transactions, you should realize that some storage engines do not support transactions, like the MyISAM storage engine. Only the InnoDB storage engine supports transactions. It is fully ACID compliant. ACID stands for, Atomicity, Consistency, Isolation and Durability. To work with transactions, the preceding property should have use, when you work with transactions. Operation within a transaction must be atomic, which means that either all operations succeed or all fail. The consistency property means, the database must be in a consistent state after the transaction. The data is valid and there are no half-finished records. Isolation is the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions. Without isolation, the data may end up in a inconsistent state. Durability is the ability of the database system to recover the committed transaction updates against any kind of system failure.

Now to better clarify my point I have provided an example. Suppose you want to purchase some items online (in other words through a website). The process of purchasing an item is that, first you select the items that you want to purchase, then the website provides an online payment facility. Next you make the payment online and the items purchased will be added to your billing payment. But this website has a rule; the amount of items the purchased, which is deductd from your account, must be added to the vendors account, then you will be authorized for the items. Now let's see the second condition of the process of purchasing items. The only, and the most important condition is that, if your amount to be deducted is added to the venders account, then you will be authorized for those items. Suppose one SQL query for the amount to be deducted for the purchased items has been successfully executed, but that amount that is deductd from your account and that is added to the vendor's account query was not successful, then what happens is that the amount is deducted from your account but not added to the vendor's account. So you are not authorized for the items. To solve such type of problems, we can work with transactions. In other words whenever all operation are not successfully executed, the database values will not be committed. So in the transaction, if at least one statement fails, all the changes will be rolled back and all the changes in the database will be in its initial state. 

By default, MySQL sessions use automatic mode, which automatically commits INSERT, DELETE and UPDATE statements immediately after you execute them. Then you can use a transaction to control when the changes are committed. Basically transactions are often coded within a "Stored Procedure".

Example of transaction in PHP

In the following example when you execute the mysqli_query() function, the result is immediately committed to the database, and when you use the mysqli_autocommit() function, you can turn off this behavior so that the result would not be committed to the database permanently until you command. Then we simply execute necessary statements and set the $flag variable to false. If any statements fail and if no error is generated then set the $flag variable to true, and use the mysqli_commit() function to commit the result to the database permanently. Else we use the mysqli_rollback() function to roll back the result.

<?php
$dbConnection = mysqli_connect('localhost', 'root', '', 'mysql');
mysqli_autocommit($dbConnection, false);
$flag = true;
$query1 = "insert into buyer values(1234567,45345)";
$query2 = "insert into seller values(4357657,1233)";
$result = mysqli_query($dbConnection, $query1);
if (!$result)
{
$flag = false;
echo "Error details: " . mysqli_error($dbConnection) . ". ";
}
$result = mysqli_query($dbConnection, $query2);
if (!$result)
{
$flag = false;
echo "Error details: " . mysqli_error($dbConnection) . ". ";
}
if ($flag)
{
mysqli_commit($dbConnection);
echo "All queries were executed successfully";
}
else
{
mysqli_rollback($dbConnection);
echo "All queries were rolled back";
}
print "<h2>MySQL: All data of seller and buyer table</h2>";
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
$result1 = mysql_query("SELECT * FROM buyer");
$result2 = mysql_query("SELECT * FROM seller");
echo "<table border='1'><tr>;
<th>AccountNumber</th>;
<th>Amount</th>;
</tr>";
while($rowval = mysql_fetch_array($result1))
{
echo "<tr>";
echo "<td>" . $rowval['account_id'] . "</td>";
echo "<td>" . $rowval['amount'] . "</td>";
echo "</tr>";
}
  while($rowval = mysql_fetch_array($result2))
{
echo "<tr>";
echo "<td>" . $rowval['account_id'] . "</td>";
echo "<td>" . $rowval['amount'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
mysqli_close($dbConnection);
?>

Output