Wednesday, October 19, 2011

How MySQL Transaction works ?

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Practically you will club many SQL queries into a group and you will execute all of them together as a part of a transaction.
You can not use transactions directly, you can but they would not be save and guaranteed. If you plan to use transactions in your MySQL programming then you need to create your tables in a special way. There are many type of tables which support transactions but most popular one is InnoDB.
You can execute these SQL commands in PHP by using mysql_query() function.

  1. Begin transaction by issuing SQL command BEGIN WORK
  2. Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE
  3. Check if there is no error and everything is according to your requirement.
  4. If there is any error then issue ROLLBACK command otherwise issue a COMMIT command.
Those who have no idea about the MySQL Transactions can take a look at the below code.
It will help you to get an overall idea about how a transaction works in MySQL.
 
mysql_query("START TRANSACTION");

$a = mysql_query("INSERT INTO table_name (amount) VALUES('100')");
$b = mysql_query("INSERT INTO table_name (amount) VALUES('200')");
if ($a and $b) {
    mysql_query("COMMIT");
} else {        
    mysql_query("ROLLBACK");
}


No comments:

Popular Posts