Ardi
Reputation: 161
Knex is returning Deadlock Detected
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.
Upvotes: 0
Views: 2577
Answers (2)
Ardi
Reputation: 161
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.
Upvotes: 1
Mikael Lepistö
Reputation: 19718
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.
Upvotes: 3
Related Questions
- Knex Error: Transaction query already complete, run with DEBUG=knex:tx for more info
- Knex and Postgress connections
- Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call? Best practices for using Knex.Transaction
- Knex: Timeout acquiring a connection
- Knex.js won't connect to postgres
- Undefined db connection with knex
- Knex Connection issue? EAI_AGAIN
- Make KnexJS Transactions work with async/await
- Knex.js postgresql database connection
- knex transaction expecting a then