商品是否会超发？（UPDATE ... remained = remained - 1 WHERE ...）

迈不过友情╰ 2023-10-01 20:23 4阅读 0赞

原文网址：[商品是否会超发？（UPDATE ... remained = remained - 1 WHERE ...）\_IT利刃出鞘的博客-CSDN博客][UPDATE ... remained _ remained - 1 WHERE ..._IT_-CSDN]

## 简介 ##

**说明**

商品的超发是下订单业务中需要考虑的情景。那么，使用下边这样的语句是否会超发呢？

UPDATE t_storage SET remained = remained -1, used = used + 1 WHERE product_id = 1 AND remained > 0;

*  remained：剩余的库存
 *  used：已用的库存

结论是：不会导致超发。（事务隔离级别是默认的：可重复读）。

带来的影响就是：我们无需在下单接口中加分布式锁。

**验证方法**

本文将用实例来进行验证。

2个客户端开启事务，都去更新库存，看最后的库存真正扣减了多少。如果最后扣减了1件，则说明超发了；如果最后扣减了2件，则说明没有超发。

## 建库建表 ##

DROP DATABASE IF EXISTS demo;
    CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
    USE demo;
    
    DROP TABLE IF EXISTS `t_storage`;
    
    CREATE TABLE `t_storage` (
      `id`          bigint(11) unsigned NOT NULL AUTO_INCREMENT,
      `product_id`  bigint(11) unsigned DEFAULT NULL COMMENT '产品id',
      `total`       int(11) unsigned DEFAULT NULL COMMENT '总库存',
      `used`        int(11) unsigned DEFAULT NULL COMMENT '已用库存',
      `remained`    int(11) unsigned DEFAULT NULL COMMENT '剩余库存',
      `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`),
      UNIQUE KEY (`product_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '库存';
    
    INSERT INTO `demo`.`t_storage` (`id`, `product_id`, `total`, `used`, `remained`) 
        VALUES ('1', '1', '100', '0', '100');

结果如下

![7fe438edcd2342e9865ec08a2c8c6dca.png][]

## 测试 ##

使用两个MySQL事务来测试。

<table> 
 <tbody> 
 <tr> 
 <td style="width:515px;">会话A</td> 
 <td style="width:528px;">会话B</td> 
 </tr> 
 <tr> 
 <td style="width:515px;"> mysql -uroot -p222333 use demo </td> 
 <td style="width:528px;"></td> 
 </tr> 
 <tr> 
 <td style="width:515px;"></td> 
 <td style="width:528px;"> mysql -uroot -p222333 use demo </td> 
 </tr> 
 <tr> 
 <td style="width:515px;">mysql&gt; START TRANSACTION; Query OK, 0 rows affected (0.00 sec)</td> 
 <td style="width:528px;"></td> 
 </tr> 
 <tr> 
 <td style="width:515px;"></td> 
 <td style="width:528px;">mysql&gt; START TRANSACTION; Query OK, 0 rows affected (0.00 sec)</td> 
 </tr> 
 <tr> 
 <td style="width:515px;">mysql&gt; UPDATE t_storage SET remained = remained -1, used = used + 1 WHERE product_id = 1 AND remained &gt; 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 &nbsp;Changed: 1 &nbsp;Warnings: 0</td> 
 <td style="width:528px;"></td> 
 </tr> 
 <tr> 
 <td style="width:515px;"></td> 
 <td style="width:528px;"> mysql&gt; UPDATE t_storage SET remained = remained -1, used = used + 1 WHERE product_id = 1 AND remained &gt; 0; 结果：被阻塞 </td> 
 </tr> 
 <tr> 
 <td style="width:515px;">COMMIT;</td> 
 <td style="width:528px;"> 会话A在COMMIT之后，会话B打印： Query OK, 1 row affected (3.70 sec) Rows matched: 1 &nbsp;Changed: 1 &nbsp;Warnings: 0 </td> 
 </tr> 
 <tr> 
 <td style="width:515px;">mysql&gt; SELECT * FROM t_storage; +----+------------+-------+------+----------+---------------------+---------------------+ | id | product_id | total | used | remained | create_time &nbsp; &nbsp; &nbsp; &nbsp; | update_time &nbsp; &nbsp; &nbsp; &nbsp; | +----+------------+-------+------+----------+---------------------+---------------------+ | &nbsp;1 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 | &nbsp; 100 | &nbsp; &nbsp;1 | &nbsp; &nbsp; &nbsp; 99 | 2022-02-27 11:04:22 | 2022-02-27 11:17:18 | +----+------------+-------+------+----------+---------------------+---------------------+ 1 row in set (0.00 sec)</td> 
 <td style="width:528px;"></td> 
 </tr> 
 <tr> 
 <td style="width:515px;"></td> 
 <td style="width:528px;">mysql&gt; SELECT * FROM t_storage; +----+------------+-------+------+----------+---------------------+---------------------+ | id | product_id | total | used | remained | create_time &nbsp; &nbsp; &nbsp; &nbsp; | update_time &nbsp; &nbsp; &nbsp; &nbsp; | +----+------------+-------+------+----------+---------------------+---------------------+ | &nbsp;1 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 | &nbsp; 100 | &nbsp; &nbsp;2 | &nbsp; &nbsp; &nbsp; 98 | 2022-02-27 11:04:22 | 2022-02-27 11:17:22 | +----+------------+-------+------+----------+---------------------+---------------------+ 1 row in set (0.00 sec)</td> 
 </tr> 
 <tr> 
 <td style="width:515px;"></td> 
 <td style="width:528px;">COMMIT;</td> 
 </tr> 
 <tr> 
 <td style="width:515px;">mysql&gt; SELECT * FROM t_storage; +----+------------+-------+------+----------+---------------------+---------------------+ | id | product_id | total | used | remained | create_time &nbsp; &nbsp; &nbsp; &nbsp; | update_time &nbsp; &nbsp; &nbsp; &nbsp; | +----+------------+-------+------+----------+---------------------+---------------------+ | &nbsp;1 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 | &nbsp; 100 | &nbsp; &nbsp;2 | &nbsp; &nbsp; &nbsp; 98 | 2022-02-27 11:04:22 | 2022-02-27 11:17:22 | +----+------------+-------+------+----------+---------------------+---------------------+ 1 row in set (0.00 sec)</td> 
 <td style="width:528px;"></td> 
 </tr> 
 <tr> 
 <td style="width:515px;"></td> 
 <td style="width:528px;">mysql&gt; SELECT * FROM t_storage; +----+------------+-------+------+----------+---------------------+---------------------+ | id | product_id | total | used | remained | create_time &nbsp; &nbsp; &nbsp; &nbsp; | update_time &nbsp; &nbsp; &nbsp; &nbsp; | +----+------------+-------+------+----------+---------------------+---------------------+ | &nbsp;1 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 | &nbsp; 100 | &nbsp; &nbsp;2 | &nbsp; &nbsp; &nbsp; 98 | 2022-02-27 11:04:22 | 2022-02-27 11:17:22 | +----+------------+-------+------+----------+---------------------+---------------------+ 1 row in set (0.00 sec)</td> 
 </tr> 
 </tbody> 
</table>

可以看到，最终是扣减了2个库存，没有超发。

**原因**

在UPDATE时，会对product\_id = 1这一行记录加锁，其他写操作直接阻塞，不会出现并行的问题。

[UPDATE ... remained _ remained - 1 WHERE ..._IT_-CSDN]: https://knife.blog.csdn.net/article/details/123160737
[7fe438edcd2342e9865ec08a2c8c6dca.png]: https://img-blog.csdnimg.cn/7fe438edcd2342e9865ec08a2c8c6dca.png