更新库存时，你是如何用mysql锁解决高并发问题的

友儿

发布于 2022-09-09 17:12:44

1.7K0

文章被收录于专栏：友儿友儿

利用Mysql的锁来解决高并发的问题，先看没有利用事务的时候并发的后果创建库存管理表

CREATE TABLE `storage` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

创建订单管理表

CREATE TABLE `order1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;
 

//测试代码
<?php
$pdo = new PDO('mysql:host=127.0.0.1;port=3306; dbname=storageorder', 'root', '123456');
$sql = "select `number` from storage where id=2 limit 1";
$res = $pdo->query($sql)->fetch();
$number = $res['number'];
if ($number > 0) {
    $sql = "insert into `order1` VALUES (null,$number)";
    $order_id = $pdo->query($sql);
    if ($order_id) {
        $sql = "update storage set `number`=`number`-1 WHERE id=2";
        $pdo->query($sql);
    }
}

我们预置库存是十个，然后执行ab测试查看结果

mysql> insert into storage values(2,10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from storage;
+----+--------+
| id | number |
+----+--------+
|  2 |     10 |
+----+--------+
1 row in set (0.00 sec)

 
zhengniu@zhengdembp:~$     ab -c 200 -n 1000 http://www.niuzheng.net/
This is ApacheBench, Version 2.3 <$Revision: 1843412 $>
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Licensed to The Apache Software Foundation, http://www.apache.org/
Benchmarking www.niuzheng.net (be patient)
Completed 100 requests
Completed 200 requests
Completed 300 requests
Completed 400 requests
Completed 500 requests
Completed 600 requests
Completed 700 requests
Completed 800 requests
Completed 900 requests
Completed 1000 requests
Finished 1000 requests
Server Software:        nginx/1.15.8
 
mysql> select * from storage;
+----+--------+
| id | number |
+----+--------+
|  2 |     -2 |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from order1;
+----+--------+
| id | number |
+----+--------+
| 11 |     10 |
| 12 |      9 |
| 13 |      9 |
| 14 |      7 |
| 15 |      7 |
| 16 |      7 |
| 17 |      4 |
| 18 |      4 |
| 19 |      4 |
| 20 |      1 |
| 21 |      1 |
| 22 |      1 |
+----+--------+
12 rows in set (0.00 sec)

得到了订单共有12个，而库存表的库存也减到了-2，这显然不符合实际逻辑的;

下面我们来看利用数据库行锁来解决这个问题

修改代码如下

<?php
$pdo = new PDO('mysql:host=127.0.0.1;port=3306; dbname=storageorder', 'root', '123456');
$pdo->beginTransaction();//开启事务
$sql = "select `number` from storage where id=2 for UPDATE ";//利用for update 开启行锁
$res = $pdo->query($sql)->fetch();
$number = $res['number'];
if ($number > 0) {
    $sql = "insert into `order1` VALUES (null,$number)";
    $order_id = $pdo->query($sql);
    if ($order_id) {
        $sql = "update storage set `number`=`number`-1 WHERE id=2";
        if ($pdo->query($sql)) {
            $pdo->commit();//提交事务
        } else {
            $pdo->rollBack();//回滚
        }
    } else {
        $pdo->rollBack();//回滚
    }
}
 
 
mysql> select * from storage;
+----+--------+
| id | number |
+----+--------+
|  2 |      0 |
+----+--------+
1 row in set (0.00 sec)
mysql> select * from order1;
+----+--------+
| id | number |
+----+--------+
| 23 |     10 |
| 24 |      9 |
| 25 |      8 |
| 26 |      7 |
| 27 |      6 |
| 28 |      5 |
| 29 |      4 |
| 30 |      3 |
| 31 |      2 |
| 32 |      1 |
+----+--------+
10 rows in set (0.00 sec)

很明显在利用了mysql锁之后，对库存进行了有效的控制，很好的解决了第一段代码里面，因为并发引起的一些逻辑性的问题

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库