1

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?

Carlos Delgado
  • 159
  • 1
  • 2
  • 15

0 Answers0