What is Transaction and how to implement transaction in php using mysqli ?

A transaction is a sequential group of database manipulation operations, which is performed in a sequential manner. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any step or process within the transaction fails, the entire transaction will fail.

Properties of Transactions

  • Atomicity − All operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.
  • Consistency − The database properly changes states upon a successfully committed transaction.
  • Isolation − Transactions to operate independently on and transparent to each other.
  • Durability − The result or effect of a committed transaction persists in case of a system failure.

Note: In Mysql, transaction support in InnoDB database engine.So when you create a table you have to select the InnoDB engine not MyISAM

Now, I will show you how to implement the transaction in PHP using MySQLi.

First of all, you have created two tables(emp,emp_details) with InnoDB in your database.

emp

emp_details

Now, you have to create a test php page with code.

$mysqli = new mysqli('localhost','root','','test');
$mysqli->query("INSERT INTO emp(name) VALUES('rupam')");
$last_id = $mysqli->insert_id;
$result = $mysqli->query("INSERT INTO emp_details(emp_id,salary) VALUES($last_id,3500)");

if you run this code, a new row would be added to your tables simply because MySQL automatically set the autocommit true.
Next you set the autocommit.

$mysqli = new mysqli('localhost','root','','test');
$mysqli->autocommit(false);
$mysqli->query("INSERT INTO emp(name) VALUES('rupam')");
$last_id = $mysqli->insert_id;
$result = $mysqli->query("INSERT INTO emp_details(emp_id,salary) VALUES($last_id,3500)");

Now if you run this code you will see that no rows are added to your tables because of no commit here.
Next, you have to add commit.

$mysqli = new mysqli('localhost','root','','test');
$mysqli->autocommit(false);
$mysqli->query("INSERT INTO emp(name) VALUES('rupam')");
$last_id = $mysqli->insert_id;
$result = $mysqli->query("INSERT INTO emp_details(emp_id,salary) VALUES($last_id,3500)");
$mysqli->commit();

Now if you run this code you will see that rows are added to your tables.
Next, you have to test rollback functionality so, in ACID property if any process or query falls the total transaction would in previous state or rollback.
For testing purpose, you have to edit the 2nd query with the wrong syntax.

$mysqli = new mysqli('localhost','root','','test');
$mysqli->autocommit(false);
$mysqli->query("INSERT INTO emp(name) VALUES('rupam')");
$last_id = $mysqli->insert_id;
$result = $mysqli->query('INSERT INTO emp_details(emp_id,salary) VALUES($last_id,3500)');
if(!$result) $mysqli->rollback();
$mysqli->commit();

Now if you run this code you will see that rows are added to your tables because of 2nd query does not execute that’s why the rollback occurs.
In the above examle i will try to cover a transation process.

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *