How to protect against double debiting in a multi-threaded application?

Question

PostgreSQL

The spending consists of three stages. Reading the user's balance, checking whether there are enough funds and actually recording a new balance. Recording and reading the balance goes to the database. In my case postgres.
If in a multi-threaded application it happens that two threads read the balance, check whether there are enough funds for the purchase and this stage is successful for both, then two entries will follow, which will drive the user into a minus. What are the options for double spending protection?
I'm throwing it myself as I think:
1 Make a constraint on the user's balance> 0.
2 Set a read lock on the user for the duration of the first successful process, let the second one wait. There is a problem that I did not find a line lock blocking on reading. If it is, tell me pliz

What other options are there?

In general, everything is much more complicated. the user's balance is calculated on the fly and is not stored in the user. It is calculated based on the history of its replenishment / expenses.

Additional question. What is the best way to design an application that controls user balances? Write balance to user? Then how to ensure consistency at the base level? Write triggers when a transaction appears / changes to recalculate the balance?
Or is it better to recalculate every time from history? Who had experience, please share
And the last question is how to write a test for such cases? Application on ruby-on-rails

Answer 1

T - transactions.

Answer 2

Blocking during spending has already been said, but it happens that the process can last long enough so that the user in the next window cannot make a payment in parallel (he will have everything hanging), so blocking is implemented programmatically
Add the lockedBalance field to the user account, add it at the beginning of the purchase transaction to this value, the amount you need to spend (and at the end, subtract the same amount both from here and from the total balance), respectively, the final balance when checking, consider from the difference between the main balance and this blocked one. If the transaction fails, it will have to be tracked, the blocked balance should also be reduced by the amount of the transaction, but do not touch the total.
more beautiful, instead of one field, start a special table - current transactions, where, in accordance with the status, calculate this blocked balance (the status of the transaction is started, the transaction is completed or the transaction has broken off), this is relevant just in case when the transaction is completed long enough not to carry it out within the database transaction (otherwise the user will get at the time of maintenance, for example, restarting the database for an update, and his transaction will be lost), besides, you already have this table, just add more statuses

Answer 3

if the check passed, then both will be executed

Who said that the DBMS will allow you to perform both transactions with the same source data?
If both transactions started executing in parallel, read the same data, and are trying to overwrite it, how will the DBMS behave? Will it allow both transactions to work at all? Or will one of them wait until the other is done? The question is much more interesting than it seems. And, most importantly, intelligent people have already thought about it. Very well thought .
The postgres docs are even better.

Or is it better to recalculate every time from history?

Take the trouble to recalculate, this is not scalable, the complexity of the calculation will grow all the time. If you think that you can mess up with the current balance - make it possible to recalculate it according to the history of replenishment / spending. This is called denormalized data. This is one of those cases where it is justified to use stored procedures to update such data. Those. instead of directly writing both to the deposit/spending history and the current balance directly from the application, you instead call a stored procedure that atomically writes a new operation - this is your main data - and changes your denormalized data as needed - i.e. your balance. At the same time, in the same storage, you can additionally check the possibility of debiting. This solution does not scale very well, and in general, storage is an anti-pattern for modern, trendy, youthful distributed applications, but judging by your questions, it is unlikely that you are responsible for developing a service where there are tens of thousands of such write-offs per second, so that's enough for you.
Here on SOstill offer many solutions to this classic problem, none of which is ideal and best for all situations.

Answer 4

The spending consists of three stages. Reading the user's balance, checking whether there are enough funds and actually recording a new balance.

Strange approach. It is enough to impose a balance non-negativity constraint on the table and perform the operation unconditionally. If there are not enough funds, the restriction will work, the server will not perform data changes and will return an error. One request, and no problems with parallel execution with the correct default isolation level.

the user's balance is calculated on the fly and is not stored in the user. It is calculated based on the history of its replenishment / expenses.

In this case, a trigger that will calculate the current balance, check the condition, and generate an error in case of insufficient funds. Although I would still think about keeping the current balance, at least for a certain point in time (say, at midnight, in order to add up only for the operations of the current day, and not for the entire history). Rare errors in it, even if they cannot be avoided, will cost less than a system that can calculate the balance on the fly every time and breed conflicts.

If in a multi-threaded application it happens that two threads read the balance, check whether there are enough funds for the purchase and this stage is successful for both, then two entries will follow, which will drive the user into a minus. What are the options for double spending protection?

Just the level of isolation. With the right level chosen, what the hell will the second read until the first completes its transaction and releases resources.

Answer 5

Locks in the database are always not very good, and with serializable the performance of the database will drop to the floor. Constraint as an option, but it will only help for this situation and it turns out that the logic was dragged into the base.
The best option is to simply divide the work between separate threads, i.e. a specific user is served by a specific thread and no other, then there will be no race in principle. But this is if your architecture allows.

Answer 6

Add a version field to the table.
Do everything in a transaction (level not lower than Read committed):
1. When selecting a row from the database, save the version value in your program.
2. When updating, do this:
Update ... set balance = balance - purchase amount, version=version + 1 where version = saved version value.
3. If the request is successful, then the number of rows changed = 1, if nothing has changed = 0.
All this is called optimistic locking

Answer 7

1) Use transaction isolation level: serializable
but Update performance will drop, so it's better to take measurements and see if it fits or not. Usually there are not as many Updates as there are reads.
Advantage : you won't have to dance with a tambourine.
2) To make this operation through a class which controls. Those. if we make a request a second time, it's the class that looks to see if the first one has completed.
And here there are already 2 options:
- answer the user with a refusal (like: "wait for the previous operation to complete")
- wait for the first one to complete and proceed with the second one. But if after the first, the balance is <0, then tell the user about it.