title: How to avoid deadlock in nodejs mysql with a lot of queries?
body: <p>I have a lot of urls, for every url I call the function load(url), this function parse the html, extracts the needed data and builds a bulk insert query as you can see in my test.js code. The problem is, if I have to many urls (like 100+), I get a Error: ER_LOCK_DEADLOCK from mysql. I tried to use async.queue but this is somehow not working (I don't know why, maybe I am using is wrongly). How can I run many urls + queries one after another, avoiding parallel execution which I think resulted in a deadlock? Even using async.queue results to a DEADLOCK (not always).</p>
  
  <p>test.js</p>
  
  <pre><code>const request = require('request');
  const async = require('async');
  const pool = require('./database');
  
  const urls = [
      'https://www.quora.com/What-is-the-best-way-to-have-delayed-job-queue-with-node-js',
      'https://de.wikipedia.org/wiki/Reinhardt-Zimmermann-L%C3%B6sung',
      'https://towardsdatascience.com/the-5-clustering-algorithms-data-scientists-need-to-know-a36d136ef68'
  ]
  
  let load = function(url) {
      request({url: url}, function(error, response, html) {
          if(!error) {
              console.log(html);
              /**
               * 1. Parse HTML
               * 2. Create Array of Values
               * 3. Call pool.query(sql, [values], function(error) { ... })
               */
              let data = [{}];
              let sql = "INSERT IGNORE INTO tbl_test (title, content) VALUES ?";
              let values = [];
  
              data.forEach((item) =&gt; { values.push(item) });
  
              pool.query(sql, [values], function(error) { 
                  if(error) throw error;
               })
          } else {
              console.log("handle error...");
          }
      })
  }
  
  let jobs = []
  
  /*urls.forEach((url) =&gt; {
      //jobs.push(load(url)); // --&gt; Works but fails if the urls list is to big -&gt; mysql deadlock error!
      jobs.push(function(callback) { callback(load(url)) });
  })*/
  
  let q = async.queue(function(task, callback) {
      console.log("Task:", task.uri);
      callback();
  })
  
  q.drain = function() {
      console.log('all task completed');
      pool.end();
  }
  
  urls.forEach((url) =&gt; {
      q.push({uri: url}, function(err) {
          console.log('finished processing ...')
      });
  });
  </code></pre>
  
  <p>databse.js</p>
  
  <pre><code>require('dotenv').config();
  
  const mysql = require('mysql');
  
  let pool = mysql.createPool(
      {
          connectionLimit: 10,
          host: process.env.DB_HOST,
          port: process.env.DB_PORT,
          user: process.env.DB_USER,
          password: process.env.DB_PASSWORD,
          database: process.env.DB_NAME
      }
  );
  
  pool.getConnection((err, connection) =&gt; {
      if(err) {
          if(err.code === 'PROTOCOL_CONNECTION_LOST') {
              console.log('Database connection lost.')
          }
  
          if(err.code === 'ER_CON_COUNT_ERROR') {
              console.log('Database has too many connections.')
          }
  
          if(err.code === 'ECONNREFUSED') {
              console.log('Database connection refused.')
          }
  
          if(err.code === 'POOL_CLOSED') {
              console.log('Pool is closed.')
          }
      }
  
      if(connection) {
          connection.release()
      }
  
      return;
  });
  
  module.exports = pool;
  </code></pre>