Как правильно списать баланс в базе?

Question

[aopil]

При десятках одновременных запросах баланс пользователя списывается не правильно.

Гуглил, изучал, читал, как я понял для этого используют транзакции.

Накинул такой код:

<?php 
try {
	$objDBCD14->autocommit(false);
	$objDBCD14->begin();
	$rsMyCredits = $objDBCD14->queryUniqueObject("SELECT Balance FROM tbl_users WHERE UserId = '" . $rs->UserId . "'");

	$myCredits = somedecrypt($rsMyCredits->Balance);
	$finalBalance = $myCredits - $servicePrice;
	$encryptedCredits = someencrypt($finalBalance);

    $objDBCD14->execute("INSERT INTO tbl_credit_history SET UserId = '" . $rs->UserId . "', Credits = '" . $servicePrice . "', HistoryDtTm = '" . $currDtTm . "', CreditsLeft = '" . $encryptedCredits . "'");
    $objDBCD14->execute("UPDATE tbl_users SET Credits = '" . $encryptedCredits . "' WHERE UserId = '" . $rs->UserId . "'");

    $objDBCD14->commit();
    $objDBCD14->autocommit(true);

} catch (Exception $e) {
	$objDBCD14->rollback();
}
?>

Но проблема как была, так и есть. Да, знаю, что баланс пользователя лучше считать напрямую в SQL запросе, но в данной ситуации баланс пользователя закодирован некой функцией, как вы можете видеть по коду.

Вопрос: Как же все таки решается эта проблема? Транзакциями? тогда что я делаю не так?

Comments

[Akina]

Да, знаю, что баланс пользователя лучше считать напрямую в SQL запросе, но в данной ситуации баланс пользователя закодирован некой функцией, как вы можете видеть по коду.

Не вижу разницы. Не всё ли равно, берётся сумма движения готовая или считается? всё равно выполняется занесение уже рассчитанных значений.

А чтобы баланс не рассогласовывался - да, всё должно выполняться в единой транзакции. И проще всего это сделать, изменяя/обновляя таблицу tbl_users из триггера на таблице tbl_credit_history - тогда всё укладывается в один запрос, который всегда является транзакцией. Но да, внешняя функция этому мешает - поэтому придётся формировать транзакцию явно.

[aopil]

Akina, К нам пришло 20 запросов в секунду, у пользователя баланс 100$

каждый заказ стоит 1$

100-20=80.

А у меня у пользователя баланс остается 85$ грубо говоря. Вот вам и проблема о которой я изначально пишу!) Как избежать этого???

[Дмитрий Гординский]

aopil, какой уровень изоляции транзакций используется?

[aopil]

Дмитрий Гординский, Я не устанавливал его. Я должен это сделать? Или как посмотреть мой текущий уровень изоляции транзакций?

[aopil]

Дмитрий Гординский, должен ли я использовать Serializable в моем случае?

[Дмитрий Гординский]

aopil, не, repeatable read должно быть достаточно.

Есть логи исходя из которых можно быть уверенным что все запросы на обновление прошли успешно?

Answer 1

[galaxy]

Начните вот с этого:

$rsMyCredits = $objDBCD14->queryUniqueObject(
    "SELECT Balance FROM tbl_users WHERE UserId = '" . $rs->UserId . "' FOR UPDATE"
);

Comments

[aopil]

galaxy спасибо за ваш ответ, но чтобы он был более ценным, хочется понять для чего здесь FOR UPDATE и что оно даст

[galaxy]

aopil, блокировку он даст на этапе SELECT. Так что пока одна транзакция не закончит менять баланс, другая не сможет начать его читать (а ваши проблемы, скорее всего, как раз из-за race condition в этом месте)

[aopil]

