1

My application is implemented with php/laravel and MySQL database. In this system, users can deposit money to wallet. I have a wallet_balance column in the users table. When users deposit funds into their wallet, I update that column as follows:

public function topup(Request $request)
{
    $user = auth()->user();
    $deposit_amount = $request->deposit_amount;
    //e.g.
    //$deposit_amount = 100
    //$user->wallet_balance = 50
    $user->wallet_balance = $user->wallet_balance + $deposit_amount;
    $user->save();
    // After saving I expect $user->wallet_balance to be 150 which works perfectly.
}

There are some services that users are charged where money is deducted from their wallet (in another function). For example:

public function chargeService(Request $request)
{
    $user = User::findOrFail($request->user_id);
    $service_fee = $request->service_fee;
    //$service_fee = 30
    //$user->wallet_balance = 150
    $user->wallet_balance = $user->wallet_balance - $service_fee;
    $user->save();
    // After saving I expect $user->wallet_balance to be 120 which works perfectly.
}

After the two transactions, the user's wallet balance should be 120. However, on very rare cases the two transactions might happen concurrently. That means in the deposit transaction, the initial wallet balance is 50 and also for the service fee transaction the initial wallet balance is 50 (because they queried the database at the same time before any of the two updated the wallet balance). Here is the danger. The first transaction will have the resulting wallet balance as 150 (50 + 100), and the second will have it as 20 (50 - 30). This leaves the wallet balance of the user as either 150 or 20, depending on which operation updates the user's wallet balance last.

Now, my question is, how can I approach my wallet system so as to avoid this stinking issue. I will appreciate so much guys.

You can suggest a better way to phrase the question.

Destiny Maina
  • 61
  • 1
  • 8
  • Did you have a look at database transactions? – Aless55 May 19 '21 at 07:49
  • Can they help on the issue? @Aless55 Let me have a look at it. If you have a resourceful link, please drop here I will appreciate. – Destiny Maina May 19 '21 at 08:01
  • https://stackoverflow.com/questions/15105640/laravel-eloquent-orm-transactions may be worth a read. – Nigel Ren May 19 '21 at 08:02
  • It turns out like database transactions won't help in this case. This is because the operations (Wallet deposit and service charging) are in two completely different instances. Service charging is triggered by the system and wallet top-up is triggered by the user. – Destiny Maina May 19 '21 at 08:24

2 Answers2

2

You have to lock the row of table using raw mysql query and i dont know if eloquent has some features for that type of query but you can dig in. Have a look at this article to get a clear picture https://medium.com/@ibraheemabukaff/locking-rows-in-mysql-e84fd3bbb8cd

Sandeep Dhakal
  • 299
  • 2
  • 11
2

The main issue you're facing is that the User state that is loaded in PHP doesn't reflect what's in your database. You should either execute a query against the database:

$params = ['fee' => 30, 'user_id' => $request->user_id];
DB::statement('UPDATE users SET wallet_balance=wallet_balance-:fee WHERE id=:user_id', $params);

or lock the record for updating:

try {
    DB::beginTransaction();
    // it is important to get the state from the 
    // database at the time of locking
    $user = User::query()->lockForUpdate()->findOrFail($request->user_id);
    $user->wallet_balance = $user->wallet_balance - $service_fee;
    $user->save();
    DB::commit();
} catch (Throwable $e) {
    DB::rollBack();
}
dkt
  • 146
  • 3