1

Overview

Consider the following details:

  1. We have a table named user. In it is a column named wallet.
  2. We have a table named walletAction. We insert a new entry on each wallet action a user is doing. This table acts like some sort of logs in the database with some calculations.
  3. We have a CRON command that does an update every N minutes. Each CRON action gets some data by using a standalone API and 'inserts' a new walletAction entry. At the sime time, it updates the user.wallet's value.
  4. A user can buy stuff from our site. When the user clicks the buy button, we insert a new walletAction entry and change the user.wallet column.

Problem

I am afraid that the CRON update and the action of the user when they click the buy button will happen at the exact same time causing the entries in the walletAction table to have wrong calculations.

I need some kind of 'lock' on the CRON update execution or something along those lines.

Questions

  1. Should I be afraid of this situation?
  2. How can I avoid this problem?
  3. Can I avoid this trouble by using MySQL transactions?
  4. What isolation level should I use and in which case should I use it? (In the CRON command or in the action of the user when they click the buy button?)
Keale
  • 3,924
  • 3
  • 29
  • 46
user1954544
  • 1,619
  • 5
  • 26
  • 53
  • You seem to be at a loss for the term; you are worried about having **concurrency** problems. – Keale Jan 18 '16 at 06:39
  • 1
    [might be able to help](http://stackoverflow.com/questions/1195858/how-to-deal-with-concurrent-updates-in-databases) – Keale Jan 18 '16 at 07:23

2 Answers2

0

It seems that we don't have concurrency on php as is in GO or Java. You can implement some technical trick, but almost of them made new problems for you :). For solving your problem i suggest you to use optimistic lock. For more information you can see http://www.yiiframework.com/doc-2.0/guide-db-active-record.html#optimistic-locks.

meysam
  • 1,754
  • 2
  • 20
  • 30
  • I won't help because as far as I know it locks only on beforeSave till afterSave. So if I need change several tables(entries) it will be a problem because I need lock them all, not just 'one on update moment'. So it cannot help me. – user1954544 Jan 19 '16 at 11:04
0

Yes, in this case I would recommend to use trasactions with the strongest isolation level yii\db\Transaction::SERIALIZABLE. This level should prevent "phantom reads" and "non-repetable reads".

Moreover I recommend to use transactions always when you perform more than 1 related changes, because it helps to keep DB consistency. This may prevents problem when you get some PHP exception after successful inserting new rows into walletAction, but before user.wallet updating.

IStranger
  • 1,868
  • 15
  • 23