ROLLBACK doesn't rollback transaction [NodeJS, MySQL]

Question

connection.query(`START TRANSACTION;`, async function (err) {
  if (err) {
    req.flash("flash", "Something went wrong while deleting. Try again.");
    return res.redirect("back");
  } else {
    await connection.query(
      `INSERT INTO... ; SELECT LAST_INSERT_ID();`,
      async (error, results1) => {
        if (error) {
          await connection.query(`ROLLBACK;`, function (err) {
            req.flash("flash", "There was an error while posting.");
            return res.redirect("/post");
          });
        } else {
          var post_id = await results1[0].insertId;
          await connection.query(
            `INSERT INTO...`,
            [],
            async (error, results) => {
              if (error) {
                await connection.query(`ROLLBACK;`, function (err) {
                  req.flash("flash", "There was an error while posting.");
                  return res.redirect("/post");
                });
              } else {
                await connection.query(
                  `INSERT INTO...`,
                  async (error, results) => {
                    if (error) {
                      await connection.query(`ROLLBACK;`, function (err) {
                        req.flash("flash", "There was an error while posting.");
                        return res.redirect("/post");
                      });
                    }
                    await connection.query(`ROLLBACK;`, function(err){
                    req.flash("flash", "Went through...");
                    return res.redirect("back");
                 })
         ...
});

First I start the transaction. Then I have the code in the middle to insert data into 3 tables. I wanted to test the rollback which is supposed to undo everything after START TRANSACTION but it didn't do anything. What did I do wrong?

(The code works by itself so please ignore if I missplaced any brackets).

@Barmar Yep you get the message. The problem is only that it doesn't undo the inserts. — Janez Kranjski, Dec 29 '21 at 23:06
It your table actually transactional (InnoDB, and not MyISAM/Aria)? Look at `show create table {tablename}`. — danblack, Dec 30 '21 at 01:59

score 3 · Answer 1 · answered Dec 30 '21 at 10:15

The problem was that I was using a pool directly. You need to get a new connection and then use that to make all the queries and transactions. That is also the reason beginTransaction wasn't working for me before. What I should have done is:

connection.getConnection(function(err, con) {
    con.query...
    con.beginTransaction()...
    con.query..
    con.rollback()...
})

score 0 · Answer 2 · answered Jul 22 '22 at 11:05

You can check the multiple-transaction-manager library to manage your transaction.

https://www.npmjs.com/package/@multiple-transaction-manager/mysql

Example;

// init manager & context
const txnMngr: MultiTxnMngr = new MultiTxnMngr();
const mysqlContext = new MysqlDBContext(txnMngr, pool);

// Add first step
mysqlContext.addTask("DELETE FROM test_table");

// Add second step
mysqlContext.addTask("INSERT INTO test_table(id, name) VALUES (:id, :name)", { "id": 1, "name": "Dave" });

// Add third step
mysqlContext.addTask("INSERT INTO test_table(id, name) VALUES (:id, :name)", { "id": 2, "name": "Kevin" });

// jest test
await expect(txnMngr.exec()).resolves.not.toBeNull();

ROLLBACK doesn't rollback transaction [NodeJS, MySQL]

2 Answers2