Hello. There is a site that stores virtual finance for the site and a system of enrollment, withdrawal, transfer between users, etc. Closer to the topic, one person could somehow withdraw the amount two times the same, the whole thing happened at once , the date of the two transactions is the same, the amount of the first transaction is the total cost of its finance on the site, and the second one turns out the transaction that was created it should not have been created since the amount on the account is 0. All protection against F5 and the like are present. Question: how was it done?

The solution, as I understand it, lies in mysql transactions, and how to use it, I understand something, I’m more interested in how this can be done to understand more.

Here is a piece of code that is responsible for creating the application:

$paySys = Paymsys::findByName($this->tasys_in, $this->amount_in)->setPersent(true); if ($paySys->sysAbbr == null) { Yii::$app->session->setFlash('error', "<div class=\"alert alert-danger text-center\">Направильное имя системы</div>"); return false; } $this->amount_in_origin = $paySys->sum; $this->tasys_in = $paySys->sys; $this->user_real_sum = UserMoney::getSum($paySys->sysAbbr); $money = OperationsMoney::start($paySys)->moneyDeduct(); if (!$money->save()) { Yii::$app->session->setFlash('alert', $money->getError() . "&nbsp" . $money->getSum() . '&nbsp' . $paySys->sysAbbr); return false; }

The date is stored in the usual format. 2016-08-22 00:21:51

  • How does backing behave if you send 2 identical requests for this operation at the same time? - Vyacheslav Danshin
  • Can you be more specific please. - Ricco381

3 answers 3

Consider two parallel streams (1) and (2)

  1. Original residue: 10
  2. (1) Request for withdrawal 8
  3. (2) Request for withdrawal 7
  4. (1) The amount does not exceed 10. We remove. As a result, we get 10 - 8 = 2
  5. (2) The amount does not exceed 10. We remove. As a result, we get 10 - 7 = 3
  6. (1) Write 2 in base
  7. (2) write 3 to the base
  8. Bottom line: removed 8 + 7 = 15, current balance 3

Solution: in the 4th stage, block the table. Then we get the following picture:

  1. Original residue: 10
  2. (1) Request for withdrawal 8
  3. (2) Request for withdrawal 7
  4. (1) Block the recording. The amount does not exceed 10. Remove. As a result, we get 10 - 8 = 2
  5. (2) Tried to block the record. It is already locked. We wait
  6. (1) Write 2 in base
  7. (1) Unlock Record
  8. (2) Block the recording. Amount exceeds 2. Break
  9. (2) Unlock Record
  10. Outcome: removed 8, the current balance 2

In the code it will look like this.

 mysqli_autocommit(false); $res = mysqli_query("SELECT money FROM my_table WHERE user_id = $id LOCK FOR UPDATE"); $row = mysqli_fetch_row($res); mysqli_free_result($res) if ($row[0] >= $val) mysqli_query("UPDATE my_table SET money = GREATEST(money - $val, 0) WHERE user_id = $id"); else echo "Облом!"; mysqli_commit();

Parameters and error handling add to taste

  • It turns out I think everything correctly, do I need to use transactions? - Ricco381
  • @KoVadim Hinders. FOR UPDATES - Anton Shchyrov
  • @ Ricco381 Yes. They are precisely for this purpose and intended - Anton Shchyrov
  • Thanks, I will try! - Ricco381

the date of the two transactions is the same

How accurate is the date? mysql for timestamp and datetime stores as the smallest unit of just a second. One second is a lot of time. During this time, even a person can manage to make several requests. And automation with ease.

Classic race condition in your code. It is necessary to carefully look at the code and think what can go wrong with competitive access.

DBMS is quite a good place to serialize transactions. But about competitive access it will be necessary to think all the same. Just saying begin/commit not enough.

All protection against F5 and the like are present.

The obvious conclusion is that these checks were implemented incorrectly or only for special cases.

Without code, nothing can be said, except for the "race condition". With the code - most likely it is quite a lot, and it may not be possible to read it out just like that.

  • The date is stored in the usual format. 2016-08-22 00:21:51 - Ricco381
  • Added code, read about race condition but did not understand - Ricco381

Embedding transactions into ready-made code can be very difficult. If you have a few such critical places, you can use the semaphores http://php.net/manual/ru/ref.sem.php Or you can organize a similar on memcache http://php.net/manual/ru/memcached.add. php