MySQL悲观锁：轻松解决商品超卖难题，提升电商网站稳定性！

Tinywan

发布于 2023-10-16 20:12:51

3510

发布于 2023-10-16 20:12:51

文章被收录于专栏：开源技术小栈

MySQL锁机制

SELECT ········FOR UPDATE 的语法开启排他锁。
select … lock in share 可加共享锁

innodb加行锁的前提是：必须是通过索引条件来检索数据，否则会切换为表锁。

因此，悲观锁在未通过索引条件检索数据时，会锁定整张表。导致其他程序不允许“加锁的查询操作”，影响吞吐。故如果在查询居多的情况下，推荐使用乐观锁。

共享锁与排他锁

说明：数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

共享锁

对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源(也可以再继续加共享锁，即共享锁可多个共存)，但无法修改。要想修改就必须等所有共享锁都释放完之后。

语法为：

select * from table lock in share mode

排他锁

对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。

语法为：

select * from table for update

使用悲观锁(其实说白了也就是排他锁)（推荐）


|-- 程序A在查询库存数时使用排他锁 (select * from table where id=10 for update)

|-- 然后进行后续的操作，包括更新库存数，最后提交事务。

|-- 程序B在查询库存数时，如果A还未释放排他锁，它将等待……

|-- 程序C同B……

注意： 排他锁的真实含义不是排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，而是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。

使用乐观锁(靠表设计和代码来实现)

|-- 一般是在该商品表添加version版本字段或者timestamp时间戳字段

|-- 程序A查询后，执行更新变成了：

update table set num=num-1 where id=10 and version=23

悲观锁解决商品超卖问题的实现

使用场景举例

以MySQL InnoDB为例

商品good表，假设商品的id为1，购买数量为1，status为1表示上架中，2表示下架。现在用户购买此商品，在不是高并发的情况下处理逻辑是：

查找此商品的信息；（返回 '商品不存在'）
检查商品库存是否大于购买数量；（'库存不足'）
修改商品库存和销量；

[danger] 上面这种场景在高并发访问的情况下很可能会出现问题。如果商品库存是100个，高并发的情况下可能会有1000个同时访问，在到达第2步的时候，都会检测通过。这样会出现商品库存是-900个的情况

数据表

商品表 `good`

CREATE TABLE `good` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名称',
  `status` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '是否上架（1：上架中，2：表示下架）',
  `total` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '库存',
  `sell` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '销售数',
  `price` decimal(10,2) unsigned NOT NULL COMMENT '价格',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO `good`(`id`, `name`, `status`, `total`, `sell`, `price`) VALUES (1, '商品', 1, 100, 0, 15.00);

订单表：order

CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户id',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `goods_id` int(11) NOT NULL DEFAULT '0',
  `order_no` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

使用悲观锁处理

实现逻辑： 当我们在查询出good信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为good被锁定了，就不会出现有第三者来对其进行修改了。
注：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。
这里ThinkORM中使用事务，手动进行提交回滚。

并发测试

使用Apache JMeter工具进行压力并发测试

JMeter是一个开源的Java应用程序，由Apache软件基金会开发和维护，可用于性能测试、压力测试、接口测试等。

测试代码

public function noLock()
{
    $num = 1;
    $goods_id = 1;
    Db::startTrans();
    try {
        $where = [];
        $where['id'] = $goods_id;
        $where['status'] = 1;
        // $goodInfo = Db::table('good')->lock(true)->where($where)->find(); // 加悲观锁

        $goodInfo = Db::table('good')->where($where)->find();  // 不加悲观锁
        if (empty($goodInfo)) {
            return '商品不存在';
        }

        if ($goodInfo['total'] < $num) {
            return '库存不足';
        }

        $orderUpdate['total'] = $goodInfo['total'] - $num;
        $orderUpdate['sell'] = $goodInfo['sell'] + $num;
        Db::table('good')->where(['id' => $goods_id])->update($orderUpdate);

        $orderData = [];
        $orderData['uid'] = rand(10000, 99999);
        $orderData['status'] = 1;
        $orderData['create_time'] = date('Y-m-d H:i:s');
        $orderData['goods_id'] = $goods_id;
        $orderData['order_no'] = date('YmdHis') . rand(1000, 10000);
        Db::table('order')->insert($orderData);
        Db::commit();
    } catch (\Exception $exception) {
        Db::rollback();
        return '异常回滚' . $exception->getMessage();
    }
    return '恭喜！抢购成功！';
}