galaxy, протестировал, не помогло. Отправил 20 заказов одновременно, в итоге баланс конечный посчитался не правильно(

[galaxy]

aopil, вы уверены, что транзакции работают у вас? InnoDB, надеюсь?

Попробуйте задебажить и понять, что происходит. Просто хотя бы пишите лог типа (для каждого отдельного запроса):

# запроса        Временная метка        Баланс (из первого SELECT)
# запроса        Временная метка        Баланс (после UPDATE)

и посмотрим.

[aopil]

galaxy, таблицы в InnoDB. Тип сервера: Percona Server.

К сожалению, я не шарю как сделать лог который вы попросили :(

Если можете помочь разобраться с транзакциями напишите на почту указанную в профиле, готов оплатить.

[aopil]

galaxy, заменил $objDBCD14->commit(); на $objDBCD14->rollback();

Таким образом баланс пользователя вовсе не должен был измениться, верно? Но изменения произошли. Значит транзакция не работает?

[galaxy]

aopil,

Значит транзакция не работает?

логично.
А что у вас за такой класс для работы с БД? Явно не просто mysqli

Ну и на всякий случай проверьте SHOW CREATE TABLE tbl_users (https://dev.mysql.com/doc/refman/8.0/en/show-creat...)

[aopil]

galaxy,

public function __construct($base, $server, $user, $pass)
    {
        $this->mtStart = $this->getMicroTime();
        $this->nbQueries = 0;
        $this->lastResult = NULL;
        $this->dbh = mysqli_connect($server, $user, $pass) or exit("DB Connection Failed!");
        mysqli_select_db($this->dbh, $base) or exit("Error connecting with DB");
    }

    public function __destruct()
    {
        mysqli_close($this->dbh);
    }

    public function autocommit($value)
    {
        mysqli_autocommit($this->dbh, $value);
    }

    public function begin()
    {
        mysqli_begin_transaction($this->dbh);
    }

    public function commit()
    {
        mysqli_commit($this->dbh);
    }

    public function rollback()
    {
        mysqli_rollback($this->dbh);
    }

Весь класс БД думаю скидывать нет смысла.

Я сделал запрос " SHOW CREATE TABLE tbl_users" и увидел вот что:


ENGINE=MyISAM - Видимо и есть причина того, что не работают транзакции, сейчас буду менять на InnoDB и отпишусь по разельтату

[aopil]

galaxy, До сих пор не верю, но ваш ответ решил мою проблему) Спасибо огромное!

Answer 2

[Pavel K]

Похоже больше на гонку - когда запрос A приходит позже запроса B.
Решаются такие проблемы индивидуально, в зависимости от остальной архитектуры грубо говоря ичего-то универсального тут нет.
Касаемо Вашей ситуации, мне лично непонятно, каким образом очерёдность запросов может повлиять на результат, т.к. в моём понимании текущей ситуации баланс это либо сложение либо вычитание, и ничего особо страшного не будет, если будет -1 -2 -3 +10 а не +10 -1 -2 -3 итог то будет один.

Можете слать баланс ДО и какой должен быть ПОСЛЕ и все изменения проводить скажем раз в N секунд, а до этого просто всё собирать в некую очередь для дальнейшей сортировки/понимания кто что делает и когда, во время которой отказывать в запросах, если выполнение долгое и т.д.

Comments

[aopil]

Pavel K К нам пришло 20 запросов в секунду, у пользователя баланс 100$

каждый заказ стоит 1$

100-20=80.

А у меня у пользователя баланс остается 85$ грубо говоря. Вот вам и проблема о которой я изначально пишу!) Как избежать этого???

[Александр Карабанов]

aopil, это решается с помощью транзакций.

[aopil]

Александр Карабанов, так в моем примере я не использую транзакцию?)))) Я видимо совсем не понимаю что-то(

[Pavel K]

aopil, возможно, какие-то запросы у Вас не выполняются, потому что на момент запроса выполняется транзакция.

[Александр Карабанов]

aopil, работать с финансами без транзакций невозможно.

[Руслан Федосеев]

ну или не храните баланс. Храните платежи и расходы. И в любой момент времени баланс = сумма платежей - сумма расходов....