CODEWITHSUNDEEP
node.jssql-server
user3510665
user3510665

Reputation: 218

Create nested Transaction in mssql using NodeJS

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.

Upvotes: 0

Views: 635

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89256

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

Upvotes: 2

Related Questions