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.
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.