i building an application that deal with a 'Wallet', so i have cash-in and cash-outs of this wallet. And i have a problem when a update occurs to or - of this wallet. Look the scenario:

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

select * from Wallet where idUser = x;

So this query above returns the wallet, and i 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 i 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 whats the better scenario in this case. Put this in a queue? Lock the table when i doing the update? Is the first time i deal with a system like that and i`m really confused about what to do.

Thanks for everyone.

CodePudding user response：

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

CodePudding user response：

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.