NodeJs + MySql connection pool ER_WAIT_LOCK_TIMEOUT sometimes only

user_13875311 0
Viewed 468

I'm facing random issue its not happen everytime. i'm using nodeJs and connection pool. and in connection we have connection limit 50. and this is happening on INSERT only.

Does nested query blocks cause this issue ??

router.post('/userUpdateProfile', (req, res) => {

    let selectedSports = [1,2];

    mysqlPool = mysql.createPool(mysqlConfig);
    const sql1 = `call userProfileUpdate(some params);`;

    mysqlPool.query(sql1, [params], (err, response) => {

        const sql2 = `call userSportMapDeleteAll(?);`;

        mysqlPool.query(sql1, [params], (err1, response1) => {
            var sqlBlk = "INSERT INTO UserSportMapping (userId, sportId) VALUES ?";

            mysqlPool.query(sqlBlk, [tempSportArray], function(bErr) {
                //Here sometimes ER_LOCK_WAIT_TIMEOUT
            });
        });
    })
}
node.js connection-pooling mysql
2 Answers

without releasing connection you are opening new one. You need to close/release connection when you finish your operation.

If this is an atomic procedure. execute them together instead of nesting it.

to execute multiple statements:

var connection = mysql.createConnection({multipleStatements: true});
connection.query('SELECT ?; SELECT ?', [1, 2], function(err, res) {
  if (err) throw err;

  console.log(res[0]); 
  console.log(res[1]); 

});

check this out for handling connections properly

EDIT:

connection.query('SELECT ?; SELECT ?', [1, 2], function(err, res) {
   // connection opened here
   ......

}); //connection released here
user_11189780

I've tried below solution and its worked for me. Thanks @Derviş Kayımbaşıoğlu for pointing me that my script creating new connection without waiting for closing old one.

By using getConnection method. my issue resolved now.

router.post('/userUpdateProfile', (req, res) => {

    let selectedSports = [1,2];

    mysqlPool = mysql.createPool(mysqlConfig);
    const sql1 = `call userProfileUpdate(some params);`;

    mysqlPool.getConnection(function(conErr, connection) {

        if(conErr) throw;

        connection.query(sql1, [params], (err, response) => {

            const sql2 = `call userSportMapDeleteAll(?);`;

            connection.query(sql1, [params], (err1, response1) => {
                var sqlBlk = "INSERT INTO UserSportMapping (userId, sportId) VALUES ?";

                connection.query(sqlBlk, [tempSportArray], function(bErr) {

                    connection.release();

                });
            });
        })
    }):

}
user_138753110
Related