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.

HTML Form Builder
Ryan Campbell

MySQL Transactions and Auto Committing Queries by Ryan Campbell

This entry was posted 4 years ago and was filed under Notebooks.
Comments are currently closed.

· 6 Comments! ·

  1. peter royal · 4 years ago

    just as a comparison, you can execute DDL in postgresql inside a transaction no problem. obviously the decision to switch databases should be predicated upon more than that one simple difference.

  2. Jeff · 4 years ago

    Would you mind describing the circumstances under which you are performing DDL queries in the normal operation of a web app?

  3. Erwin Moller · 4 years ago

    Thanks for this solution. The more I (am forced to) use MySQL, the more I miss Postgresql. MySQL is really an inmature piece of junk compared to Postgresql. :-/ I was going crazy with a transaction MySQL claimed was rollbacked, but in fact it wasn’t. Tunred out MySQL autocommits when CREATE or ALTER are used. Why MySQL? Why oh why? Did nobody cared to check those functions before shipping, or did they (as usual) burry a note deep into their docs that claimes this is ‘by design’ ??

    The only reason we are forced to use MySQL is because those guys released it under W$, so all nOObs who cannot tell diff between up and down started using it, and now all serious developers are often forced to use it too. Now Postgresql is released under W$, I was hoping for better times, but it seems too late. MySQL took over big time. :-/

    IMHO Postgresql beats mySQL any day.

    Sorry for the rant, but this is the zillionth time I hit a wall with MySQL.

  4. Jason · 4 years ago

    quote from this page:

    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.

    does this mean (pseudo code):

    query(“BEGIN”) query(“UPDATE table SET col=’something’”) //success query(“DELETE FROM table1 WHERE col2 IN (1,2,3)”) //sucess query(“DELETE FROM table WHERE col2=’2 something’”) //fail, ROLLSBACK query(“COMMIT”) // if query #3 didnt fail, it will commit? or will it commit even after rollback?

    I was thinking that I should check the result of each statement and executing a COMMIT or ROLLBACK appropriately? but the quote you said on this page seems to make me think I do not need to check the result and that it will ROLLBACK for me? and if it doesn’t roll back, that when it finishes the set of statements, it will COMMIT when I run that statement?

  5. pKG10UxOWi · 4 years ago

    Hi! Very nice site! Thanks you very much! xVNH803Jzdh

  6. hfmmpqnhba · 4 years ago

    yypyrway