MySQL transactions not stopping race conditions from for loop

Question

I'm using node-mysql in my application. I tried implementing SELECT FOR UPDATE but I haven't been able to get it to work properly. The problem I have looks like the code below. The first transaction in the for loop below does not successfully block the second transaction. Instead, both transactions SELECT FOR UPDATE at the same time and get the same record. How can I fix this? Thanks!

var testTransaction = function (count) {
  connection.beginTransaction(function(err) {
    if (err) throw err;
    db.query('SELECT * FROM myTable WHERE id = 1 FOR UPDATE', function(err, rows, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }
      connection.query('UPDATE myTable SET myField=? WHERE id=1', (count + 1), function(err, result) {
        if (err) { 
          db.rollback(function() {
            throw err;
          });
        }  
        connection.commit(function(err) {
          if (err) { 
            db.rollback(function() {
              throw err;
            });
          }
          console.log('success!');
        });
      });

    }); 

  });
}

for (var i = 0; i < 2; i++) {
  testTransaction(i);
}

I ask, because I've heard that only InnoDB has support for consistent reads, and I don't know if that would make a difference. — cloudfeet, Oct 31 '13 at 13:49
I don't really understand why do you need to do `SELECT FOR UPDATE` just before making the update? Are you planning to do anything in between? — Bulat, Sep 20 '14 at 01:31

score 0 · Answer 1 · answered Oct 10 '13 at 02:54

The methods in the node-mysql module are written to be asynchronous, and therefore will not block the application. If you have little reason to be using a loop, then you can just add a callback to your function and nest the execution:

var testTransaction = function(count, callback) {
  connection.beginTransaction(function (err) {
    if (err) throw err;
    db.query('SELECT * FROM myTable WHERE id = 1 FOR UPDATE', function (err, rows, result) {
      if (err) {
        connection.rollback(function() {
          return callback(err);
        });
      }
      connection.query('UPDATE myTable SET myField=? WHERE id=1', (count + 1), function (err, result) {
        if (err) {
          db.rollback(function() {
            return callback(err);
          });
        }
        connection.commit(function (err) {
          if (err) {
            db.rollback(function() {
              return callback(err);
            });
          }
          callback(null);
          console.log('success!');
        });
      });
    });
  });
};

testTransaction(0, function(err) {
  testTransaction(1, function(err) {
    // both operations have completed
  });
});

If you need to loop asynchronous functions for some reason, then I'd take a look at the async library.

I think the issue was that the asker was expecting the transaction to "block" read/write - meaning that one `SELECT`/`UPDATE` pair would complete before the next transaction began. — cloudfeet, Oct 31 '13 at 13:47

score 0 · Answer 2 · answered Sep 23 '14 at 07:30

Your problem is that you are using the same connection.

Try something like this:

var mysql      = require('mysql');
function conn() {
    var connection = mysql.createConnection({
        host     : 'localhost',
        user     : 'user',
        password : 'pass',
        database : 'test'
    });
    return connection;
}

var testTransaction = function (connection,count) {
    connection.beginTransaction(function(err) {
        if (err) throw err;
        connection.query('SELECT * FROM table WHERE id = 1 FOR UPDATE', function(err, rows, result) {
            console.log(rows);
            if (err) {
                connection.rollback(function() {
                    throw err;
                });
            }
            connection.query('UPDATE table SET name=? WHERE id=1', (count + 1), function(err, result) {
                if (err) {
                    connection.rollback(function() {
                        throw err;
                    });
                }
                setTimeout(function(){
                    connection.commit(function(err) {
                        if (err) {
                            connection.rollback(function() {
                                throw err;
                            });
                        }
                        console.log('success!');
                    });
                },2000);
            });

        });

    });
}

for (var i = 0; i < 2; i++) {
    testTransaction(conn(),i);
}

Notice that it uses a different connection for each transaction.

In command-line mysql, you can replicate something similar, by opening two connections, issuing start transaction in both, and then trying the select ... for update command twice in one (works both times) and once in the other (waits for the first).

In case it's not all that obvious, there is no race condition if the commands are sent via the same connection. — Chris Lear, Sep 23 '14 at 07:35
I think the connection pooling should deal it nicely. Normally each connection is only used by one process at a time. — Chris Lear, Oct 02 '14 at 08:30

MySQL transactions not stopping race conditions from for loop

2 Answers2