Knex is returning Deadlock Detected

Question

So I have this function that will decrease a stock on the database, the problem is, Knex is returning a 'Deadlock Detected' if I have to subtract quantities of 2 different items, hence the loops on the code.

const updateOrder = (req, res, db, logger) => {
    let {
        status,
        trx_id,
        orNumber,
        returnReason
    } = req.body;
    db.transaction((trx) => {
            db('cart')
                .returning('*')
                .where('trx_id', '=', trx_id)
                .update({
                    status: status,
                    or_num: orNumber,
                    return_reason: returnReason
                })
                .then(order => {
                    if (status === 'Success') {
                        db('cart')
                            .returning('*')
                            .where('trx_id', '=', trx_id)
                            .update({
                                date_purchased: new Date()
                            })
                            .then(purchased => {
                                db.transaction((trx) => {
                                    db.select('*').from('cart_item').where({
                                            cart_id: order[0].id,
                                            trx_id: order[0].trx_id
                                        })
                                        .then(item => {
                                            let newQuantity = [];
                                            if (item[0]) {
                                                for (let i = 0; i < item.length; i++) {
                                                    db.select('*').from('product').where('item_code', '=', item[i].item_code)
                                                        .then(product => {
                                                            for (let ii = 0; ii < product.length; ii++) {
                                                                if (product[ii]) {
                                                                    newQuantity[ii] = parseInt(product[ii].stock) - parseInt(item[i].quantity)
                                                                    db('product')
                                                                        .returning('*')
                                                                        .where('item_code', '=', item[i].item_code)
                                                                        .update({
                                                                            stock: newQuantity[ii]
                                                                        })
                                                                        .then(stock => {
                                                                            if (stock[0]) {
                                                                                db('activity_order_logs')
                                                                                    .returning('*')
                                                                                    .insert({
                                                                                        date: new Date(),
                                                                                        employee_id: req.session.emp_id,
                                                                                        module: "MONITORING",
                                                                                        trx_id: trx_id,
                                                                                        activity: status,
                                                                                        or_num: orNumber
                                                                                    })
                                                                                    .then(activity => {
                                                                                        if (activity[0]) {
                                                                                            res.json({
                                                                                                isSuccess: true
                                                                                            });
                                                                                            return;
                                                                                        } else {
                                                                                            res.json({
                                                                                                isSuccess: false
                                                                                            });
                                                                                            return;
                                                                                        }
                                                                                    })
                                                                                    .then(trx.commit)
                                                                                    .catch(err => {
                                                                                        logger.error(err);
                                                                                        trx.rollback;
                                                                                        res.render('pages/error-500');
                                                                                    });
                                                                            }
                                                                        })
                                                                        .then(trx.commit)
                                                                        .catch(err => {
                                                                            logger.error(err);
                                                                            trx.rollback;
                                                                            res.render('pages/error-500');
                                                                        });
                                                                }
                                                            }
                                                        })
                                                        .then(trx.commit)
                                                        .catch(err => {
                                                            logger.error(err);
                                                            trx.rollback;
                                                            res.render('pages/error-500');
                                                        });
                                                }
                                            }
                                        })
                                        .then(trx.commit)
                                        .catch(err => {
                                            logger.error(err);
                                            trx.rollback;
                                            res.render('pages/error-500');
                                        });
                                    })
                                    .catch(err => logger.error(err));
                            })
                            .catch(err => logger.error(err));
                    } else if (status === 'Returned'){
                        if (order[0]) {
                            db('activity_order_logs')
                                .returning('*')
                                .insert({
                                    date: new Date(),
                                    employee_id: req.session.emp_id,
                                    module: "MONITORING",
                                    trx_id: trx_id,
                                    activity: status,
                                    or_num: orNumber,
                                    return_reason: returnReason
                                })
                                .then(activity => {
                                    if (activity[0]) {
                                        res.json({
                                            isSuccess: true
                                        });
                                        return;
                                    } else {
                                        res.json({
                                            isSuccess: false
                                        });
                                        return;
                                    }
                                })
                                .then(trx.commit)
                                .catch(err => {
                                    logger.error(err);
                                    trx.rollback;
                                    res.render('pages/error-500');
                                });
                        }
                    }
                    else {
                        if (order[0]) {
                            db('activity_order_logs')
                                .returning('*')
                                .insert({
                                    date: new Date(),
                                    employee_id: req.session.emp_id,
                                    module: "MONITORING",
                                    trx_id: trx_id,
                                    activity: status,
                                    or_num: orNumber
                                })
                                .then(activity => {
                                    if (activity[0]) {
                                        res.json({
                                            isSuccess: true
                                        });
                                        return;
                                    } else {
                                        res.json({
                                            isSuccess: false
                                        });
                                        return;
                                    }
                                })
                                .then(trx.commit)
                                .catch(err => {
                                    logger.error(err);
                                    trx.rollback;
                                    res.render('pages/error-500');
                                });
                        }
                    }
                })
                .then(trx.commit)
                .catch(err => {
                    logger.error(err);
                    trx.rollback;
                    res.render('pages/error-500');
                });
        })
        .catch(err => logger.error(err));
}

