Node.js + MySQL - handling transactions

Question

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.

Currently, the way I am doing this is to have some kind of middleware which does a START TRANSACTION when 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 COMMIT before sending the response to the browser.

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.

Can someone please advice me if my concerns are valid, and how should I get in transactions support?

Answer 1

Check out 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. :)

UPDATE: I have tested this library pretty thoroughly now... should be good to go!