javascript Node.js + MySQL - 处理事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5940380/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Node.js + MySQL - handling transactions
提问by jeffreyveon
I am building an app on node.js using express, and node-mysql driver. There is a couple of cases in my app when I need to make a series of database inserts/updates. I want them in a transaction such that if the second or third one fails, the previous inserts are rolled back completely.
我正在使用 express 和 node-mysql 驱动程序在 node.js 上构建一个应用程序。当我需要进行一系列数据库插入/更新时，我的应用程序中有几种情况。我希望它们在一个事务中，如果第二个或第三个失败，之前的插入将完全回滚。
Currently, the way I am doing this is to have some kind of middleware which does a START TRANSACTIONwhen a request arrives. During the course of processing of the request, if any error is thrown, I catch this error, and do a ROLLBACK. If no error occurs, I do a COMMITbefore sending the response to the browser.
目前，我这样做的方法是使用某种中间件，START TRANSACTION在请求到达时执行。在处理请求的过程中，如果抛出任何错误，我会捕捉到这个错误，并执行一个ROLLBACK. 如果没有发生错误，我会COMMIT在将响应发送到浏览器之前执行。
However, I am now concerned that this won't work when multiple users access the application simultaneously, as MySQL does a forced commit if another request tries to begin it's own transaction with START TRANSACTION! I am currently using only a single instance of node, and a single MySQL connection for all the requests.
但是，我现在担心当多个用户同时访问应用程序时这将不起作用，因为如果另一个请求尝试开始它自己的事务，MySQL 会强制提交 START TRANSACTION！我目前只使用一个节点实例，并为所有请求使用一个 MySQL 连接。
Can someone please advice me if my concerns are valid, and how should I get in transactions support?
如果我的担忧是有效的，有人可以建议我，我应该如何获得交易支持？
采纳答案by Geoff Chappell
You'll need to create a client pool, or somehow otherwise ensure that two different pages aren't interspersing commands on the same connection (at least while any of them is in a transaction).
您需要创建一个客户端池，或者以其他方式确保两个不同的页面不会在同一连接上散布命令（至少当它们中的任何一个处于事务中时）。
Since you want to conditionally do a rollback based upon the result of an earlier command, you'll need to chain the db calls together through their callbacks and not rely on the node-mysql queuing behavior. That will open up a window for some other page to come in and queue up an operation on the same connection as you suggest.
由于您希望根据先前命令的结果有条件地进行回滚，因此您需要通过它们的回调将 db 调用链接在一起，而不是依赖于 node-mysql 排队行为。这将打开一个窗口，让其他页面进入并按照您的建议在同一连接上排队操作。
You could create and manage your own queue, but that would end up serializing all transactional pages (assuming you're sticking with the single connection model).
您可以创建和管理自己的队列，但这最终会序列化所有事务页面（假设您坚持使用单一连接模型）。
From a quick googling, it looks like there are several node-mysql pools on github. After looking at them, though, they don't look like they'll help with your issue.
从快速的谷歌搜索来看，github 上似乎有几个 node-mysql 池。但是，在查看它们之后，它们看起来不会对您的问题有所帮助。
回答by BMiner
Check out https://github.com/bminer/node-mysql-queues
查看https://github.com/bminer/node-mysql-queues
I implemented a little wrapper for node-mysql to support transactions and multiple statements. It has not been tested, and is NOT production ready... but it will be in a few days. :)
我为 node-mysql 实现了一个小包装器来支持事务和多个语句。它尚未经过测试，也未准备好投入生产……但将在几天内完成。:)
UPDATE: I have tested this library pretty thoroughly now... should be good to go!
更新：我现在已经非常彻底地测试了这个库......应该很好！
回答by Loc Nguyen
Depending on how complex your transaction is you might run into some ugly nesting trying to queue your queries from Node, which might introduce ugly variable scoping issues.
根据您的事务的复杂程度，您可能会遇到一些丑陋的嵌套，试图将来自 Node 的查询排队，这可能会引入丑陋的变量范围问题。
What you can do instead is write a stored procedure and end it by SELECTing a success/failure flag, then query the procedure with node-mysqlas you would a SELECTquery. Here's how the stored procedure might look:
你可以做的反而是写一个存储过程并结束它SELECT荷兰国际集团成功/失败的标志，然后查询与程序节点MySQL的你将一个SELECT查询。以下是存储过程的外观：
DELIMITER //
DROP PROCEDURE IF EXISTS MyProcedure //
CREATE PROCEDURE MyProcedure(IN param1 VARCHAR/*, My, Parameters, ... */)
BEGIN
DECLARE EXIT HANDLER FOR NOT FOUND, SQLWARNING, SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 0 AS res;
END;
START TRANSACTION;
# My Transaction ...
COMMIT;
SELECT 1 AS res;
END //
DELIMITER ;
Your Node code would look something like this:
您的 Node 代码如下所示：
var mysql = require('mysql');
var client = mysql.createClient({
host : '127.0.0.1',
user : 'username',
password: 'password'
});
client.query('USE mydatabase');
var myParams = "'param1', 'param2', ... ";
client.query("CALL MyProcedure(" + myParams + ")", function(err, results, fields) {
if (err || results[0].res === 0) {
throw new Error("My Error ... ");
} else {
// My Callback Stuff ...
}
});
回答by davin
I find it hard to believe that if a separate session executes a START TRANSACTIONthat other transactions are committed. That would be totally unsafe, especially when data needs to be rollbacked (or is it "rolled back"?).
我发现很难相信如果一个单独的会话执行了START TRANSACTION其他事务会被提交。那将是完全不安全的，尤其是当数据需要回滚（或者是“回滚”？）时。
Is it possible you're mixing this up with a same sessionSTART TRANSACTION?
See http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.htmlwhere it explains that transactions cannot be nested. That of course applies to the same session, not to another user's session.
你有可能把它和同一个会话混在一起START TRANSACTION吗？
请参阅http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html，其中说明事务不能嵌套。这当然适用于同一个 session，而不是另一个用户的 session 。
Assuming you haven't messed around with the isolation level of your session, or the global isolation level, then transactions should be safe.
假设您没有搞乱会话的隔离级别或全局隔离级别，那么事务应该是安全的。
In any case, if you wanted to queue your transactions it wouldn't be hard to build a global queue object in node and chain the calls (so one starts when another finishes). A simple array with push and pop should do the trick.
在任何情况下，如果您想对您的交易进行排队，在节点中构建一个全局队列对象并将调用链接起来并不难（因此一个在另一个完成时开始）。一个带有 push 和 pop 的简单数组应该可以解决问题。
回答by Alvaro Juste
Just an idea: on postresql you can start a transaction and set an ID to it. So then, you could be reusing the same connection around, because in case you need to commit or rollback, you are going to refer to your transaction by id, right?
只是一个想法：在 postresql 上，您可以启动一个事务并为其设置一个 ID。那么，您可以重复使用相同的连接，因为如果您需要提交或回滚，您将通过 id 引用您的事务，对吗？
