Operation timed out for an unknown reason

[yasharma]

Hello, if someone can provide help with the following or refer me at correct reference or docs. I'm getting operation timeout error, while executing parallel queries with pool.max = 1, Multiple prepare statements are running under Transactions and parallel prepare statements queries are running, during queries execution if network is down, at that time i'm getting the error operation timed out for the prepare statement query and for transactions getting following error different times

TransactionError: Can't rollback transaction. There is a request in progress TransactionError: Requests can only be made in the LoggedIn state, not the Final state TransactionError: Transaction has not begun. Call begin() first.

Configuration:

const configDB = {
  user: config.sqlDbUsername,
  password: config.sqlDbPassword,
  port: config.sqlDbPort,
  server: config.sqlDbHost,
  database: config.sqlDbName,
  options: {
    enableArithAbort: false,
  },
  connectionTimeout: 15000,
  requestTimeout: 15000,
  pool: {
    max: 1,
  },
};

let _dbClient: Request;
let _pool: sql.ConnectionPool;
let _poolConnect: Promise<sql.ConnectionPool>;

export const setupDB = async () => {
  try {
    _pool = new sql.ConnectionPool(configDB);
    _poolConnect = _pool.connect();
    if (await _poolConnect) {
      _dbClient = new sql.Request(_pool);
      logger.info('Database is ready...');
    }
    _pool.on('error', error => {
      logger.error('Pool Error', error);
    });
  } catch (err) {
    logger.error('Failed to Initialize the socket:', err);
    throw err;
  }
};

Note: setupDB function is called in index.js when nodejs service comes up

export const getSQLClient = () => {
  return _dbClient;
};

export const getConnectionPool = () => {
  return _pool;
};

export const getPoolConnect = () => {
  return _poolConnect;
};

export const executeQuery = (data) => {
    const pool = await getPoolConnect();
    const _request = new PreparedStatement(pool);
    await _request.prepare(query);
    const { recordsets } = await _request.execute(data);
    await _request.unprepare();
    return recordsets;
}

I'm using single connection pool for your application/service as mentioned here (https://github.com/tediousjs/node-mssql#connection-pools)

Software versions

• NodeJS: 10.17.0
• node-mssql: 6.2.0
• SQL Server: 2016 SP2

[dhensby]

I'm a bit confused; you're saying you're running many transactions, in parallel, with prepare statements, etc. with a max pool count of 1.

But if you've got a maximum pool count of 1 and you're trying to run parallel queries / transactions then they will all be in contention with one another to use this 1 connection and will only be able to execute in sequence, waiting for the previous transaction to complete and thus free your connection for use. This stacking of transactions could easily lead to timeouts if they are "kicked off" at the same time but the last one takes some time to actually be able to acquire the single available connection.

[yasharma]

@dhensby Within a single transaction multiple prepare statement queries are running one after another, at the same time parallel normal prepare statement query is running without transaction, at that time if network is done, Error is coming Operation timed out for unknown reason and now after all queries start giving same error until service restart.

The same error also coming with default pool config ie 10 by default as mentioned in docs, however i'm able to reproduce with pool.max = 1 on local machine to troubleshoot the issue.

Let me know if i can provide more information

[dhensby]

network is done

What do you mean by this?

It looks like you've got a problem which I need code examples to reproduce? Or is the problem that your network connection and/or DB server is going away? If that's the case then there's nothing this library can do about that...

[yasharma]

@dhensby I mean to say Network is down,

Or is the problem that your network connection and/or DB server is going away

Yes, the use case is network connection is inconsistence, network can be down at any time, however once network is down driver should established the connection with DB and queries should work as normal, ?

[dhensby]

This library makes no promises to gracefully retry in the instance that the DB server is unavailable. If there's an error on the connection it is your job to retry the operation, it would be too opinionated of the library to do otherwise.

You can use the pool.healthy flag to check if the pool looks healthy (ie: the server is available), or this PR https://github.com/tediousjs/node-mssql/pull/1192 which only releases healthy connections. But if a connection goes bad during a query (ie: once it's been released) because the DB server goes offline, that's on your code to handle.