Atomic Operations in MySQL–How to wrap Inserts or Updates into a Transaction

innodb-vs-myisam-300x225As a developer, it is normally quite un-nerving to get a call from the people using your beautifully designed software with the message – things have gone wrong and your code or system has gone fritzzz… I normally just blame the operators cum users of the system for either not using the system right or for putting in wrong inputs. I know – as developers, we are supposed to code as if the users are total and absolute idiots but from time to time, the crazy things that users put as inputs on systems just catches us off guard as anybody in their right mind would never have put ‘that’ as an input.

You would be surprised how many times people call, and when the customer care clerk asks the user for their usernames so that their account can be located and looked into – the person needing help on the other side of the line says “Let me text you my username”. The reason as to why some one would use the term bi*** as their username on a public site is beyond me. I digress, back to the topic.

There is a certain system I developed and although for the most part, things work on the platform, from time to time, errors of half finished processes kept occurring. The site administrators were tired of going through the documented process of solving these errors and forced me to find a fix. It took a while to find the bug as recreating an intermittent problem can be tough but it was finally identified.

Under some conditions, not all the queries of a certain function were being committed as they should be. This quickly brought back memories of school and being asked in the Database Management Unit Exam to define or explain Atomic Operations in Database Management Systems.

According to Wikipedia, a programing operation is atomic if it appears to the rest of the system to occur instantaneously and either successfully change the state of the system, or have no apparent effect. This is exactly what I required to fix this bug, and here is how I did it (with reference to PHP and MySQL)

First, I had to change the database engines of the table(s) involved to one that is transactional. Most web-based system prefer the use of MyISAM for their databases mainly because it is fast! Should your server support it, you will need to change the engine to something like InnoDB or BDB storage engines.

Code:

ALTER TABLE t1 ENGINE = InnoDB;

Next, it was a matter of wrapping queries into a transaction and either performing a commit when everything works out or a rollback should there be any unexpected errors.

Find below some code to demonstrate how to do this in PHP MySQL

<?php

mysql_connect('localhost', 'testuser', 'test623') 
    or die("cannot connect to database\n");

mysql_select_db("testdb") or die(mysql_error());

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

$r1 = mysql_query("DELETE FROM Writers WHERE Id = 3")
    or die(mysql_error());

$r2 = mysql_query("DELETE FROM Writers WHERE Id = 4")
    or die(mysql_error());

$r3 = mysql_query("DELETE FROM Writers WHERE Id = 5")
    or die(mysql_error());

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

mysql_close();

?>

The notable lines of code are as follows

mysql_query("START TRANSACTION");

The START TRANSACTION statement starts a new transaction. All MySQL related changes will be made permanent with a COMMIT statement or ignored with the ROLLBACK statement.

if ($r1 and $r2 and $r3) {
    mysql_query("COMMIT");
} else {
    mysql_query("ROLLBACK");
}

We commit the statements only if all SQL statements returned True. Otherwise, we roll them back.

Now, granted, the above example may be a bit simple but it works. Should you have any queries (pun intended) concerning it, holla at me in the comments.

Share

Leave a Reply