How to avoid concurrent UPDATE of a MySQL database table?

Question

MySQL

I am developing a browser game that has a market. The market sells seeds and each user can put their seeds up for sale. There was a question of simultaneous use.
The table looks like this:

id | user_id | amount
1 | 4 | 0
2 | 1 | 30
3 | 5 | 10

In php, the algorithm of actions is as follows:
1 - I select the first record in the table with amount > 0 (that is, there are still seeds for sale)
2 - I update the record in the table, reducing its amount
In this case, it may be that there are several users at the same time go to the market and get the same record. (And this may be, since there is only one button and the action is simple).
That is, the algorithm is:

1 user - SELECT * FROM market WHERE amount > 0 LIMIT 1
2 user - SELECT * FROM market WHERE amount > 0 LIMIT 1
1 user - UPDATE market SET amount = amount - 10 WHERE id LIKE id

And at this moment the second user also updates the record, but he does not know that the number of seeds there has already decreased. How to be in this case?
I dug towards the following algorithm:

1. Get the amount of the last record and set it to zero
2. Then update the amount (subtract purchased seeds) and update the record in the table (return unpurchased seeds)

But in such a case (as far as I googled) it is not possible to do SELECT before UPDATE in one query.
What is the correct way to implement this in MySQL? Maybe change the algorithm?

Answer 1

Transactions are good for everyone, but under load they slow down the application and sometimes, if not properly handle requests to deadlocks.
You can create a separate table in which to record user requests for a resource, for example:
user ID, resource ID, application status (queued, processed, rejected), date and time of the application accurate to milliseconds (well, or a numeric field with auto-numbering).
When you click on "buy goods" the operation is performed in two steps, the first step is to place the user's request in this table. (several users can come for one resource and the one who came first will have priority)
The table is needed in order not to impose locks on the database tables (locks may be due to an open transaction)
Next, the second step is to launch the procedure for obtaining the status of the application (it should return a response whether the user received his resource or not, i.e. whether his application was processed successfully or rejected).
Request processing is a separate server process, it takes requests in order of receipt and processes (changes the state of the resource in the database and the state of the request) until the selected resource has enough quantity, all users who do not have enough resource receive a message about the impossibility to get the resource.

Answer 2

Read up on transactions, they lock fields while an UPDATE is in progress.

Answer 3

To buy, the user probably clicks on the button indicating the ID from the market table
So, at the time of purchase (before updating), do again select amount from market where id = 1)
and if amount > 0 then do an update, otherwise display to the user " sorry, someone already bought it