Backend Development
PHP Tutorial
How to prevent product inventory from being oversold under high concurrency conditions in PHP
How to prevent product inventory from being oversold under high concurrency conditions in PHP
This article brings you relevant knowledge about PHP, which mainly introduces related issues about preventing overselling of product inventory under high concurrency conditions, and mainly solves the problems caused by high concurrency on the database. I hope this helps everyone on how to solve oversold inventory under pressure and competition.
You can view the test based on this article through "php High Concurrency Test: Case Study on Preventing Overselling of Inventory" case. [Recommended study: "PHP Tutorial"]
In the mall system, rush sales and flash sales are very common marketing scenarios. Within a certain period of time, a large number of users visit the mall to place orders. , there are two main problems that need to be solved:
The pressure caused by high concurrency on the database;
How to solve the problem of product inventory overflow under competition conditions Sell;
The pressure caused by high concurrency on the database
For the first problem, use cache to deal with it, Avoid directly operating the database, such as using Redis.
How to solve the oversold inventory of goods under competition
For the second question, it is necessary to focus on the explanation.
Conventional writing method: Query the inventory of the corresponding product, determine whether the inventory quantity is greater than 0, and then perform operations such as generating an order. However, when determining whether the inventory is greater than 0, there will be problems under high concurrency, resulting in Inventory levels are negative.
Test table sql
Import the following table data into the database
/* Navicat MySQL Data Transfer Source Server : 01 本地localhost Source Server Version : 50553 Source Host : localhost:3306 Source Database : test Target Server Type : MYSQL Target Server Version : 50553 File Encoding : 65001 Date: 2020-11-06 14:31:35 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for products -- ---------------------------- DROP TABLE IF EXISTS `products`; CREATE TABLE `products` ( `id` int(10) NOT NULL AUTO_INCREMENT COMMENT 'ID', `title` varchar(50) DEFAULT NULL COMMENT '货品名称', `store` int(11) DEFAULT '0' COMMENT '货品库存', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='货品表'; -- ---------------------------- -- Records of products -- ---------------------------- INSERT INTO `products` VALUES ('1', '稻花香大米', '20'); -- ---------------------------- -- Table structure for order_log -- ---------------------------- DROP TABLE IF EXISTS `order_log`; CREATE TABLE `order_log` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `content` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日志内容', `c_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Table structure for order -- ---------------------------- DROP TABLE IF EXISTS `order`; CREATE TABLE `order` ( `oid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '订单号', `product_id` int(11) DEFAULT '0' COMMENT '商品ID', `number` int(11) DEFAULT '0' COMMENT '购买数量', `c_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`oid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='订单表';
Place an order Processing code
<?php
db();
global $con;
//step1 接收下单参数
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
//step2 查询商品信息
$sql = "select * from products where id={$product_id}";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
//step3 判断商品下单数量是否大于商品库存数量
//此处在高并发下，可能出现上一个下单后还没来得及更新库存，下一个下单判断库存数不是最新的库存
if ($row['store'] > 0) {
sleep(1);
//step4 更新商品库存数量（减去下单数量）
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "更新成功";
//step5 生成订单号创建订单
$oid = build_order_no();
create_order($oid, $product_id, $buy_num);
insertLog('库存减少成功，下单成功');
} else {
echo "更新失败";
insertLog('库存减少失败');
}
} else {
echo "没有库存";
insertLog('库存不够');
}
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}
/**
* 生成唯一订单号
*/
function build_order_no()
{
return date('Ymd') . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT);
}
function create_order($oid, $product_id, $number)
{
global $con;
$sql = "INSERT INTO `order` (oid, product_id, number) values('$oid', '$product_id', '$number')";
mysqli_query($con, $sql);
}
/**
* 记录日志
*/
function insertLog($content)
{
global $con;
$sql = "INSERT INTO `order_log` (content) values('$content')";
mysqli_query($con, $sql);
}Set the inventory field field to unsigned
Because the inventory field cannot be a negative number, after placing the order When updating product inventory, if a negative number appears, false will be returned
<?php
db();
global $con;
//step1 接收下单参数
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
//step2 查询商品信息
$sql = "select * from products where id={$product_id} for UPDATE";//利用for update 开启行锁
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
//step3 判断商品下单数量是否大于商品库存数量
if ($row['store'] > 0) {
sleep(1);
//step4 更新商品库存数量（减去下单数量）
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "更新成功";
//step5 生成订单号创建订单
$oid = build_order_no();
create_order($oid, $product_id, $buy_num);
insertLog('库存减少成功，下单成功');
} else {
// 如果出现负数将返回false
echo "更新失败";
insertLog('库存减少失败');
}
} else {
//商品已经抢购完
echo "没有库存";
insertLog('库存不够');
}
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}
/**
* 生成唯一订单号
*/
function build_order_no()
{
return date('Ymd') . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT);
}
function create_order($oid, $product_id, $number)
{
global $con;
$sql = "INSERT INTO `order` (oid, product_id, number) values('$oid', '$product_id', '$number')";
mysqli_query($con, $sql);
}
/**
* 记录日志
*/
function insertLog($content)
{
global $con;
$sql = "INSERT INTO `order_log` (content) values('$content')";
mysqli_query($con, $sql);
}Use mysql transactions to lock the rows of the operation
In the order processing process , use mysql transactions to lock the row data of the product that is placing the order
<?php
db();
global $con;
//step1 接收下单参数
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
mysqli_query($con, "BEGIN"); //开始事务
//step2 查询商品信息
$sql = "select * from products where id={$product_id} for UPDATE";//利用for update 开启行锁
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
//step3 判断商品下单数量是否大于商品库存数量
if ($row['store'] > 0) {
sleep(1);
//step4 更新商品库存数量（减去下单数量）
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "更新成功";
//step5 生成订单号创建订单
$oid = build_order_no();
create_order($oid, $product_id, $buy_num);
insertLog('库存减少成功，下单成功');
mysqli_query($con, "COMMIT");//事务提交即解锁
} else {
echo "更新失败";
insertLog('库存减少失败');
mysqli_query($con, "ROLLBACK");//事务回滚即解锁
}
} else {
//商品已经抢购完
echo "没有库存";
insertLog('库存不够');
mysqli_query($con, "ROLLBACK");//事务回滚即解锁
}
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}
/**
* 生成唯一订单号
*/
function build_order_no()
{
return date('Ymd') . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT);
}
function create_order($oid, $product_id, $number)
{
global $con;
$sql = "INSERT INTO `order` (oid, product_id, number) values('$oid', '$product_id', '$number')";
mysqli_query($con, $sql);
}
/**
* 记录日志
*/
function insertLog($content)
{
global $con;
$sql = "INSERT INTO `order_log` (content) values('$content')";
mysqli_query($con, $sql);
}Use non-blocking file exclusive lock
While processing the order When making a request, flock is used to lock a file. If the lock fails, it means that other orders are being processed. At this time, the user must either wait or directly prompt the user that "server is busy". The counter stores the number of purchased products to avoid querying the database.
Blocking (waiting) mode: During concurrency, when there is a second user request, the program will wait for the first user request to complete, release the lock, and obtain the file lock before the program will continue to run.
<?php
db();
global $con;
//step1 接收下单参数
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
$fp = fopen('lock.txt', 'w');
if (flock($fp, LOCK_EX)) { //文件独占锁,阻塞
//step2 查询商品信息
$sql = "select * from products where id={$product_id}";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
//step3 判断商品下单数量是否大于商品库存数量
if ($row['store'] > 0) {
//处理订单
sleep(1);
//step4 更新商品库存数量（减去下单数量）
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "更新成功";
//step5 生成订单号创建订单
$oid = build_order_no();
create_order($oid, $product_id, $buy_num);
insertLog('库存减少成功，下单成功');
} else {
echo "更新失败";
insertLog('库存减少失败');
}
} else {
//商品已经抢购完
echo "没有库存";
insertLog('库存不够');
}
flock($fp, LOCK_UN); //释放锁
}
fclose($fp);
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}
/**
* 生成唯一订单号
*/
function build_order_no()
{
return date('Ymd') . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT);
}
function create_order($oid, $product_id, $number)
{
global $con;
$sql = "INSERT INTO `order` (oid, product_id, number) values('$oid', '$product_id', '$number')";
mysqli_query($con, $sql);
}
/**
* 记录日志
*/
function insertLog($content)
{
global $con;
$sql = "INSERT INTO `order_log` (content) values('$content')";
mysqli_query($con, $sql);
}Non-blocking mode: During concurrency, the first user requests and obtains the file lock. Users who request later directly return to the system busy, please try again later
<?php
db();
global $con;
//step1 接收下单参数
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
$fp = fopen('lock.txt', 'w');
if (flock($fp, LOCK_EX|LOCK_NB)) { //文件独占锁,非阻塞
//step2 查询商品信息
$sql = "select * from products where id={$product_id}";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
//step3 判断商品下单数量是否大于商品库存数量
if ($row['store'] > 0) {
//处理订单
sleep(1);
//step4 更新商品库存数量（减去下单数量）
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "更新成功";
//step5 生成订单号创建订单
$oid = build_order_no();
create_order($oid, $product_id, $buy_num);
insertLog('库存减少成功，下单成功');
} else {
echo "更新失败";
insertLog('库存减少失败');
}
} else {
//商品已经抢购完
echo "没有库存";
insertLog('库存不够');
}
flock($fp, LOCK_UN); //释放锁
} else {
//系统繁忙，请稍后再试
echo "系统繁忙，请稍后再试";
insertLog('系统繁忙，请稍后再试');
}
fclose($fp);
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}
/**
* 生成唯一订单号
*/
function build_order_no()
{
return date('Ymd') . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT);
}
function create_order($oid, $product_id, $number)
{
global $con;
$sql = "INSERT INTO `order` (oid, product_id, number) values('$oid', '$product_id', '$number')";
mysqli_query($con, $sql);
}
/**
* 记录日志
*/
function insertLog($content)
{
global $con;
$sql = "INSERT INTO `order_log` (content) values('$content')";
mysqli_query($con, $sql);
}Use redis queue
because of pop The operation is atomic. Even if many users arrive at the same time, they will be executed sequentially. It is recommended to use
mysql transaction performance drops severely under high concurrency, and the file lock method is also
1. First put the product inventory into the redis queue
<?php
db();
global $con;
// 查询商品信息
$product_id = 1;
$sql = "select * from products where id={$product_id}";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);
$store = $row['store'];
// 获取商品在redis缓存的库存
$redis = new Redis();
$result = $redis->connect('127.0.0.1', 6379);
$key = 'goods_store_' . $product_id;
$res = $redis->llen($key);
$count = $store - $res;
for ($i=0; $i<$count; $i++) {
$redis->lpush($key, 1);
}
echo $redis->llen($key);
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}2. Rush buying and flash sale logic
<?php
db();
global $con;
//step1 接收下单参数
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
//step2 下单前判断redis队列库存量
$redis = new Redis();
$result = $redis->connect('127.0.0.1',6379);
$count = $redis->lpop('goods_store_' . $product_id);
if (!$count) {
insertLog('error:no store redis');
return '秒杀结束，没有商品库存了';
}
sleep(1);
//step3 更新商品库存数量（减去下单数量）
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "更新成功";
//step4 生成订单号创建订单
$oid = build_order_no();
create_order($oid, $product_id, $buy_num);
insertLog('库存减少成功，下单成功');
} else {
echo "更新失败";
insertLog('库存减少失败');
}
function db()
{
global $con;
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
}
/**
* 生成唯一订单号
*/
function build_order_no()
{
return date('Ymd') . str_pad(mt_rand(1, 99999), 5, '0', STR_PAD_LEFT);
}
function create_order($oid, $product_id, $number)
{
global $con;
$sql = "INSERT INTO `order` (oid, product_id, number) values('$oid', '$product_id', '$number')";
mysqli_query($con, $sql);
}
/**
* 记录日志
*/
function insertLog($content)
{
global $con;
$sql = "INSERT INTO `order_log` (content) values('$content')";
mysqli_query($con, $sql);
}redis optimistic lock prevention Oversold
<?php
$redis =new Redis();
$redis->connect("127.0.0.1", 6379);
$redis->watch('sales');//乐观锁 监视作用 set() 初始值0
$sales = $redis->get('sales');
$n = 20;// 库存
if ($sales >= $n) {
exit('秒杀结束');
}
//redis开启事务
$redis->multi();
$redis->incr('sales'); //将 key 中储存的数字值增一 ,如果 key 不存在，那么 key 的值会先被初始化为 0 ，然后再执行 INCR 操作。
$res = $redis->exec(); //成功1 失败0
if ($res) {
//秒杀成功
$con = new mysqli('localhost','root','root','test');
if (!$con) {
echo "数据库连接失败";
}
$product_id = 1;// 商品ID
$buy_num = 1;// 购买数量
sleep(1);
$sql = "update products set store=store-{$buy_num} where id={$product_id}";
if (mysqli_query($con, $sql)) {
echo "秒杀完成";
}
} else {
exit('抢购失败');
}Recommended learning: "PHP Video Tutorial"
The above is the detailed content of How to prevent product inventory from being oversold under high concurrency conditions in PHP. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1664
14
1423
52
1321
25
1269
29
1249
24
Explain JSON Web Tokens (JWT) and their use case in PHP APIs.
Apr 05, 2025 am 12:04 AM
JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,
PHP Program to Count Vowels in a String
Feb 07, 2025 pm 12:12 PM
A string is a sequence of characters, including letters, numbers, and symbols. This tutorial will learn how to calculate the number of vowels in a given string in PHP using different methods. The vowels in English are a, e, i, o, u, and they can be uppercase or lowercase. What is a vowel? Vowels are alphabetic characters that represent a specific pronunciation. There are five vowels in English, including uppercase and lowercase: a, e, i, o, u Example 1 Input: String = "Tutorialspoint" Output: 6 explain The vowels in the string "Tutorialspoint" are u, o, i, a, o, i. There are 6 yuan in total
What are PHP magic methods (__construct, __destruct, __call, __get, __set, etc.) and provide use cases?
Apr 03, 2025 am 12:03 AM
What are the magic methods of PHP? PHP's magic methods include: 1.\_\_construct, used to initialize objects; 2.\_\_destruct, used to clean up resources; 3.\_\_call, handle non-existent method calls; 4.\_\_get, implement dynamic attribute access; 5.\_\_set, implement dynamic attribute settings. These methods are automatically called in certain situations, improving code flexibility and efficiency.
Explain late static binding in PHP (static::).
Apr 03, 2025 am 12:04 AM
Static binding (static::) implements late static binding (LSB) in PHP, allowing calling classes to be referenced in static contexts rather than defining classes. 1) The parsing process is performed at runtime, 2) Look up the call class in the inheritance relationship, 3) It may bring performance overhead.
PHP and Python: Comparing Two Popular Programming Languages
Apr 14, 2025 am 12:13 AM
PHP and Python each have their own advantages, and choose according to project requirements. 1.PHP is suitable for web development, especially for rapid development and maintenance of websites. 2. Python is suitable for data science, machine learning and artificial intelligence, with concise syntax and suitable for beginners.
PHP in Action: Real-World Examples and Applications
Apr 14, 2025 am 12:19 AM
PHP is widely used in e-commerce, content management systems and API development. 1) E-commerce: used for shopping cart function and payment processing. 2) Content management system: used for dynamic content generation and user management. 3) API development: used for RESTful API development and API security. Through performance optimization and best practices, the efficiency and maintainability of PHP applications are improved.
PHP: A Key Language for Web Development
Apr 13, 2025 am 12:08 AM
PHP is a scripting language widely used on the server side, especially suitable for web development. 1.PHP can embed HTML, process HTTP requests and responses, and supports a variety of databases. 2.PHP is used to generate dynamic web content, process form data, access databases, etc., with strong community support and open source resources. 3. PHP is an interpreted language, and the execution process includes lexical analysis, grammatical analysis, compilation and execution. 4.PHP can be combined with MySQL for advanced applications such as user registration systems. 5. When debugging PHP, you can use functions such as error_reporting() and var_dump(). 6. Optimize PHP code to use caching mechanisms, optimize database queries and use built-in functions. 7
The Enduring Relevance of PHP: Is It Still Alive?
Apr 14, 2025 am 12:12 AM
PHP is still dynamic and still occupies an important position in the field of modern programming. 1) PHP's simplicity and powerful community support make it widely used in web development; 2) Its flexibility and stability make it outstanding in handling web forms, database operations and file processing; 3) PHP is constantly evolving and optimizing, suitable for beginners and experienced developers.
