Why did you choose PHP file lock instead of MySQL lock mechanism when buying, and what are the special advantages of PHP file lock compared with it?

  mysql, question

May I ask why PHP file lock was chosen instead of MySQL lock mechanism when buying, and what are the special advantages of PHP file lock?

InfoQ Interview with Ding Qi: Alibaba Cloud to Open Source AliSQL, MySQL Branch Optimized for Seconds
E-commerce’s second kill (panic buying) scene is actually to reduce inventory, and for the database, it is to update a record.
Because of the characteristics of transactions, the update of a single record must be completed serially.
But the characteristic of seconds kill is that at a certain moment, a large number of concurrent inventory reductions are carried out.
This causes a large number of threads to be in deadlock detection state because they cannot acquire locks.
It consumed a large amount of Central Processor resources, eventually causing the system to be unable to respond and causing Avalanche effect.
AliSQL provides queuing and current limiting functions for such scenarios.
It has passed the test of high concurrent requests in Zero Hour, double 11, and has maintained the stability and continuous throughput of the system.
There are two big challenges to the database in E-commerce Peak:
1. Super concurrency
MySQL has an upper limit on the number of concurrent active connections it can support, ideally about (Central Processor Core Number ×2) active connections.
When the number of active connections exceeds this value, the performance will drop sharply, resulting in the unavailability of the entire service.
AliSQL has water level control, when the pressure exceeds the processing capacity of the database, it will voluntarily give up the later request.
This ensures that the database can maintain a high throughput that can respond normally.
2. The second kill scene
There is a problem of stock reduction in the second kill scenario.
When a large number of users snap up the same commodity at the same time, they need to update their inventory at the same time.
At this time, InnoDB’s row lock and deadlock detection mechanism will cause the database Central Processor to be occupied in a short period of time, causing the entire database to be almost unresponsive.
In AliSQL, we have a special plan for seconds killing, which ensures that TPS can still maintain a high level when a large number of threads reduce inventory at the same time.
In addition to Ali’s own seconds kill business, this function is also applicable to businesses such as Red Wars, which have undergone a large number of business verifications during the Spring Festival in 2015 and 2016.

Xiaomi Net First Edition Buying System:
On the PHP server, a file is used to indicate whether the goods are sold out. If the file exists, it means that the goods have been sold out.
After receiving the user’s panic buying request, the PHP program checks to see if the user has made an appointment and snapped up, and then checks whether the sold-out flag file exists.
For reserved users, if they have not sold out and have not snapped up successfully, the results of the successful snapping up will be returned and a log will be recorded.
Logs are transmitted to the central control node asynchronously to complete operations such as counting.
Finally, the list of successfully snapped up users is asynchronously imported into the store system, and the successfully snapped up users can place orders within the next few hours.
In this way, the rush hour is completely blocked by the panic buying system, and Online Mall system does not need to face high traffic.
The whole system uses about 30 servers, including 20 PHP servers and 10 Redis servers.

As can be seen from the experience of Ali and Xiaomi above, when buying commodities on a large scale and concurrently,
If MySQL is directly connected to UPDATE, the database will not hold up.
Ali’s method is to optimize MySQL, Xiaomi’s method is to use PHP plus Redis to handle it first.