How to commit transaction after loop over asynchonous updates

Question

I'm using node-mysql-queues to handle database transactions in my application.

for (lineitem in lineitems) {
        transaction.query("SELECT n from inventory WHERE productId = ?", [lineitem], function (err, rows) {
            if (err)
                transaction.rollback();
            var newN = rows[0].n - lineitems[lineitem].quantity;
            if (newN >= 0) {
                transaction.query("UPDATE inventory SET n = ? WHERE productId = ?", [newN, lineitem], function (err) {
                    if (err){
                        transaction.rollback();
                        console.log(err);
                    }
                    //here I want to commit if all updates were successfull!!!
                });
            }
        })
    }

As you can see in the code, I don't know how to handle the commit part. If it was synchronous it would be easy, but don't know how ro solve this problem.

Thanks & Regards

score 1 · Answer 1 · answered Aug 14 '13 at 21:12

This is easy with something like the async module.

async.each(lineitems, performQuery, function(err) {
  if(err) {
    transaction.rollback();
    console.log(err);
    return;
  }

  transaction.commit();
});

function performQuery(lineitem, callback) {
  transaction.query("SELECT n from inventory WHERE productId = ?", [lineitem], function (err, rows) {
    if (err) return callback(err);

    var newN = rows[0].n - lineitems[lineitem].quantity;
    if (newN >= 0) {
      transaction.query("UPDATE inventory SET n = ? WHERE productId = ?", [newN, lineitem], function (err) {
        if (err) return callback(err);

        callback();
      });
    }
  });
}

Hi @Timothy and thanks for your hint. Unfortunately I couldn't make it working :( I pasted my code here (http://pastebin.com/quzkN3SN) and marked the lines where I expected output. In addition I debugged the application. For the callback after the select Method it states: "function has inconsistent retrun points"... Any further idea? — NorRen, Aug 15 '13 at 11:07

score 0 · Answer 2 · answered Aug 16 '13 at 08:41

I found a solution for my problem. Since I had problems with doing a select and then an update depending on the result of the select, I implemented something like a conditional update. But see my code:

mysql.getTransaction(function (err, transaction) {
    //For each item in the cart, call the performUpdate method
    //If an error occures, rollback the whole transaction
    async.each(lineitems, performUpdate, function (err) {
        if (err) {
            transaction.rollback();
            res.json(err.message);
            return;
        }
        //Since we are going to call another callback, we need to pause the transaction, else it would be committed automatically
        transaction.pause();
        //If the Updates were successfull, create an Order in MongoDB
        orderController.createMongoOrder(lineitems, req.session.cart.total, req.session.passport.user, function (err) {
            if (err) {
                //If there is a Problem with Mongo, cancel the transaction
                transaction.resume();
                transaction.rollback();
                res.json(err.message);
            } else {
                //Else commit the transaction and empty the cart
                transaction.resume();
                transaction.commit();
                req.session.cart = {
                    products: {},
                    count: 0,
                    total: 0
                };
                res.json("Order accepted!");
            }
        })
    });

    function performUpdate(lineitem, callback) {
        //This query can be seen as conditional update. If the number of articles in stock is not sufficient, there will be no affectedRows in the returned info message
        transaction.query("UPDATE inventory SET n = n -? WHERE productId = ? AND n >= ?", [lineitem.quantity, lineitem.id, lineitem.quantity],function (err, info) {
            if (err) {
                return callback(err);
            } else {
                //if for any item there is no affectedRow, this means the Updated failed. This should make the whole transaction roll back so we return an error to the callback
                if (info.affectedRows != 1) {
                    return callback(new Error("Article: " + lineitem.productObject.name + " out of stock!"))
                }
                return callback(null, info);
            }
        }).execute()
    }
})

How to commit transaction after loop over asynchonous updates

2 Answers2