Using Promise for parallel insert query on MySQL fails

Question

I wrote a code for running insert queries in parallel in Node.js and I am also using Promise.js.

But the code fails and raises an exception of "Duplicate Primary Key" entry.

The code is as follows,

 var Promise = require("promise");
 var mySql = require("mysql");
 var _ = require("underscore");

 var connection = mySql.createConnection({
   host : "localhost",
   user : "root",
   password : "rahul",
   database : "testDb" //schema
 });

 connection.connect();

function insertDept(name){
   return new Promise(fn);

   function fn(resolve,reject){
    getMaxDept().then(function(rows){
        var deptId = rows[0]["DeptId"];
        deptId = (_.isNull(deptId) === true) ? 125 : deptId;
        var sql = "insert into departmentTbl values("+deptId+",'"+name+"')";
        console.log(sql);
        connection.query(sql,function(err,rows,fields){
            if(err){
                console.log(err);
                return reject(err);
            }else{
                return resolve(rows);
            }
        });

    }).catch(function(error){
        return reject(error);
    }); 
   }//fn            
 }//insertDept

function getMaxDept(){
return new Promise(fn);

function fn(resolve,reject){
    var sql = "select max(deptId) + 1 as 'DeptId' from departmentTbl";

    connection.query(sql,function(err,rows,fields){
        if(err){
            console.log(err.stack);

            return reject(err);
        }else{
            return resolve(rows);
        }
    });
  }// fn    
} //getMaxDept 

function createDeptForAll(){
  var promiseObj = [];

  if(arguments.length > 0){
    _.each(arguments,callback);
  }else{
    throw "No departments passed";
  }

  function callback(deptName){
    promiseObj.push(insertDept(deptName))       
  }

  return Promise.all(promiseObj);
}//createDeptForAll


  createDeptForAll("Archiology","Anthropology").then(function(createDepartment){
   createDepartment.then(function(rows){
    console.log("Rows inserted "+rows["affectedRows"]);
  }).catch(function(error){
    console.log(error);
  }).done(function(){
    connection.end();
  });
});

When I run the above code code, the output is

rahul@rahul:~/myPractise/NodeWebApp/NodeMySqlv1.0$ node queryUsingPromise02.js 
insert into departmentTbl values(125,'Archiology')
insert into departmentTbl values(125,'Anthropology')
{ [Error: ER_DUP_ENTRY: Duplicate entry '125' for key 'PRIMARY'] code: 'ER_DUP_ENTRY', errno: 1062, sqlState: '23000', index: 0 }

As Department Id is Primary key and the promises run in parallel, the primary key for second Department's insert query fails.

As you can see, before any insert query, I fetch the max of departments + 1.

if the above query fails, I assign '125'.

Now, what should I change so that my above written code runs.

Should I use trigger of "before insert" for calculating next value of primary key of "department ID" at the database level itself or should I do something in my own Node.js code?

Answer 1

This issue is not restricted to node or JavaScript, but you will face this problem with any technology that tries to write to an SQL database in parallel. Unique id generation in a scenario like this is not trivial.

If you have the option to do so, make your id field in your database AUTO_INCREMENT, this will save you a lot of headaches in situations like this.

More about AUTO_INCREMENT.