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
AI Hentai Generator
Generate AI Hentai for free.
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
CakePHP Project Configuration
Sep 10, 2024 pm 05:25 PM
In this chapter, we will understand the Environment Variables, General Configuration, Database Configuration and Email Configuration in CakePHP.
PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian
Dec 24, 2024 pm 04:42 PM
PHP 8.4 brings several new features, security improvements, and performance improvements with healthy amounts of feature deprecations and removals. This guide explains how to install PHP 8.4 or upgrade to PHP 8.4 on Ubuntu, Debian, or their derivati
CakePHP Date and Time
Sep 10, 2024 pm 05:27 PM
To work with date and time in cakephp4, we are going to make use of the available FrozenTime class.
CakePHP File upload
Sep 10, 2024 pm 05:27 PM
To work on file upload we are going to use the form helper. Here, is an example for file upload.
CakePHP Routing
Sep 10, 2024 pm 05:25 PM
In this chapter, we are going to learn the following topics related to routing ?
Discuss CakePHP
Sep 10, 2024 pm 05:28 PM
CakePHP is an open-source framework for PHP. It is intended to make developing, deploying and maintaining applications much easier. CakePHP is based on a MVC-like architecture that is both powerful and easy to grasp. Models, Views, and Controllers gu
CakePHP Creating Validators
Sep 10, 2024 pm 05:26 PM
Validator can be created by adding the following two lines in the controller.
How To Set Up Visual Studio Code (VS Code) for PHP Development
Dec 20, 2024 am 11:31 AM
Visual Studio Code, also known as VS Code, is a free source code editor — or integrated development environment (IDE) — available for all major operating systems. With a large collection of extensions for many programming languages, VS Code can be c
