我正在尝试使用MSSQL NPM在NodeJ中创建嵌套事务。我正在使用以下代码块
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
);
});
});
创建事务
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);
});
});
}
我必须创建两个事务,这样我就可以启动一个事务并对不同的存储过程执行一组操作。如果出现任何问题,我都可以使用第一个事务恢复到原始状态。
简而言之,我正在尝试实现类似以下SQL代码的功能
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
我的问题是如何创建嵌套事务,以便在第一个事务完成后出现任何错误时可以恢复整个事务。非常感谢。
首先阅读此
SQL Server DBA每天的神话:(26/30(嵌套事务是真正的
看看SQL Server的"嵌套事务"或保存点是否真的会对您有所帮助。
然后,您可能需要通过使用sql.execute()
发布事务控制语句来处理这一切,例如:
sql.execute("begin transaction");
//something that works
sql.execute("save transaction MyTran");
//something that fails
sql.execute("rollback transaction MyTran");
sql.execute("commit transaction");
如果第一个事务完成后出现任何错误,我如何创建嵌套事务,以便恢复整个事务
你所拥有的将做到这一点。嵌套的事务不是真实的,因此调用
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