Create nested Transaction in mssql using NodeJS

Question

I am trying to create nested transactions in NodeJs using MSSQL NPM. I am using following code block

var sqlModule = require("mssql");

var sqlManagerClass = require("./sqlManager.js");

var sql = new sqlManagerClass(sqlModule);
sql.setConfig({
  user: "dbo",
  password: "***********",
  server: "127.0.0.1",
  database: "dbname",
  requestTimeout: 120000,
});

sql.beginTransaction(function (transaction) {
  if (transaction == null) {
    callback("Unable to Start Transaction", null);
    return;
  }
  sql.beginTransaction(function (newTransaction) {
    if (newTransaction == null) {
      callback("Unable to Start newTransaction", null);
      return;
    }
    sql.execute(
      "dbname.dbo.insert_dest",
      [],
      [],
      function (err, results) {
        console.log(results);
        newTransaction.commit(function (err) {
          if (err) {
            console.log(err);
          }
          sql.execute("dbname.dbo.select_dest", [], [], function (
            err,
            results2
          ) {
            if (err) {
              console.log(err);
              return;
            }
            console.log(results2);
            transaction.rollback(function (rollBackErr) {
              if (rollBackErr) {
                console.log(rollBackErr);
              }
              sql.execute("dbname.dbo.select_dest", [], [], function (
                err,
                results2
              ) {
                if (err) {
                  console.log(err);
                  return;
                }
                console.log(results2);
                console.log('end')
              });
            });
          });
        });
      },
      newTransaction
    );
  });
});

creating transaction

this.beginTransaction = function(callback, transaction) {


        // if the optional transaction argument is passed in, we are already working with a transation, just return it
        if (typeof transaction !== "undefined") {
            callback(transaction);
            return;
        }
        self.connectSql(function(err) {

            var transaction = new self.sql.Transaction(self.connection);

            transaction.begin(function(err) {
                // ... error checks 
                if (err) {
                    self.log("SQLManager - Error Beginning Transaction " + err);
                    callback(null);
                }

                // callback with the transaction handler
                callback(transaction);
            });
        });
    }

I have to create 2 transactions so that I can start a transaction and perform a set of operations on different stored procedures id any thing goes wrong I can revert back to my original state using the first transaction.

In short I am trying to achieve something like following SQL code

BEGIN TRAN T1;
BEGIN TRAN M2
INSERT INTO dbo.dest
(
    Code,
    Text,
    Type
)
VALUES
(   'l', -- Code - varchar(50)
    'Love', -- Text - varchar(50)
    2   -- Type - int
    )
COMMIT TRAN M2
// I am calling another Sp which is similar to this
SELECT * FROM dbo.dest
//validation the code 
//if error
ROLLBACK TRAN T1

My question is how can I create nested transaction so that I can revert back entire transaction if any error occur after the first transaction complete. Thank you.

David Browne - Microsoft · Accepted Answer · 2020-05-06 19:41:22Z

2

First read this

A SQL Server DBA myth a day: (26/30) nested transactions are real

To see if SQL Server "nested transactions" or savepoints are really going to help you.

Then you'll probably have to handle this all yourself by issuing the transaction control statements using sql.execute() eg:

sql.execute("begin transaction");
//something that works
sql.execute("save transaction MyTran");
//something that fails
sql.execute("rollback transaction MyTran");
sql.execute("commit transaction");

how can I create nested transaction so that I can revert back entire transaction if any error occur after the first transaction complete

What you have will do that. Nested transactions are not real, so calling

begin tran
...
begin tran --<-- @@trancount += 1, but nothing else really happens
...
commit tran --<--nothing is really commited yet. @@trancount -= 1
...
rollback tran --<-- all work is rolled back

edited May 6, 2020 at 19:41

answered May 6, 2020 at 18:51

David Browne - Microsoft

90.8k7 gold badges53 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3510665 Over a year ago

Can I Rollback entire transaction with out rolling back the saved transaction??

David Browne - Microsoft Over a year ago

No. That would be an "autonomous transaction", and SQL Server doesn't support that. Perhaps you could add to your question some additional information about what you're really trying to achieve.

user3510665 Over a year ago

In short I am trying to achievesomething like this BEGIN TRAN T1; BEGIN TRAN M2 INSERT INTO dbo.dest ( Code, Text, Type ) VALUES ( 'l', -- Code - varchar(50) 'Love', -- Text - varchar(50) 2 -- Type - int ) COMMIT TRAN M2 // I am calling another Sp which is similar to this SELECT * FROM dbo.dest //validation the code //if error ROLLBACK TRAN T1

user3510665 Over a year ago

If I have multiple transactions, which are doing operations on same db, will it save them as well or it will apply the save point on current transaction.

David Browne - Microsoft Over a year ago

There is only ever one transaction per session.

Collectives™ on Stack Overflow

Create nested Transaction in mssql using NodeJS

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related