Request timeouts in mssql/msnodesqlv8 when using default Pool size

Question

I am switching from mssql (Tedious) to mssql/msnodesqlv8, because we need Windows Authentication support in our Node.js service. However after switching, I run into the following issue.

We have a test that checks if there are no race conditions while getting a new id from the database. To make this operation atomic a TABLOCKX is used inside a transaction. The tests starts 100 promises in parallel and checks if 100 unique numbers are generated in the end.

This test succeeds without any issues when using the mssql (Tedious) driver. However, using mssql/msnodesqlv8, the test fails with the default pool size of 10. (it fails with request timeout errors)

When I set the pool size to 4, the test succeeds. Any bigger pool size will fail. I tried to reduce the code to the simplest version possible, to make sure this issue is not related to my own code.

• Can you please tell me if I am doing anything wrong ?
• Am I correct to assume that: if this works with mssql, it should also work with mssql/msnodesqlv8 ?
• Could this be a bug in either the mssql wrapper or the underlying msnodesqlv8 driver ?

Thanks in advance for any help!

The related table is defined in SQL as follows:

CREATE TABLE [dbo].[un_reportlayoutno](
    [NextId] [int] NOT NULL
  ) ON [PRIMARY] 

The code for the test:

const sql: any = require('mssql/msnodesqlv8');

function getConfig(): config {
    return {
      database: 'EXPR4MSDE_DEV',
      server: 'X10573',
      port: undefined,
      connectionTimeout: 15000,
      requestTimeout: 2000,
      pool: {
        min: 1,
        max: 4
      },
      options: {
        encrypt: false,
        trustedConnection: true,
        instanceName: 'MSDE4EXPR',
        useUTC: false
      }
    };
  }
  async function giveNextId(cp: ConnectionPool): Promise<number> {
    const getQuery = `SELECT un_reportlayoutno.NextId FROM un_reportlayoutno WITH (TABLOCKX)`;
    const updateQuery = (un: number) => `UPDATE un_reportlayoutno SET NextId = ${un+1}`;
    const trans = cp.transaction();
    await trans.begin();
    try {
      const r = await trans.request().query(getQuery);
      const result = r.recordset[0].NextId as number;
      await trans.request().query(updateQuery(result));
      await trans.commit();
      return result;
    }
    catch (err) {
      await trans.rollback();
      throw err;
    }
  }
  test("unique numbers hammering simplified", async () => {
    const cp: ConnectionPool = new sql.ConnectionPool(getConfig());
    await cp.connect();
    const iterations = new Array(100).fill(0);
    try {
      const nrs = await Promise.all(iterations.map(_ => giveNextId(cp)));
      const check = new Set<number>();
      nrs.forEach((n) => check.add(n));
      expect(check.size).toBe(nrs.length);
    }
    finally {
      await cp.close();
    }
  }, 60000);

Software versions

• NodeJS: v16.17.1
• node-mssql: 10.0.2
• msnodesqlv8: 4.1.2
• SQL Server: Microsoft SQL Server 2012 (SP3-GDR)