So my team is developing an API in PHP . My main concern is what happen 2 people call to update the same row.
So if one API call will do a read then process info then write, what happens if another API call does the same thing for the same row of data. We are using Postgres OR Mysql.
User has 100 dollars.
API Call 1 to ADD 20 1 reads 100.
API Call 2 to Subtract 20 reads 100.
API Call 1 writes 120;
API Call 2 write 80;( instead of 100);
I know there is a solution involving locking tables or rows, will that solve my problem?
I need a solution where API call 2 doesn't fail, but rather waits or retries.
[EDIT]
I should go in further detail of what we are doing. It's a browser mmo game that where it has 2 parts. A PHP REST API and Java server.
The browser makes AJAX GET API calls ex. : Build Factory for $40k PHP API Check if enough funds, and return JSON. SO -$40k from player.
Our Server ticks have concurrent database calls using boneCP to Update Values like Funds.
So from my previous example CALL 1 would be our PHP API and CALL 2 would be the server ticks.
You need to learn about transactions, transaction isolation, and explicit row-level locking:
select ... for update
In your case, also look into using expressions. Your life would be much simpler if you issued someting like:
update account set amount = amount - 20 where id = ?
Your API is badly designed and needs to be fixed. Instead of:
You should instead have a flow like:
Similarly, if you're transferring between two accounts you would always use an API call that does a transfer rather than doing an add, then a separate subtract or vice versa.
In other words, you need to redesign your API so that your database state is consistent at the start of, and end of, every API call. To ensure that an API call that uses multiple SQL statements is consistent even if aborted half way through you must wrap the API call's work up in a transaction and if you're not going to use SERIALIZABLE isolation, do appropriate SELECT ... FOR UPDATE commands if you'll be doing a read-modify-write within the transaction.
You need to avoid read-modify-write cycles by client applications.
In situations where a read-modify-write is unavoidable, so two or more API calls must be kept consistent, you should use optimistic concurrency control to protect against update collisions. In that case your scenario would play out as follows: