prevent simultaneous withdrawal from user account in Laravel

Why this is a problem

The problem here occurs because one transaction doesn’t know what the other transaction’s resulting balance will be, and it can’t know about the answer to that question because it’s likely happening in a separate thread/process and DB connection.

In these ‘from one user to another’ transactions you are doing all of the following, possibly in one step:

1. Increasing/deducting an amount in one account
2. Calculating the balance for that account
3. Increasing/deducting an amount in another account
4. Calculating the balance for the other account

Basically you will always run into concurrency problems here if the balance calculation is dependent on other data in the same table if that table is allowed to change after it has been read.

How to fix it

To get away from this, you need to move the balance away from the insertion of a transaction.

The balance is actually a summary of all of the transactions on a user’s account. So you should always be able to calculate the balance simply by SUM-ing all of the transactions for a given user. This means you shouldn’t need a running balance in the table.

While that’s true, simply running a SUM query over your transactions table every time you add a new transaction is quite naive and will likely result in pretty poor performance should your transactions table get to any real size (think millions of rows).

This is where something like a lock comes in, because it helps ensure that the value being read (in this case, John’s balance) isn’t going to change during the process.

In order to make the most value out of this sort of locking though, you would need to re-architect your tables so that the balance resides outside of the transactions table.

Your transactions should really only be just that – transactions.

If you keep the running balance elsewhere, you’ll also have a much simpler dataset, as this can just be a single value for each user. You could put it in your users table for example.

Really it ought to be in another table called accounts or something, especially if this is imitating a bank, where users can have multiple accounts… but keeping things simple here.

Then, when you receive a request to deduct from one account, you acquire a lock (see the docs on Pessimistic Locking for more details) on the users table to make sure that the balance isn’t changed by any other transaction first.

The lock happens at the MySQL level as a part of a database transaction and actually prevents any other requests from editing this row. That means any other transaction that comes in while this one is processing will be blocked.

$john = DB::table('users')
    ->where('user', 'John')
    ->sharedLock()
    ->get()
    ->sole();

Then you can read the current balance knowing that it won’t change!

$balance = $john->balance;

Then you add the new transaction and update John’s balance:

$amount = -500; // Obviously variable, coming from your request

if ($amount < 0 && $john->balance < -$amount) {
    throw new Exception('Insufficient funds');
}

DB::insert('insert into transactions (user, amount) values (?, ?)', ['John', $amount]);

$new_balance = $john->balance + $amount;

DB::update('update users set balance = ? where user = "John"', [$new_balance])

Now the deduction transaction will only be processed AND John’s balance will only be updated when his balance was sufficient to meet the payment.

Do this all as a part of a database transaction and the users table will be automatically unlocked once the transaction is committed. All together now…

use IlluminateSupportFacadesDB;
 
DB::transaction(function () use ($amount, $from_user, $to_user) {
    $from = DB::table('users')
        ->where('user', $from_user)
        ->sharedLock()
        ->get()
        ->sole();

    if ($amount < 0 && $from->balance < -$amount) {
        throw new Exception('Insufficient funds');
    }

    DB::insert('insert into transactions (user, amount) values (?, ?)', [$from_user, $amount]);

    $new_balance = $from->balance + $amount;

    DB::update('update users set balance = ? where user = ?', [$from_user, $new_balance])
}, 5);

NB: For brevity, I’ve skipped the parts that relate to the other user ($to_user) and leave this as an exercise for the reader…

The outcome

If two requests happen at the exact same moment, this is still not the exact same moment for MySQL. It will decide on one to run first, lock the users table and run the transaction and then try to run the next one.

When the second one runs, it will likely fail because of the insufficient funds exception. This will allow you to catch that exception and give the user a helpful message.