多个应用程序访问相同的数据

Question

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.

Answer 1

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 = ?