I am using transactions for controlling data across several MySQL InnoDB tables inside a reasonably complex web application. Briefly, confirmed transaction works the following:

  1. Information is read from the row inside a "user_point_totals" table
  2. Various machinations calculate exactly what the user's new point total ought to be
  3. A brand new entry is produced within the "user_point_totals" table reflecting the up-to-date total

Let us state that user A works action which has point-related implications, step one is performed, that thread of execution reads anyone's point total into memory, and also the application starts calculating the brand new total. Meanwhile, user B works an action which has implications for user A's point total, and the other transaction starts however, the very first transaction has not completed, therefore the second thread will get exactly the same point total value like a beginning point because the first transaction (in the same table row). Subsequently, transaction 1 completes and produces a brand new user point total using its thought of exactly what the new value ought to be, and very soon after that, transaction 2 completes and produces a brand new row for that user's point total too. However, the 2nd transaction's point total has become incorrect, because it does not take into account the brand new total produced by transaction 1.

My questions are:

  • Is scenario impossible because of the atomic character of transactions, that we apparently do not understand in addition to I ought to?
  • Otherwise, how do you make sure that data integrity is available in these kinds of situations?

Interesting consideration!

Around the technical level, you could utilize table-locking (or row-securing) capabilities of MySQL and would permit you to identify if a person is really computing something for that table, but that will require much factors like what goes on if your process crash, etc.

Around the practical level, though, I doubt you would like to make a move such as this. Operators like sum() or avg() in MySQL happen to be optimized with this need. If what you ought to do is really a sum over some posts of the table and obtain the solution inside a table, you could utilize a view or produce a temporary table (possible but reduced). You should attempt not to produce a table that consists of statically something that's dynamic in your system.

On the second note, make sure to use InnoDB tables in your MySQL instance, otherwise the body will not be fully Acidity-compilant, symbols of you will not obtain the atomicity character you'll need.