1、创建测试

（1）创建线程测试组模拟1s内1000个用户同时访问

（2）创建http请求

（3）添加察看结果树

2、测试开始

（1）100个商品不加锁的结果

100个商品库存，生成订单1000个，超卖944个商品，还剩下44个商品

（2）100个商品，加锁结果

可以看到，通过加锁可以解决该问题哈

其他问题

General error: 1205 Lock wait timeout exceeded; try restarting transaction

从报错看是因为事务锁超时了，那么超时时间设置问题？

事务锁超时时间 innodb_lock_wait_timeout

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.06 sec)

超时时间是50秒

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 添加一下，设置为120秒超时时间
innodb_lock_wait_timeout=120

重启MySQL

查询进程查询数据库线程情况，看是否有执行很慢的SQL

mysql> show processlist;
+----+------+---------------------+-----------------------+---------+------+------------+-----------------------------------------------------------------------------+
| Id | User | Host                | db                    | Command | Time | State      | Info                                                                        |
+----+------+---------------------+-----------------------+---------+------+------------+-----------------------------------------------------------------------------+
| 27 | www  | 47.98.155.92:60996  | webman.tinywan.com    | Sleep   |   17 |            | NULL                                                                        |
| 28 | www  | 39.170.63.237:60955 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 29 | www  | 39.170.63.237:60952 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 30 | www  | 39.170.63.237:60949 | cms.webman.tinywan.cn | Execute |   87 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 31 | www  | 39.170.63.237:60951 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 32 | www  | 39.170.63.237:60956 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 33 | www  | 39.170.63.237:60950 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 34 | www  | 39.170.63.237:60948 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 35 | www  | 39.170.63.237:60954 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 36 | www  | 39.170.63.237:60953 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 37 | www  | 39.170.63.237:60957 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 38 | www  | 39.170.63.237:60958 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 39 | www  | 39.170.63.237:60959 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 40 | www  | 39.170.63.237:60961 | cms.webman.tinywan.cn | Execute |   87 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 41 | www  | 39.170.63.237:60960 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 42 | www  | 39.170.63.237:60962 | cms.webman.tinywan.cn | Execute |   87 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 43 | www  | 39.170.63.237:60963 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 44 | www  | 39.170.63.237:60964 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 45 | www  | 39.170.63.237:60967 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 46 | www  | 39.170.63.237:60966 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 47 | www  | 39.170.63.237:60965 | cms.webman.tinywan.cn | Sleep   |    7 |            | NULL                                                                        |
| 48 | www  | 39.170.63.237:60968 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 49 | www  | 39.170.63.237:60969 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 50 | www  | 39.170.63.237:60970 | cms.webman.tinywan.cn | Sleep   |    7 |            | NULL                                                                        |
| 51 | www  | 39.170.92.205:60971 | cms.webman.tinywan.cn | Execute |   86 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 52 | www  | 39.170.92.205:60973 | cms.webman.tinywan.cn | Execute |   87 | statistics | SELECT * FROM `good` WHERE  `id` = ?  AND `status` = ? LIMIT 1   FOR UPDATE |
| 53 | www  | 39.170.63.237:60999 | cms.webman.tinywan.cn | Query   |    0 | starting   | show processlist                                                            |
| 54 | www  | 39.170.92.205:61083 | cms.webman.tinywan.cn | Sleep   |   84 |            | NULL                                                                        |
| 55 | www  | 39.170.92.205:61084 | cms.webman.tinywan.cn | Sleep   |  130 |            | NULL                                                                        |
+----+------+---------------------+-----------------------+---------+------+------------+-----------------------------------------------------------------------------+
29 rows in set (0.11 sec)

在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务

查询到相对应的进程

再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉

kill   id

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-10-11，如有侵权请联系 cloudcommunity@tencent.com 删除

网站

本文分享自开源技术小栈微信公众号，前往查看

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

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度