Hello I'm using the mssql
library to insert some data into an SQL Server DB, the call is being executed inside a queue so if it fails it will retry in 1 min * retryAttemp
up until 5 attempts
however I keep getting this weird behavior where I get this error message:
ERROR: Error inserting into BASIS OLAP TransactionError: Can't acquire connection for the request. There is another request in progress.
and I don't know why this is happening... this is my code:
static async insertPresale(req, res) {
logger.info('Inserting new Pre-Sale');
const { CAB, DET } = req.body.basisOlapStructure;
const { country } = req.body;
if (country.toLowerCase() !== 'cl') return res.status(404).json({ success: false, message: 'Other countries are not currently supported' });
let pool;
let transaction;
try {
pool = await Databases.ClSQLServerPreventa;
if (!pool) throw new errors.UNEXPECTED_ERROR({ message: 'Error inserting pre-sale in BASIS OLAP. Could not get a connection pool' });
transaction = new mssql.Transaction(pool);
await new Promise((resolve, reject) => transaction.begin(err => (err ? reject(err) : resolve())));
const request = new mssql.Request(transaction);
logger.info('Inserting data into PVRCabecera');
let queryStr = msql.insert().into(bTables.PVRCabecera.toString()).setFields(CAB);
logger.info(queryStr.toString());
let result = await request.query(queryStr.toString());
if (result.rowsAffected > 0) logger.info('Data successfully inserted into PVRCabecera');
logger.info('Inserting data into PVRDetalle');
const queries = DET.map((d) => {
queryStr = msql.insert().into(bTables.PVRDetalle.toString()).setFields(d);
logger.info(queryStr.toString());
return request.query(queryStr.toString());
});
result = await Promise.all(queries);
if (result[0].rowsAffected > 0) logger.info('Data successfully inserted into PVRDetalle');
await transaction.commit();
logger.info('BASIS_OLAP Transaction completed successfully');
return res.status(201).json({ success: true });
} catch (error) {
logger.error(`Error inserting into BASIS OLAP ${error}`);
if (transaction) {
await transaction.rollback();
}
logger.info('Transaction has been rolled back');
throw new errors.UNEXPECTED_ERROR({ message: `Error inserting pre-sale in BASIS OLAP. ${error.toString()}` });
}
}
what can I do to solve this problem?