I Have a table named 'transactions', and another named 'users'
When a user requests to the server, i get from transactions his last transaction balance and increase or decrease the balance base on his request,
all works good,
but problem is when he tries to request 10 times or more in a second without considering the response something like async requests, the speed of sending request is more than speed of getting balance from database, so i have problem to calculate the new balance.
something like this:
Section A: balance:100, amount 0, new balance: 100
Section B: balance(got from section A):100, amount:-10, new balance: 90
Section C: balance(got from last section):100(because section b is not finished yet to set balance to 90), amount:-10, new balance: 90
When this is happening at the milliseconds The balance get at the section C is the balance in section A, not balance in section B! because section B is not finished yet to call database set the balance,90,So this is problem
a better example:
I know some solutions to handle this issue but i want to solve this problem in other way.
solutions i found:
1-Lock the database for current user at beginning of the section B and unlock it at the end of it and don't let section C starts until database(or user) is lock!
2-Throttle requests for each user,don't let users to send 10 request for example in a minute or something like that
For reasons i don't want to limit users from sending requests. and solution number 1 reduces performance of responding to users.
what algorithms do you recommend?
Thanks.
