Web Application - MySQL Transactions

Question

I'm planning a web application that will allow concurrent access to certain tables--using Rails. Does anyone have advice for implementing transactions? Is it even necessary?

The application will run under a CentOS LAM configuration.

Answer 1

Transactions and concurrence are not related. Transactions are needed in cases when you apply several actions over the DB (usually, but not necessarily, to several tables) if the failure of any one of them would provoke an inconsistent state of some sort- be it with the already existent data or if the failed action was supposed to establish a requisite for the actions that follows it (for example: if fails the insertion of a new record that will be parent of others that would be inserted by the next actions). So in those cases, you need to guarantee to apply the related actions only if they are all successful or if they weren't, to rollback then all the previously executed related actions to return to the initial state that existed before the first of such actions were executed

Answer 2

I don't know that transactions and concurrency have a whole lot to do with each other.

The main reason to use transactions, as I see it, is to make sure that if you perform multiple operations on the database, that they either all work or all fail. See this blog post of mine for more details on that.

So in other words, I'd say no, you don't have to worry about transactions as a way to address your concurrency situation. (I could be wrong, though.)