How to do the comparison correctly?

Question

PHP

Hello. This is the dilemma.
There is a database, and it has two columns: balance and blocked_balance.
balance - regular account.
blocked_balance - this is the account that was replenished by the client, funds from this account cannot be withdrawn, unlike balance.


There is a function that counts.

spoiler

function setUserBalance(int $user_id, int $sum) : void {
    $db = DB::getAll('SELECT `balance`, `blocked_balance` FROM `users` WHERE `user_id`=?', [$user_id])[0];
    $balance = (int)$db['balance'];
    $blocked_balance = (int)$db['blocked_balance'];

    if ($sum < 0) {
        if ($blocked_balance > 0 && $blocked_balance >= $sum * -1) {
            DB::exec('UPDATE `users` SET `blocked_balance`=`blocked_balance`-? WHERE `user_id`=?', [$sum * -1, $user_id]);
        } elseif ($blocked_balance > 0 && $blocked_balance < $sum * -1) {
            DB::exec('UPDATE `users` SET `blocked_balance`=0, `balance`=`balance`-? WHERE `user_id`=?', [($sum * -1) - $blocked_balance, $user_id]);
        } else {
            DB::exec('UPDATE `users` SET `balance`=`balance`-? WHERE `user_id`=?', [$sum * -1, $user_id]);
        }
    } else {
        DB::exec('UPDATE `users` SET `balance`=`balance`+? WHERE `user_id`=?', [$sum, $user_id]);
    }
}

The essence of the function is as follows: when debiting, if the blocked_balance balance is greater than or equal to the amount, then the funds are debited from blocked_balance. If there is not quite enough money on blocked_balance for a full write-off, then blocked_balance is set to 0, and all deductions are already made in balance, and the amount of blocked_balance that was canceled is deducted. Such is the system.

And the dilemma itself is that the function is not entirely correct, sometimes the user's balance goes into the red, but this should not be the case. All checks for the balance before writing off stand (there is a function that adds balance + blocked_balance). Therefore, the error is in this function. I ask for help, because I have already spent enough time and brought the function to my ideal, but I can’t go any further, there is not enough knowledge.

PS I am using RedBeanPHP.

Answer 1

See how your function works when there is not enough funds for a given amount on both balances.
For example, balance = 1, blocked_balance = 1, sum = -3. The second branch of if will work, blocked_balance will become equal to 0, and balance will become -1.
Also, your code is not immune to race conditions. If the withdrawal of funds will take place on two simultaneous requests of one user (for example, from different browsers or devices), then a situation may arise when the first request reads the balances, then the second reads the balances, then the first changes them, and then the second also changes them, but based on old data not yet changed by the first request. It is necessary to lock the table for the duration of the function.