0

I have a problem: I changed mysql from createConnection to createPool. But sometimes when many queries will run, I get deadlocks.

Here's the pool

var db_config = {
connectionLimit : 1000,
    host: 'localhost',
    user: '******',
    password: '******',
    database: '******.com'
};
pool = mysql.createPool(db_config);
pool.getConnection(function(err, connection) {
    if(err) {
        logger.error('[ERROR] Connecting to database "' + err.toString() + '"');
        setTimeout(function() { database_connection(); }, 2500);
    }
    else
    {
        pool.query('SET NAMES utf8');
        pool.query('SET CHARACTER SET utf8');
        logger.trace('[INFO] Connected to database and set utf8!');
    }
});

That's the database connection

But for any reason, when many queries send at same time or something, I get deadlock message

Deadlock found when trying to get lock; try restarting transaction

Every query that used for the queries looks like that

pool.query('SELECT * users');

I read that query combines connection + the query + release, that is what i read.

But I don't know why so much times deadlock... anyone has any ideas? :/

Its InnoDB MySQL Tables

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Reudiga
  • 29
  • 1
  • 4
  • Don't deadlocks occur only when _writing_ to the database? I don't see any code in your question that's doing that. – robertklep Jan 03 '19 at 12:00
  • @robertklep i think so. Thats the only point when i insert like 50 times in a loop into a table. Doesnt see that deadlock on update or something. Only found that error on insert when there much insertions running – Reudiga Jan 03 '19 at 12:07
  • I think it's more of a MySQL issue than a Node.js issue. Perhaps read up on how to prevent deadlocks in MySQL. – robertklep Jan 03 '19 at 12:15
  • @robertklep but what can i do there? I dont know why mysql send deadlocks – Reudiga Jan 03 '19 at 12:22
  • That's why it's advisable to find some documentation on when and why MySQL deadlocks (I don't know either), and see if there are any mitigations that will prevent (or at least limit) deadlocks. – robertklep Jan 03 '19 at 12:23
  • Do you actually create a new pool each time you want to create a new connection? That would most likely be an issue. You should create the pool only _once_ and then retrieve a connection from it. – Psi Jan 12 '19 at 15:14
  • What is the result of SELECT @@max_connections; Your connectionLimit : 1000 is asking for up to 1000. Make your upper limit 10% of max_connections to allow the server to get things completed before piling on more work. – Wilson Hauck Jan 16 '19 at 19:22

0 Answers0