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[&#39;store&#39;] > 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(&#39;库存减少成功，下单成功&#39;);
    } else {
        echo "更新失败";
        insertLog(&#39;库存减少失败&#39;);
    }

} else {
    echo "没有库存";
    insertLog(&#39;库存不够&#39;);
}

function db()
{
    global $con;
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    if (!$con) {
        echo "数据库连接失败";
    }
}

/**
 * 生成唯一订单号
 */
function build_order_no()
{
    return date(&#39;Ymd&#39;) . str_pad(mt_rand(1, 99999), 5, &#39;0&#39;, STR_PAD_LEFT);
}

function create_order($oid, $product_id, $number)
{
    global $con;
    $sql = "INSERT INTO `order` (oid, product_id, number) values(&#39;$oid&#39;, &#39;$product_id&#39;, &#39;$number&#39;)";
    mysqli_query($con, $sql);
}

/**
 * 记录日志
 */
function insertLog($content)
{
    global $con;
    $sql = "INSERT INTO `order_log` (content) values(&#39;$content&#39;)";
    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[&#39;store&#39;] > 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(&#39;库存减少成功，下单成功&#39;);
    } else {
        // 如果出现负数将返回false
        echo "更新失败";
        insertLog(&#39;库存减少失败&#39;);
    }
} else {
    //商品已经抢购完
    echo "没有库存";
    insertLog(&#39;库存不够&#39;);
}

function db()
{
    global $con;
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    if (!$con) {
        echo "数据库连接失败";
    }
}

/**
 * 生成唯一订单号
 */
function build_order_no()
{
    return date(&#39;Ymd&#39;) . str_pad(mt_rand(1, 99999), 5, &#39;0&#39;, STR_PAD_LEFT);
}

function create_order($oid, $product_id, $number)
{
    global $con;
    $sql = "INSERT INTO `order` (oid, product_id, number) values(&#39;$oid&#39;, &#39;$product_id&#39;, &#39;$number&#39;)";
    mysqli_query($con, $sql);
}

/**
 * 记录日志
 */
function insertLog($content)
{
    global $con;
    $sql = "INSERT INTO `order_log` (content) values(&#39;$content&#39;)";
    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[&#39;store&#39;] > 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(&#39;库存减少成功，下单成功&#39;);
        mysqli_query($con, "COMMIT");//事务提交即解锁
    } else {
        echo "更新失败";
        insertLog(&#39;库存减少失败&#39;);
        mysqli_query($con, "ROLLBACK");//事务回滚即解锁
    }
} else {
    //商品已经抢购完
    echo "没有库存";
    insertLog(&#39;库存不够&#39;);
    mysqli_query($con, "ROLLBACK");//事务回滚即解锁
}

function db()
{
    global $con;
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    if (!$con) {
        echo "数据库连接失败";
    }
}

/**
 * 生成唯一订单号
 */
function build_order_no()
{
    return date(&#39;Ymd&#39;) . str_pad(mt_rand(1, 99999), 5, &#39;0&#39;, STR_PAD_LEFT);
}

function create_order($oid, $product_id, $number)
{
    global $con;
    $sql = "INSERT INTO `order` (oid, product_id, number) values(&#39;$oid&#39;, &#39;$product_id&#39;, &#39;$number&#39;)";
    mysqli_query($con, $sql);
}

/**
 * 记录日志
 */
function insertLog($content)
{
    global $con;
    $sql = "INSERT INTO `order_log` (content) values(&#39;$content&#39;)";
    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(&#39;lock.txt&#39;, &#39;w&#39;);
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[&#39;store&#39;] > 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(&#39;库存减少成功，下单成功&#39;);
        } else {
            echo "更新失败";
            insertLog(&#39;库存减少失败&#39;);
        }
    } else {
        //商品已经抢购完
        echo "没有库存";
        insertLog(&#39;库存不够&#39;);
    }
    flock($fp, LOCK_UN); //释放锁

}
fclose($fp);

function db()
{
    global $con;
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    if (!$con) {
        echo "数据库连接失败";
    }
}

/**
 * 生成唯一订单号
 */
function build_order_no()
{
    return date(&#39;Ymd&#39;) . str_pad(mt_rand(1, 99999), 5, &#39;0&#39;, STR_PAD_LEFT);
}

function create_order($oid, $product_id, $number)
{
    global $con;
    $sql = "INSERT INTO `order` (oid, product_id, number) values(&#39;$oid&#39;, &#39;$product_id&#39;, &#39;$number&#39;)";
    mysqli_query($con, $sql);
}

/**
 * 记录日志
 */
function insertLog($content)
{
    global $con;
    $sql = "INSERT INTO `order_log` (content) values(&#39;$content&#39;)";
    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(&#39;lock.txt&#39;, &#39;w&#39;);
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[&#39;store&#39;] > 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(&#39;库存减少成功，下单成功&#39;);
        } else {
            echo "更新失败";
            insertLog(&#39;库存减少失败&#39;);
        }
    } else {
        //商品已经抢购完
        echo "没有库存";
        insertLog(&#39;库存不够&#39;);
    }
    flock($fp, LOCK_UN); //释放锁

} else {
    //系统繁忙，请稍后再试
    echo "系统繁忙，请稍后再试";
    insertLog(&#39;系统繁忙，请稍后再试&#39;);
}
fclose($fp);

function db()
{
    global $con;
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    if (!$con) {
        echo "数据库连接失败";
    }
}

/**
 * 生成唯一订单号
 */
function build_order_no()
{
    return date(&#39;Ymd&#39;) . str_pad(mt_rand(1, 99999), 5, &#39;0&#39;, STR_PAD_LEFT);
}

function create_order($oid, $product_id, $number)
{
    global $con;
    $sql = "INSERT INTO `order` (oid, product_id, number) values(&#39;$oid&#39;, &#39;$product_id&#39;, &#39;$number&#39;)";
    mysqli_query($con, $sql);
}

/**
 * 记录日志
 */
function insertLog($content)
{
    global $con;
    $sql = "INSERT INTO `order_log` (content) values(&#39;$content&#39;)";
    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[&#39;store&#39;];

// 获取商品在redis缓存的库存
$redis = new Redis();
$result = $redis->connect(&#39;127.0.0.1&#39;, 6379);
$key = &#39;goods_store_&#39; . $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(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    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(&#39;127.0.0.1&#39;,6379);
$count = $redis->lpop(&#39;goods_store_&#39; . $product_id);
if (!$count) {
    insertLog(&#39;error:no store redis&#39;);
    return &#39;秒杀结束，没有商品库存了&#39;;
}

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(&#39;库存减少成功，下单成功&#39;);
} else {
    echo "更新失败";
    insertLog(&#39;库存减少失败&#39;);
}

function db()
{
    global $con;
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    if (!$con) {
        echo "数据库连接失败";
    }
}

/**
 * 生成唯一订单号
 */
function build_order_no()
{
    return date(&#39;Ymd&#39;) . str_pad(mt_rand(1, 99999), 5, &#39;0&#39;, STR_PAD_LEFT);
}

function create_order($oid, $product_id, $number)
{
    global $con;
    $sql = "INSERT INTO `order` (oid, product_id, number) values(&#39;$oid&#39;, &#39;$product_id&#39;, &#39;$number&#39;)";
    mysqli_query($con, $sql);
}

/**
 * 记录日志
 */
function insertLog($content)
{
    global $con;
    $sql = "INSERT INTO `order_log` (content) values(&#39;$content&#39;)";
    mysqli_query($con, $sql);
}

redis optimistic lock prevention Oversold

<?php

$redis =new Redis();
$redis->connect("127.0.0.1", 6379);
$redis->watch(&#39;sales&#39;);//乐观锁 监视作用 set()  初始值0
$sales = $redis->get(&#39;sales&#39;);

$n = 20;// 库存
if ($sales >= $n) {
    exit(&#39;秒杀结束&#39;);
}

//redis开启事务
$redis->multi();
$redis->incr(&#39;sales&#39;); //将 key 中储存的数字值增一 ,如果 key 不存在，那么 key 的值会先被初始化为 0 ，然后再执行 INCR 操作。
$res = $redis->exec(); //成功1 失败0

if ($res) {
    //秒杀成功
    $con = new mysqli(&#39;localhost&#39;,&#39;root&#39;,&#39;root&#39;,&#39;test&#39;);
    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(&#39;抢购失败&#39;);
}

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!