CODEWITHSUNDEEP
sqlsql-servernode.jsdatabasetedious
Paulo Black
Paulo Black

Reputation: 310

Tedious MSSQL driver, how to set transaction level to READ_COMMITTED_SNAPSHOT

Are you able to specify transaction isolation level of READ_COMMITTED_SNAPSHOT through the Tedious driver? I see the available isolation levels here: http://tediousjs.github.io/tedious/api-connection.html#function_beginTransaction

But I understand that the SNAPSHOT option in READ COMMITTED is not the same as setting the isolation level to SNAPSHOT. That said, I'm not seeing a way of setting the transaction level to this without appending it to every query that comes in, which I'd obviously rather not do.

Thanks in advance for any help!

Upvotes: 0

Views: 1383

Answers (2)

gil.fernandes
gil.fernandes

Reputation: 14621

Yes, as of 2021 you seem to be able to do so. In this post I am referring to this version of the Node MSSQL driver:

"mssql": "^6.3.1"

See https://www.npmjs.com/package/mssql

There are five possible isolation levels when you import 'mssql/lib/isolationlevel':

module.exports = {
  READ_UNCOMMITTED: 0x01,
  READ_COMMITTED: 0x02,
  REPEATABLE_READ: 0x03,
  SERIALIZABLE: 0x04,
  SNAPSHOT: 0x05
}

Here is a possible way to to specify transaction isolation level of READ_COMMITTED_SNAPSHOT or any other isolation level through the Tedious driver for a connection, which you retrieve from a connection pool:

  • You import the isolation level from mssql/lib/isolationlevel:

    import { READ_UNCOMMITTED } from 'mssql/lib/isolationlevel'

  • We assume that you have a connection pool create something like this:

    export const poolPromise = new sql.ConnectionPool(dbConfig).connect();

  • Then after each connection retrieval you can set the isolation level by changing the configuration options of the connection:

      export async function executeSelectQuery(sqlQuery) {
      console.log('executeSelectQuery', sqlQuery)
      const conn = await createConn();
      conn.config.options.connectionIsolationLevel = READ_UNCOMMITTED
      const res = await conn.request().query(sqlQuery);
      return res ? res.recordset : [];
  }

I have used this code to verify, if indeed the isolation level was changed:

export async function executeSelectQuery(sqlQuery) {
    console.log('executeSelectQuery', sqlQuery)
    const conn = await createConn();
    conn.config.options.connectionIsolationLevel = READ_UNCOMMITTED
    const isolationLevelRes = await conn.request().query(`
    begin
        DBCC USEROPTIONS
    end
    `)
    console.log('isolationLevelRes after', isolationLevelRes.recordsets)
    const res = await conn.request().query(sqlQuery);
    return res ? res.recordset : [];
}

Indeed I could see this entry on the console:

enter image description here

Upvotes: 1

David Cram
David Cram

Reputation: 778

I don't know anything about Tedious. However, in SQL Server READ_COMMITTED_SNAPSHOT isn't a transaction isolation level. READ_COMMITTED_SNAPSHOT is a database option for READ_COMMITTED. So you would need to do something like:

SET READ_COMMITTED_SNAPSHOT ON

and then 

SET TRANSACTION ISOLATION LEVEL READ_COMMITTED

Upvotes: 1

Related Questions