Serializable transactions using mysql and node.js

Question

I am having problems with transactions using node.js and mysql. The problem is that my transactions do not run in isolation, even if I set the isolation level to 'serializable'.

I set up the following minimal example to exemplify my problem. I am using a single table with two collumns (id, val):

CREATE TABLE `A` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `A` (`id`, `val`) VALUES (1,0);

Note that I am unsing InnoDB table type because it supports transactions.

My node.js program just reads the value from the single row in table A, and increments it using an update statement. The select statement uses the FOR UPDATE modifier to obtain a lock on the row. The two sql statement are wrapped into a transaction, and I execute 10 of these transactions in a for loop:

var orm = require("orm");
orm.connect("mysql://root@localhost/test", function (err, db) {
    db.driver.execQuery("SET GLOBAL tx_isolation='SERIALIZABLE';", function (err, data) {
        for (var i = 0; i < 10; i++) { 
            db.driver.execQuery("START TRANSACTION;", function (err, data) {
                db.driver.execQuery("SELECT * FROM A FOR UPDATE;", function (err, data) {

                    var value = data[0].val
                    console.log('reading value: ',value)

                    db.driver.execQuery("UPDATE A SET val="+value+"+1 WHERE id=1", function (err, data) {
                        console.log('writing value: ', value+1)

                        db.driver.execQuery("COMMIT;", function (err, data) {})
                    })
                })
            })
        }
    })
})

I would expect that after running this code, the value stored in the table A is incremented by 10. However, it is just incremented by 1.

To understand what's going on I added printouts to the code. I would expect to see printouts

reading value 0
writing value 1
reading value 1
writing value 2
...

However I get the printouts

reading value 0
reading value 0
...
writing value 1
writing value 1
...

One way to fix the problem is to establish a new db connection for each transaction, but I'd prefer not to do that for performance reasons.

Can someone explain what is going on, and how I can change the above into a working example for transactions in node.js?

Answer 1

Ok, we found a solution using the node-mysql-transaction package (see code below).

var mysql = require('mysql');
var transaction =  require('node-mysql-transaction');

var trCon = transaction({
  connection: [mysql.createConnection,{
    host     : 'localhost',
    database : 'test',
    user     : 'root',
    password : 'root',
  }],
  dynamicConnection: 32,
  idleConnectionCutoffTime: 1000,
  timeout:600
});

for(var i=0;i<10;i++) {

trCon.set(function(err, safeCon){

  safeCon.query('SELECT * FROM A FOR UPDATE;',[],function(err,result){
    if (err) safeCon.rollback()

    var val = result[0].val
    val += 1

    safeCon.query('UPDATE A SET val='+val,[],function(err,result){
      if (err) safeCon.rollback(err)
      else safeCon.commit();
    })
  })
})    
}