javascript - 如何使用异步模块在nodejs中实现回滚？

Question

自从过去 2 个月以来，我一直在研究 nodejs，这是我在处理异步模块时遇到的问题。有时在一些错误异步错误调用错误从异步任务的中间。考虑这段代码:

var mysql = require('mysql');
var async = require('async');

var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test',
  password: '12345'
});

var chairArr = ['Red', 'Green', 'Yellow', 'Voilet', 'Brown'];
var inventoryName = "Chairs";
addInventory(inventoryName, chairArr, function(err, result) {
  if(err) {
    console.log("Err : "+err);
  }
  console.log("Successfully added an inventory");
});


function addInventory(inventoryName, chairArr, callback) {
  var sqlQuery = "INSERT INTO tb_inventory(name, added_on) VALUES( ?, NOW())";
  connection.query(sqlQuery, [inventoryName], function(err, result) {
    if(err) {
      return callback(err, null);
    }
    var inventoryId = result.insertId;
    var tasks = [];
    for(var i = 0; i < chairArr.length; i++) {
      tasks.push(addChair.bind(null, inventoryId, chairArr[i]));
    }
    async.parallel(tasks, function(taskErr, taskRes) {
      if(taskErr) {
        return callback(taskErr, null);
      }
      callback(null, result);
    });
  });
}

function addChair(inventoryId, chairColor, callback) {
  var sqlQuery = "INSERT INTO tb_chairs(inventory_id, color) VALUES(?, ?)";
  connection.query(sqlQuery, [inventoryId, chairColor], function(err, result) {
    if(err) {
      return callback(err, null);
    }
    callback(null, result);
  });
}

这个简单的程序只是插入两个表 tb_inventory 和 tb_chairs。我的问题是:有时由于来自前端的参数或我的错误查询没有执行并且异步并行调用错误回调。有没有办法回滚(即删除表中所有插入的条目。)谁能告诉我如何在错误回调中实现它(即跟踪所有插入的 ID 并删除它们？)。我们可以在全局范围内使用 mysql 事务吗？

Answer 1

每当使用一系列函数时，我们都可以从池中获取本地连接，并在异步调用期间调用的函数中传递该本地连接。可以在异步调用的回调中处理提交或回滚。

var mysql = require('mysql');
var async = require('async');

var dbPool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  password: '12345',
  connectionLimit: 10
});

//var chairArr = ['Red', 'Green!++++++++++++++++!!!!!!!!!!!!!!!!!!!!!!!!!!!!', 'Yellow', 'Voilet', 'Brown']; //Case where query fails due to VARCHAR(20) limit
var chairArr   = ['Red', 'Green', 'Yellow', 'Voilet', 'Brown'];
var inventoryName = "SOME ITEM ";
dbPool.getConnection(function(conErr, localConnection) {
    localConnection.beginTransaction(function(transactionErr) {
      if(transactionErr)  {
          console.log("There was some error in begining transaction");
          return;
      }
      addInventory(localConnection, inventoryName, chairArr, function(err, result) {
        if(err) {
          console.log("Err : "+err);
          return;
        }
        else {
          console.log("Successfully added an inventory");
        }
      });
    });
});


function addInventory(localConnection, inventoryName, chairArr, callback) {
  var sqlQuery = "INSERT INTO tb_inventory(name, added_on) VALUES( ?, NOW())";
  localConnection.query(sqlQuery, [inventoryName], function(err, result) {
    if(err) {
      return callback(err, null);
    }
    var inventoryId = result.insertId;
    var tasks = [];
    for(var i = 0; i < chairArr.length; i++) {
      tasks.push(addChair.bind(null, localConnection, inventoryId, chairArr[i]));
    }
    async.parallel(tasks, function(err, asyncRes) {
      if(err) {
        rollback(localConnection, function(rollErr, rollRes) {
          return callback(err, null);
        });
      }
      else {
        localConnection.commit(function(commitErr, commitRes) {
          console.log("transaction succeded");
          return callback(null, "Success");
        });
      }
    });
  });
}

function addChair(localConnection, inventoryId, chairColor, callback) {
  var sqlQuery = "INSERT INTO tb_chairs(inventory_id, color) VALUES(?, ?)";
  localConnection.query(sqlQuery, [inventoryId, chairColor], function(err, result) {
    if(err) {
      return callback(err, null);
    }
    callback(null, result);
  });
}

function rollback(localConnection, callback) {
  localConnection.rollback(function(err, result) {
    if(err) {
      console.log("ROLLBACK Failed");
      return callback(err, null);
    }
    console.log("ROLLBACK successful!");
    callback(null, result);
  });
}

希望对你有帮助。