Transaction not rolling back as expected using the mssql module in Node.js

Question

I actually found the answer to this question already, and just want to document my finding.

Here's what causes the problem:

let transaction = new sql.Transaction(pool);
await transaction.begin();
await pool.request (transaction).query (" *** SOME SQL *** ");
await transaction.rollback();

The transaction won't roll back at all.

Answer 1

To fix the problem, I ended up using new sql.Request (transaction) instead of pool.request (transaction). The codes will look like the following:

let transaction = new sql.Transaction(pool);
await transaction.begin();
await new sql.Request (transaction).query (" *** SOME SQL *** ");
await transaction.rollback();

I did not dig deeper to find out the actual mechanism of how the connection pool works, but it appears that unlike the sql.Request class constructor, the pool.request method does not take a transaction parameter (someone who has the knowledge please tell us if this is true).

UPDATE

I went back and check the mssql module's source codes and found that there is a request method in the Transaction class, so another solution is to do:

let transaction = new sql.Transaction(pool);
await transaction.begin();
await transaction.request().query (" *** SOME SQL *** ");
await transaction.rollback();