NodeJS + mysql: using connection pool leads to deadlock tables

Question

I am using NodeJS and mysql library to access MySQL database.

When I establish single connection and repeatedly use it, it works fine:

global.mysql = mysql_module.createConnection({
  host: config.mysql.host,
  user: config.mysql.user,
  password: config.mysql.password
});

When I use connection pool instead, I get ER_LOCK_WAIT_TIMEOUT errors in transactions.

global.mysql = mysql_module.createPool({    
  host: config.mysql.host,
  user: config.mysql.user,
  password: config.mysql.password,
  database : config.mysql.database,
  connectionLimit : 50
});

Strangely enough, the errors do occur on exactly the same data at exactly the same times. I.e. I have transaction in which I insert in three tables in a row, each time using last inserted ID from previous INSERT statement. With some data this works fine, with some data, the third INSERT produces ER_LOCK_WAIT_TIMEOUT error. When I use single connection in NodeJS, this works fine, so this must be problem related to connection pool.

Any help would be appreciated.

Answer 1

Not a big fan of answering my own questions, but this problem is solved by explicitly creating connection from a pool and using it for every sql statement during transaction

pool.getConnection(function(err, connection) {
  connection.query( 'START TRANSACTION', function(err, rows) {
    // do all sql statements with connection and then
    connection.query( 'COMMIT', function(err, rows) {
      connection.release();
    }       
  });
});