[adsense id=”0514458240″ width=”468″ height=”60″]
MySQL offers transaction processing. What does this mean? It means that if you start a transaction and perform UPDATEs, DELETEs and INSERTs but for some reason the processing halts. These will be rolled back to the state prior to starting the processing.
Lets imagine we have a data model with a parent/child relationship. We need to remove entries from the child table, then from the parent table, then add entries into the parent table and finally add entries into the child table. If we do not do this as a transaction and the processing fails during this operation we do not know what state the table has been left in.
If we are satisfied with the state of the table then we can COMMIT the transaction. If we are not happy we can ROLLBACK the transaction.
The first thing to make sure is that the storage engine that you use when creating the tables in MySQL support transactions. The most common storage engine in MySQL supporting transactions is InnoDB. MySQL cluster tables NDB also support transactions but these are used if a high availability cluster of databases has been configured.
Good PHP development would be to use something like this while coding:
// Start the transaction
mysql_query("BEGIN");
try {
mysql_query("DELETE FROM childtable");
mysql_query("DELETE FROM parenttable");
mysql_query("INSERT INTO parenttable (...) VALUES (...)");
mysql_query("INSERT INTO childtable (...) VALUES (...)");
mysql_query("COMMIT");
} catch (Exception $e) {
mysql_query("ROLLBACK");
}