NodeJS实现MySQL事务处理

1、构建事务处理函数

execTrans(sqlparamsEntities, callback) {
    connection.beginTransaction(function (err) {
        if (err) {
            return callback(err, null);
        }
        console.log("开始执行transaction，共执行" + sqlparamsEntities.length + "条数据");
        var funcAry = [];
        sqlparamsEntities.forEach(function (sql_param) {
            var temp = function (cb) {
                var sql = sql_param.sql;
                var param = sql_param.value;
                connection.query(sql, param, function (tErr, rows, fields) {
                    if (tErr) {
                        connection.rollback(function () {
                            console.log("事务失败，" + sql_param + "，ERROR：" + tErr);
                            throw tErr;
                        });
                    } else {
                        return cb(null, rows);
                    }
                })
            };
            funcAry.push(temp);
        });

        async.series(funcAry, function (err, result) {
            console.log("transaction error: " + err);
            if (err) {
                connection.rollback(function (err) {
                    console.log("transaction error: " + err);
                    // connection.release();
                    return callback(err, null);
                });
            } else {
                connection.commit(function (err, info) {
                    console.log("transaction info: " + JSON.stringify(info));
                    if (err) {
                        console.log("执行事务失败，" + err);
                        connection.rollback(function (err) {
                            console.log("transaction error: " + err);
                            // connection.release();
                            return callback(err, null);
                        });
                    } else {
                        // connection.release();
                        return callback(null, result);
                    }
                })
            }
        })
    });
}

2、封装一层函数，传入多个SQL语句

这部分与之前的博客共用了部分代码。此处是博客入口。

this.ControlAPI_objs_trans_async = function(...vars) {
	let len = vars.length-1;
	let callback = vars[vars.length-1];
	let sqlObjList = [];
	for(let i = 0; i < len; i++){
		if (vars[i].sql) {
			sqlObjList.push(vars[i]);
		} else {
			let sqlObj = this._structureAnalysis(vars[i]);
			sqlObjList.push(sqlObj);
		}
	}
	database.execTrans(sqlObjList, callback);
}

3、调用示例

这部分与之前的博客共用了部分代码。此处是博客入口。

let stru1 = dbController.getSQLObject();
stru1["query"] = "select";
stru1["tables"] = "seqRecord";
stru1["data"] = {
    "day": 0,
    "seq": 0
};
stru1["where"]["condition"] = [
    "day = " + dbController.typeTransform(today)
];

let stru2 = dbController.getSQLObject_sv();
stru2["sql"] = `update seqRecord set seq = seq + 1 where day = '${today}';`;

dbController.ControlAPI_objs_trans_async(stru1, stru2, (error, result) => {
    if (error) {
        utils.sendResponse(res, 404, {"errorCode": CONFIG.ErrorCode.GET_SEQ_FAIL, "msg": "获取失败"});
        return;
    } else {
        utils.sendResponse(res, 200, {"errorCode": 0, "msg": "", "data": result[0][0]});
    }
})