MySQL Transactions locking on NodeJS with mysql2/promise

Question

I'm currently using MySQL for a huge project that involves many tables and we are using NodeJS for the backend. We chose MySQL because of the highly relational nature of the data.

I've used MySQL and transactions before and also NodeJS before but never both together.

So we settled on the mysql2/promise package since it supports async/await. However, transactions are ALWAYS locking when dealing with many tables and it is very annoying. I can't seem to figure out for to get it working. As we are ways in the project, changing the library is not an option. We would like to maintain the library but find a way to get it working, hence I'm here hoping someone can shed some light for me on this.

The code is below. It's split into 2 files. One is the database.js and the other is a service file where we execute and run everything called service.js

This is the database.js file that we use to initiate the connection.

// database.js
const mysql = require("mysql2/promise");

const pool = mysql.createPool({
  port: process.env.DB_PORT,
  host: process.env.DB_HOST,
  user: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  connectionLimit: 100,
});

module.exports = pool;
This is the service.js file that we are running the function from.

// service.js
const db = require("../../config/database");

module.exports = {
  save: async (data, callBack) => {
    try {
      await db.query(
        "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
      );
      await db.query("START TRANSACTION");

      try {
        await db.query(`UPDATE QUERY FOR TABLE A`, [myParams]);

        await db.query(`DELETE QUERY FOR SOME DATA ON TABLE B`, [myParam]);
        await db.query(`DELETE QUERY FOR SOME DATA ON TABLE C`, [myParam]);
        await db.query(`DELETE QUERY FOR SOME DATA ON TABLE D`, [myParam]);
        await db.query(`DELETE QUERY FOR SOME DATA ON TABLE E`, [myParam]);
        await db.query(`DELETE QUERY FOR SOME DATA ON TABLE F`, [myParam]);
        await db.query(`DELETE QUERY FOR SOME DATA ON TABLE G`, [myParam]);

        for (var b = 0; b < data.b.length; b++) {
          await db.query(`INSERT QUERY FOR TABLE B`, [myParams]);
        }
        for (var c = 0; c < data.c.length; c++) {
          await db.query(`INSERT QUERY FOR TABLE C`, [myParams]);
        }
        for (var d = 0; d < data.d.length; d++) {
          await db.query(`INSERT QUERY FOR TABLE D`, [myParams]);
        }
        for (var e = 0; e < data.e.length; e++) {
          await db.query(`INSERT QUERY FOR TABLE E`, [myParams]);
        }
        for (var f = 0; f < data.f.length; f++) {
          await db.query(`INSERT QUERY FOR TABLE F`, [myParams]);
        }
        for (var g = 0; g < data.g.length; g++) {
          await db.query(`INSERT QUERY FOR TABLE G`, [myParams]);
        }

        await db.query("COMMIT");
        console.log("NEXT");
        return callBack(null, someReturnData);
      } catch (err) {
        console.log("Error");
        await db.query("ROLLBACK");
        console.log("Rollback successful");
        console.log(err);
        return callBack(err);
      }
    } catch (err) {
      console.log("Error");
      await db.query("ROLLBACK");
      console.log("Rollback successful");
      console.log(err);
      return callBack(err.code);
    }
  },
};

This is a use case where we needed to clear all the data of a few tables before we insert the new data hence we used it in this way. And since the data is quite huge, we are looping it to insert multiple at the same time. However, this just causes a lock or freezes.

I've tried changing SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to everything else there is or even just removed it entirely. It always causes more issues.

Anyone who has had issues, please do assist in helping to smoothen this issue out. NodeJS and MySQL has been great but this particular issue with the mysql2/promise package is driving us nuts. The async/await nature of it might be causing us to have issues where we can't insert in since the previous one hasn't completed and that is just raising exponentially till we hit a timeout.

Thanks and Cheers!

t.niese · Accepted Answer · 2023-02-09 14:52:35Z

With every call of pool.query the query might be executed with another connection of the pool.

So the START TRANSACTION might be executed in an entirely different connection then all of your other commands.

You have to request a connection using getConnection and use that for your entire transaction. And once you finished you have to release it (conn.release();) so that it is put back to the pool and is available again.

So it has to look something like that:

module.exports = {
  save: async (data, callBack) => {
   let connection
    try {
      connection = await db.getConnection()
      await connection.query(
        "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
      );
      await connection.query("START TRANSACTION");

      try {
        await connection.query(`UPDATE QUERY FOR TABLE A`, [myParams]);

        await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE B`, [myParam]);
        await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE C`, [myParam]);
        await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE D`, [myParam]);
        await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE E`, [myParam]);
        await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE F`, [myParam]);
        await connection.query(`DELETE QUERY FOR SOME DATA ON TABLE G`, [myParam]);

        for (var b = 0; b < data.b.length; b++) {
          await connection.query(`INSERT QUERY FOR TABLE B`, [myParams]);
        }
        for (var c = 0; c < data.c.length; c++) {
          await connection.query(`INSERT QUERY FOR TABLE C`, [myParams]);
        }
        for (var d = 0; d < data.d.length; d++) {
          await connection.query(`INSERT QUERY FOR TABLE D`, [myParams]);
        }
        for (var e = 0; e < data.e.length; e++) {
          await connection.query(`INSERT QUERY FOR TABLE E`, [myParams]);
        }
        for (var f = 0; f < data.f.length; f++) {
          await connection.query(`INSERT QUERY FOR TABLE F`, [myParams]);
        }
        for (var g = 0; g < data.g.length; g++) {
          await connection.query(`INSERT QUERY FOR TABLE G`, [myParams]);
        }

        await connection.query("COMMIT");
        console.log("NEXT");
        return callBack(null, someReturnData);
      } catch (err) {
        console.log("Error");
        await connection.query("ROLLBACK");
        console.log("Rollback successful");
        console.log(err);
        return callBack(err);
      }
    } catch (err) {
      console.log("Error");
      await connection.query("ROLLBACK");
      console.log("Rollback successful");
      console.log(err);
      return callBack(err.code);
    }
    connection.release()
  },
};

Collectives™ on Stack Overflow

MySQL Transactions locking on NodeJS with mysql2/promise

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related