How does the ordering process of php + mysql ensure data integrity?

If there is only one item in stock, and there is only one last item, the inventory is 1. What if there are many people buying this item at the same time, it is impossible for both people to place orders successfully, the inventory is-1, so how can this problem be solved in php + mysql? Is the inventory information of a commodity separate into a table... if there is only one item in stock, and there is only one last item, the inventory is 1. What if there are many people buying this item at the same time, it is impossible for both people to place orders successfully, the inventory is-1, so how can this problem be solved in php + mysql?

Is it better to associate the inventory information of a commodity with the commodity information table separately or only use one table?

Need to use transactions, lock? Thank you for your practical solutions! Without such data integrity, I feel that no matter how much code is written, it can only be regarded as a personal work pediatrics. Otherwise, how can it be applied to actual projects, such as payment !!!

I sincerely hope that experienced people can guide me. Thank you !!!

Reply content:

If there is only one item in stock, and there is only one last item, the inventory is 1. What if there are many people buying this item at the same time, it is impossible for both people to place orders successfully, the inventory is-1, so how can this problem be solved in php + mysql?

Is it better to associate the inventory information of a commodity with the commodity information table separately or only use one table?

Need to use transactions, lock? Thank you for your practical solutions! Without such data integrity, I feel that no matter how much code is written, it can only be regarded as a personal work pediatrics. Otherwise, how can it be applied to actual projects, such as payment !!!

I sincerely hope that experienced people can guide me. Thank you !!!

Two ideas

1. Concurrency is converted to serial, And the concurrency is eliminated by queue or similar means, and a consumer in the background deducts inventory in turn

2. Concurrency & lock. The lock location can be in mysql or redis or any self-writing sever.

But in fact, there is still a certain success rate for the order to pay, so if the place where the order is stuck, it may be unable to be sold because the user gave up the payment, therefore, there are some non-technical issues that need to be considered (Which of the following is the preference for overselling, not selling, or cutting orders after payment ?), This inventory deduction concurrency issue does not necessarily occur when the order is placed

The other is very simple. See the following SQL
Update table set num = num-1 where num> 1
If you do not need to add any transactions, the second person will naturally fail to buy them.

Ordinary malls are unlikely to encounter such a situation. If seckilling is indeed possible, there are two solutions:
1. modify the configuration of php-fpm in a single process so that only one worker is working and the request sequence is ensured. This method can be used when concurrency is very large;
2. if it is not that big, our company's solution is to create another intermediate table, and insert a piece of data into the intermediate table no matter whether there is any goods or not after each order is placed, then get the auto-increment id of the data, and then find the number of IDS smaller than this id in the table based on this id. If the inventory number is equal or smaller than this number, indicates that the inventory is no longer available; otherwise, the purchase process is normal.
The first type is easy to understand. The second type does not seem to be clearly written. The landlord will take a look.

One idea:
The inventory record of this item is locked, and two inventory fields are required: existing inventory field total_cnt, order quantity inventory order_cnt; order record another table.
If A, B dual-purpose households place orders at the same time, mysql first processes A data request [B user request Wait], calculate the total_cnt-order_cnt, greater than 0, A user order successful, generate A record to the order table. In addition, order_cnt increases the purchase quantity of order A. After A successfully pays, it changes total_cnt. After processing A request, it then requests B. The principle is that it processes C requests ....
Order failed when total_cnt-order_cnt is 0 or less than 0...

17. How can I avoid a single requirement being executed multiple times? How can I ask my colleagues to help me with the same requirement?

Provides the following ideas:
Set inventory to unsigned and use try catch to catch exceptions when concurrent generation-1

I have encountered such A problem. User A sends A message to user B, user A sends one, user B's unread field + 1, and user B checks the message (obtains the message ), b. Each time A message is retrieved, the unread Field 1 is displayed. The logic itself does not ask A question, but when A sends A message very fast, it sends one message within milliseconds, at the same time, B also obtains the message in milliseconds (update the message, they open the chat window), so there is A problem. It is reasonable to say that when A stops sending the message, b's unread messages should be 0, but there are still a few unread messages in B at this time, and there is no transaction, just a normal table. How did this problem come about?