Sometime during software development, we face a situation where we need to execute more than two sql queries to successfully complete a process. All the dependent queries has to be executed in all-or-nothing mode. Say, to complete X process, we need to insert data in one table and update two another tables. Unfortunately, for various reasons, sometime some of the intended queries fails and create inconsistency in database. To get rid of such situation, we can use mysql transaction feature.
A transaction is a set of inter-dependent SQL statements that needs to execute in all-or-nothing mode. A transaction is successful if all SQL statements executed successfully. A failure of any statement will trigger the system to rollback to the original state to avoid data inconsistency.
Example (with PHP):
mysql_query("START TRANSACTION"); // start the transaction
$q1 = mysql_query("some sql query");
$q2 = mysql_query("some other sql query");
if(q1 & q2){
mysql_query("COMMIT"); // commit if all queries are executed
} else{
mysql_query("ROLLBACK"); // rollback to initial stage, recover data
}Note: It is good to use innoDB Engine while creating tables where mysql transaction has to be used as innoDB is transaction safe storage engine.