NodeJS - MySQL 事务未按预期工作 [英] NodeJS - MySQL Transactions not working as expected

问题描述

我正在尝试应用交易(beginTransaction、rollback 和 commit.但是如果添加发票的查询(第一查询)成功执行我想更新供应商数量(第二个查询).我打算通过将 UPDATE 更改为 UPDATEEE 在第二个查询中编写错误的语法.我认为这应该 rollback.我收到查询语法错误消息，但已添加发票(第一个查询已成功执行).

I am trying to apply Trasnactions (beginTransaction,rollback and commit. however if the query of adding invoice (Frst Query) executed successfully I want to update the supplier amount (Second Query). I intended to write wrong syntax in the second query by changing UPDATE to UPDATEEE. I assumed this should rollback. I got query syntax error message but the invoice added (first query successfully executed).

我做错了什么?

Invoice.addNewInvoice = function (invoice_data,result){

    sql.beginTransaction(function(err){
        if (err) { throw err; }

        sql.query('INSERT INTO invoice SET ?',invoice_data, function(err,res){
            if(err){
                sql.rollback(function() {
                    throw err;
                });
            }else{
                sql.query('UPDATEEEEE supplier SET supplier_amount = supplier_amount + ' + invoice_data.invoice_amount + ' WHERE supplier_id = ' + invoice_data.supplier_id, function(err,res){
                    if(err){
                        sql.rollback(function() {
                            throw err;
                        });
                    }
                })
                sql.commit(function(err) {
                    if (err) { 
                        sql.rollback(function() {
                            throw err;
                        });
                    }else{

                        result(null,res);
                    }
                });
            }

        });
    });
}

推荐答案

问题是我将 commit 设置为即使第二个查询失败也会执行.它应该在 else 上设置如下:

The problem is that i set the commit to be executed even if the second query faild. it should be set on the else as below:

Invoice.addNewInvoice = function (invoice_data,result){

    sql.beginTransaction(function(err){
        if (err) { throw err; }

        sql.query('INSERT INTO invoice SET ?',invoice_data, function(err,res){
            if(err){
                sql.rollback(function() {
                    throw err;
                });
            }else{
                sql.query('UPDATEEEEE supplier SET supplier_amount = supplier_amount + ' + invoice_data.invoice_amount + ' WHERE supplier_id = ' + invoice_data.supplier_id, function(err,res){
                    if(err){
                        sql.rollback(function() {
                            throw err;
                        });
                    }else{
                        sql.commit(function(err) {
                            if (err) { 
                                sql.rollback(function() {
                                    throw err;
                                });
                            }else{

                                result(null,res);
                            }
                        });
                    }
                })

            }

        });
    });
}

这样它就会回滚，因为如果第二个查询失败.

This way it rollback since if the second query faild.

这篇关于NodeJS - MySQL 事务未按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！