title: Serializable transactions using mysql and node.js
body: <p>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'.</p>
  
  <p>I set up the following minimal example to exemplify my problem. I am using a single table with two collumns (id, val):</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Note that I am unsing InnoDB table type because it supports transactions. </p>
  
  <p>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:</p>
  
  <pre><code>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 &lt; 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) {})
                      })
                  })
              })
          }
      })
  })
  </code></pre>
  
  <p>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. </p>
  
  <p>To understand what's going on I added printouts to the code. I would expect to see printouts</p>
  
  <pre><code>reading value 0
  writing value 1
  reading value 1
  writing value 2
  ...
  </code></pre>
  
  <p>However I get the printouts</p>
  
  <pre><code>reading value 0
  reading value 0
  ...
  writing value 1
  writing value 1
  ...
  </code></pre>
  
  <p>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.</p>
  
  <p>Can someone explain what is going on, and how I can change the above into a working example for transactions in node.js?</p>