prevent simultaneous withdrawal from user account in Laravel

Question

I want to prevent simultaneous withdrawal of account balance. For example, don't allow it when the balance of the user's account is 500 dollars. Two concurrent requests that withdraw $300 will change the user balance to -$100

users table :

ID	name
1	John
2	David
3	Sina

I have a table called transactions as shown below :

ID	user	amount	balance
1	John	+350	350
2	John	+250	600
3	John	-100	500
4	John	+400	900

By looking at the table above, we can see that the account balance of the user John (by adding the values of the amount column) is $900

I created an api to transfer inventory from one user to another

http://127.0.0.1:8000/api/v1/deposit/transfer/FromUser/ToUser/amount

If I run the api once as follows. The transaction table will change as you can see

http://127.0.0.1:8000/api/v1/deposit/transfer/John/David/500

ID	user	amount	balance
4	John	-500	400
5	David	+500	500

So far we have no problem.But if the above api is executed by two users at the same time, John's account balance will be negative

Suppose the api is called by two users at the same time. The following happens

1. http://127.0.0.1:8000/api/v1/deposit/transfer/John/David/500
2. http://127.0.0.1:8000/api/v1/deposit/transfer/John/Sina/600

ID	user	amount	balance
1	John	+350	350
2	John	+250	600
3	John	-100	500
4	John	+400	900
5	John	-500	400
6	David	+500	500
7	John	-600	300
8	Sina	+600	600

What is the total amount column for John now? -100 dollars and this is the beginning of disaster!

I want to prevent this in Laravel. What is the best method I can use? (My database is mysql)

Answer 1

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 Illuminate\Support\Facades\DB;
 
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.