How to safety add new money operation to database?-CodePudding

I store all user money operations in database. 1 new operation = 1 row.

For example:

The user's balance is the sum of all his operations.

When making a purchase, the current balance is checked and if it is greater than or equal to the purchase amount, a new debit transaction is created.

I am worried that under a heavy load (or just under the circumstances) the system will be able to skip two purchases, even if there is only enough money for one. The user's balance will go into negative territory, but the service will be received.

How to avoid this situation?

CodePudding user response：

You will have to serialize these transactions.

One solution is to have a balance column for each user and to use a trigger that updates that column whenever a transaction is added. Then a CHECK constraint on that column will do the trick. In this case, the transactions are serialized on the row lock for the UPDATE of this column.

The alternative is to use the SERIALIZABLE transaction isolation level. Then concurrent modifications will cause a serialization error that forces you to repeat the transaction. This strategy will work well if there are not too many conflicts and if all access to the transaction table is via index scans.

CodePudding user response：

In the Postgresql you have multiple options, but IMHO the simplest is using SELECT .. FOR UPDATE

Example:

BEGIN; -- USE TRANSACTIONS
-- this select locks the returned rows
SELECT amount FROM balance WHERE id=.. FOR UPDATE ;

-- execute this only when amount is larger than 0
UPDATE balance SET ....
INSERT INTO transactions ... 
COMMIT

You can as well use explicit locks or table locks, but let's keep it simple for now