module.exports = {
    updateOrder
}

I'm fairly new to NodeJS and I know that I have a promise hell in my code as I can't flat the promises because I have to use the data of the previous promise to the next promise.

Code is working fine if I just have to use one update query, but encounters deadlock when I have to use two.

You have to convert your code to async/await. At the moment it's unreadable. Converting it to async/await will make it readable and is easy to do. The loop is also an issue. With SQL you don't have to use loops, as it's based on sets. — Mika Sundland, May 09 '19 at 11:04

score 3 · Answer 1 · answered May 09 '19 at 12:47

You should start by learning how to use promises and async/await and do something more simple with them. There are so many errors in handling promises that I lost the count. Effectively you are executing lots of stuff parallel without waiting that earlier query is ready.

Knex is returning Deadlock Detected means that you have done queries to the DB concurrently in a way that they are locking each others execution.

score 1 · Accepted Answer · answered May 11 '19 at 08:29

With the help of many research, I finally got rid of Deadlock error, and at the same time, flattened the code.

Here's the working code:

const updateOrder = (req, res, db, logger) => {
    let {
        status,
        trx_id,
        orNumber,
        returnReason
    } = req.body;

    const updateStatus = () => {
        return db('cart')
                .returning('*')
                .where('trx_id', '=', trx_id)
                .update({
                    status: status,
                    or_num: orNumber,
                    return_reason: returnReason
                });
    }

    const updateDate = () => {
        return db('cart')
            .returning('*')
            .where('trx_id', '=', trx_id)
            .update({
                date_purchased: new Date()
            });
    }

    const selectItems = (order) => {
        return db
            .select('*')
            .from('cart_items')
            .where({
                cart_id: order.id,
                trx_id: order.trx_id
            });
    }

    const selectProduct = (item) => {
        const queries = [];
        item.forEach(item => {
            const query = db.select('*')
            .from('product')
            .where('item_code', '=', item.item_code);
            queries.push(query);
        })
        return Promise.all(queries);
    }

    const updateQuantity = (product, cart) => {
        const prodQuantity = product.map(product => parseInt(product.stock));
        const cartQuantity = cart.map(cart => parseInt(cart.quantity));
        const newQuantity = [];
        const queries = [];
        for(let i = 0; i < product.length; i++){
            newQuantity.push(prodQuantity[i] - cartQuantity[i]);
        }
        cart.map((cart, index) => {
            const query = db('products')
            .returning('*')
            .where('item_code', '=', cart.item_code)
            .update({
                stock: newQuantity[index]
            })
            queries.push(query);
        })
      return queries;
    }

    const updateLogs = () => {
        return db('activity_order_logs')
            .returning('*')
            .insert({
                date: new Date(),
                employee_id: req.session.emp_id,
                module: "MONITORING",
                trx_id: trx_id,
                activity: status,
                or_num: orNumber
            })
    }

    const sendResponse = (result) => {
        if (result) {
            res.json({
                isSuccess: true
            });
            return;
        } else {
            res.json({
                isSuccess: false
            });
            return;
        }
    }

    (async () => {
      const first = await updateStatus();
      // console.log(first);
      if(first[0].status == 'Success'){
        const second = await updateDate().catch(err => {
            throw err
        });
        // console.log(second); 
        const third = await selectItems(second[0]).catch(err => {
            throw err
        });
        // console.log(third);
        const fourth = await selectProduct(third).catch(err => {
            throw err
        });
        const fourth2 = [].concat(...fourth);
        // console.log(fourth2);
        const fifth = await updateQuantity(fourth2, third)
        const decreaseStock = async () => {
            const finalResult = [];
        for (let i = 0; i < fifth.length; i++) {
                const finalQuery = await Promise.resolve(fifth[i]);
                finalResult.push(finalQuery);
            }
                return finalResult;
          };

          const result = await decreaseStock().catch(err => {
            throw err
          });
          const result2 = [].concat(...result);
          const logs = await updateLogs().catch(err => {
            throw err
          });
          const sendRes = await sendResponse(logs);

      } else if(first[0].status == 'Returned'){
        const logs = await updateLogs().catch(err => {
            throw err
        });
          const sendRes = await sendResponse(logs);
      } else {
        const logs = await updateLogs().catch(err => {
            throw err
        });
          const sendRes = await sendResponse(logs);
      }
    })().catch(err => {
            console.log(err);
            res.json({ isSuccess: false })
        });
}

module.exports = {
    updateOrder
}

I have a new issue, specifically with transactions, but will post it into another question.

It looks much better now. You can also use try-catch to get rid of `.catch()`. Like [this](https://pastebin.com/tWBaQijw) (untested). — Mika Sundland, May 11 '19 at 09:59
@MikaS should I still wrap the try in IIFE? Also here's my follow up question. https://stackoverflow.com/questions/56088660/make-knexjs-transactions-work-with-async-await — Ardi, May 11 '19 at 23:00

Knex is returning Deadlock Detected

2 Answers2