Text

 

Here we mainly use Redis Of setnx To handle high concurrency .

setnx There are two parameters . The first parameter represents the key . The second parameter represents the value . If the current key does not exist , The current key is inserted , Take the second parameter as the value . return 1. If the current key exists , Then it will return 0.

Create inventory table
CREATE TABLE `storage` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) DEFAULT NULL,   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
 

Set initial inventory to 10

Create order table
CREATE TABLE `order` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(11) DEFAULT NULL,   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
 

When testing without locks
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'root');
$sql="select `number` from  storage where id=1 limit 1";
$res = $pdo->query($sql)->fetch(); $number = $res['number']; if($number>0) {
    $sql ="insert into `order`  VALUES (null,$number)";
    $order_id = $pdo->query($sql);     if($order_id)     {
        $sql="update storage set `number`=`number`-1 WHERE id=1";
        $pdo->query($sql);     } }
 

ab Test simulation concurrency , The inventory was found to be correct .
mysql> select * from storage; +----+--------+ | id | number | +----+--------+
|  1 |      0 | +----+--------+ 1 row in set (0.00 sec)
 

Looking at the order form
mysql> select * from `order`; +----+--------+ | id | number | +----+--------+
|  1 |     10 | |  2 |     10 | |  3 |      9 | |  4 |      7 | |  5 |      6 |
|  6 |      5 | |  7 |      5 | |  8 |      5 | |  9 |      4 | | 10 |      1 |
+----+--------+ 10 rows in set (0.00 sec)
It is found that there are several orders with the same inventory data , This may lead to oversold .

Modify code to add redis Lock for data control
<?php /**  * Created by PhpStorm.  * User: daisc  * Date: 2018/7/23
 * Time: 14:45  */ class Lock {     private static $_instance ;
    private   $_redis;     private function __construct()     {
        $this->_redis =  new Redis();
        $this->_redis ->connect('127.0.0.1');     }
    public static function getInstance()     {
        if(self::$_instance instanceof self)         {
            return self::$_instance;         }
        return self::$_instance = new  self();     }     /**
     * @function  Lock      * @param $key  Lock name      * @param $expTime  Expiration time       */
    public function set($key,$expTime)     {         // Preliminary locking
        $isLock = $this->_redis->setnx($key,time()+$expTime);
        if($isLock)         {             return true;         }         else
        {             // In case of lock failure . Determine whether the lock already exists , If the lock exists, it has expired , Then delete the lock . Re lock
            $val = $this->_redis->get($key);             if($val&&$val<time())
            {                 $this->del($key);             }
            return  $this->_redis->setnx($key,time()+$expTime);         }     }
    /**      * @param $key  Unlock      */     public function del($key)     {
        $this->_redis->del($key);     } }
$pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'root');
$lockObj = Lock::getInstance(); // The judgment is that it can lock successfully
if($lock = $lockObj->set('storage',10)) {
    $sql="select `number` from  storage where id=1 limit 1";
    $res = $pdo->query($sql)->fetch();     $number = $res['number'];
    if($number>0)     {
        $sql ="insert into `order`  VALUES (null,$number)";
        $order_id = $pdo->query($sql);         if($order_id)         {
            $sql="update storage set `number`=`number`-1 WHERE id=1";
            $pdo->query($sql);         }     }     // Unlock
    $lockObj->del('storage'); } else {     // Locking failed to perform other operations . }
 

Again ab test , View test results
mysql> select * from `order`; +----+--------+ | id | number | +----+--------+
|  1 |     10 | |  2 |      9 | |  3 |      8 | |  4 |      7 | |  5 |      6 |
|  6 |      5 | |  7 |      4 | |  8 |      3 | |  9 |      2 | | 10 |      1 |
+----+--------+ 10 rows in set (0.00 sec)
 

It is found that the order table does not operate on the same inventory data . So use it redis Locks can effectively handle high concurrency .

When locking here, you don't need to judge the expiration time , In order to avoid a deadlock here , So add an expiration time judgment . Delete the lock when it is expired .

Technology
©2020 ioDraw All rights reserved
MYSQL database DML Common commands log4j Method of printing exception stack information Children programming children's learning route re Common methods of modules SK Hynix World Premiere DDR5 Memory : The frequency goes up 5600MHz, Capacity up to 256GBspark.sql.shuffle.partitions and spark.default.parallelism The difference between Notes on core principles of reverse engineering ( One )——Hello World-1 Using function to realize the exchange of two numbers (C language ) Programmer's promotion 28 Sorting code of Guan 3 Pymongo index Summary of index related operations