I'm thinking about implementing transactions to my app to help with consistency issues. I'm using MySQL.

I'm wondering what might happen if while inserting several rows as a transaction, the server will also need to insert a single row, that is completely unrelated.

To be more clear, I will present a scenario (hypothetical) of a store:

The application server in running nodejs, and connected to a MySQL server. It then receives a request to add a stock of 50 new computers, and needs to insert the 50 rows representing their SN to a table stock, plus a row in another table shipments, all 51 rows must be inserted in an atomic manner (all or none). While this is happening, the same nodejs server receives a request to register a new customer, and is required to add one row to a table customers.

In that scenario, what will happen if the transaction fails and must be rolled-back? Will the row in customers be ommited as well? Is this scenario even possible? Or maybe it depends on the implementation, and if so, how to implement it so that this scenario is garanteed never to happen?

  • 1
    Transaction is "opened" on some connection/session, so you just need to use a separate connection for each request, then they will be independent. You can open new connection per request or use some pool, but that's a question for nodejs/stackoverflow and not here. – jkavalik Sep 24 '15 at 5:54
  • Are you using MyISAM or InnoDB for your database engine in MySQL? What version are you using? – Kassandry Sep 24 '15 at 18:03
  • I can use either one, this is more of a theoretical question. – Tom Klino Sep 28 '15 at 10:11
  • MyISAM does not support transactions (ACID) at all, only table locks. – jkavalik Sep 29 '15 at 9:03
  • As I said, it's more of a theoretical question. Your comment from before pretty much provided the answer I wanted. If you would like to ellaborate and include it as an answer I will accept it. If you don't I would do it myself at a later time – Tom Klino Sep 29 '15 at 18:06

If you can handle all the writes in a single connection, then Plan A. Note: Moving from one "page" to another in a browser necessarily leads to another connection, hence Plan B.

Plan A: BEGIN; write...; COMMIT; And check for errors. ROLLBACK on errors and retry the entire transaction. If not successful, tell the user that his seat on the plane was taken and he must start over.

Plan B: You must set up another table to keep track of "transactions" and be ready to rollback (etc) things outside of what InnoDB (much less MyISAM) can do for you.

Note: If Plan A takes more than a few seconds, you are asking for trouble and for failures. If it takes longer, switch to Plan B.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.