Welcome to Particletree!

Transactions in MySQL allow us to execute any number of queries that will automatically undo themselves if one of the queries fails. This is great because our data is kept in tact if something were to go wrong after the third of five queries. The problem I recently ran into is that MySQL transactions with InnoDb tables do not play properly with certain queries, such as ALTER TABLE. It turns out certain queries act as an automatic COMMIT in addition to their default operation. So how do we keep everything under control when you need transactions, but you also need queries such as ALTER TABLE? Well, the solution I came up with is not ideal or efficient, but it is a solution. The problem is that to achieve this we have to open another connection to the database. So, lets say we have 4 queries:

• INSERT INTO
• UPDATE
• DELETE
• ALTER TABLE

One database connection will process the first three in transaction mode, while the second connection handles the last query. Remember to open up the second connection with the new_link parameter set to true:

mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password', true);

If you do not use true for new_link, a new connection will not be opened — you will just receive a handle on the first connection.

Given our two connections, we can now create functions that will assist us:

(Note: The code uses a straightforward database class that executes a normal mysql_query. For simplicity, all $db objects in this example are set to a mysql_connect, which we replace with a basic Particletree class behind the scenes — it is not necessary to understand the class in order to understand the concept behind this notebook)

function begin($db) {
    $sql = 'BEGIN';
    $db->nonquery($sql);
}function commit($db) {
    $sql = 'COMMIT';
    $db->nonquery($sql);
}function rollback($db) {
    $sql = 'ROLLBACK';
    $db->nonquery($sql);
}

The three functions above just initiate the basic queries involved in a transaction. To learn more, read this. We will then need our actual queries that we want to run in transaction mode:

function runNormalQueries($db) {
    $ret = 0;
    if(!$db->nonquery(Query1)) $ret = -1;
    if(!$db->nonquery(Query2)) $ret = -2;
    if(!$db->nonquery(Query3)) $ret = -3;
    return $ret;
}function runAlterQuery($db) {
    $ret = 0;
    if(!$db->nonquery(Query4)) $ret = -4;
    return $ret;
}

Now that all of our functions are in place, you can see an idea of how the logic would flow below:

$db1 = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');
begin($db1);
if(runNormalQueries($db1) == 0) {
    $db2 = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password', true);
    if(runAlterQuery($db2) == 0) {
        commit($db1);
    }
    else {
        rollback($db1);   
    }
}
else {
    rollback($db1);
}

We first create our first connection to the database, and set it into transaction mode by calling begin(). Then, if all 3 normal queries are successful, we will open a second connection. This is important because we do not want to waste resources if one of the first 3 queries fails. After the second connection is open, process the weird query (ALTER TABLE in our case). If the query fails, rollback the first connection by calling rollback(). If everything works, commit the first connection by calling commit().

Again, this is not a beautiful solution, but it does accomplish what is needed. Luckily, we only need to execute one auto committing query on any given page load. If you need to execute more than one, I think you are out of luck because they can’t be undone. If there is anyone out there with more experience on this who may know of a sexier approach, please do tell.