CODEWITHSUNDEEP
mysqlsqlalgorithmconcurrency
Thiago Gabriel Braz
Thiago Gabriel Braz

Reputation: 11

Concurrent Update

I'm building an application that deals with a Wallet. I have cash-in and cash-outs of this wallet.
And I have a problem when an update occurs to + or - of this wallet.
Look the scenario:

A user makes a cash-in and the system retrieves the balance of this wallet like this:

select * from Wallet where idUser = x;

So this query above returns the wallet, and * get the 'balance' column and do like this

wallet.balance += amount;

After this I will make the update to insert the new 'balance' of this wallet and do this

update Wallet set balance = balanceVar where idWallet = x;

Until here everything looks fine but when we deal with concurrent cash in and cash outs what's the better scenario in this case.
Put this in a queue?
Lock the table while doing the update?
This is the first time I deal with a system like that and I'm really confused about what to do.

Upvotes: 0

Views: 916

Answers (3)

wajdi_jurry
wajdi_jurry

Reputation: 307

TLDR; You can use queuing mechanism to ensure consistency in the update operations.

Short example: Using Redis queueing:

Publisher:

// Increase or decrease wallet balance, or maybe multiple operations
$redis->lpush('my-queue', json_encode([1234 => '+1']); // ex. 1234 is wallet id 
$redis->lpush('my-queue', '-1');
// ...etc

Subscriber:

while (true) {
   $queueData = $redis->rpop('my-queue');
   if (!empty($data)) {
      $data = json_decode($queueData);
      // process increment or decrement wallet balance based on consumed data
   }
}

This will update the wallet balance accordingly based on the queued data. You can use any queuing tool like Redis, RabbitMQ, ...etc to achieve this.

Upvotes: 0

Tangentially Perpendicular
Tangentially Perpendicular

Reputation: 5358

Your SELECT then UPDATE scheme leads directly to a race condition where two processes can SELECT the same data, and then perform the same update, leading to an inconsistency where two updates occur, but only one is recorded.

The way to avoid this is to use an atomic operation (i.e. one that can't be divided).

In your case you should simply update the wallet directly:

update Wallet set balance = balance+10 where idWallet = 'x';

This update will complete in its entirety before another process can perform another update.

You can go further with this idea. For withdrawals you could update the wallet only if the balance is greater than or equal to the withdrawal:

update Wallet set balance = balance-10 where idWallet = 'x' and balance >= 10;

You can test ROW_COUNT() to see if the operation succeeded. PHP provides mysqli_affected_rows() or PDOStatement::rowCount() for this purpose.

Upvotes: 2

Adrian Villaseñor
Adrian Villaseñor

Reputation: 11

The best thing in these cases is to use transactions to guarantee the integrity of the information, in this way the manager will take care of the possible conflicts that arise when manipulating the information

Upvotes: 1

Related Questions