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

发布于

MySQL锁机制

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

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

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

共享锁与排他锁

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

共享锁

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

语法为：

代码语言：javascript
复制
select * from table lock in share mode

排他锁

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

语法为：

代码语言：javascript
复制
select * from table for update

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

代码语言：javascript
复制

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

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


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


|-- 程序C同B……

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

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

代码语言：javascript
复制
|-- 一般是在该商品表添加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

代码语言：javascript
复制
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

代码语言：javascript
复制
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软件基金会开发和维护，可用于性能测试、压力测试、接口测试等。

测试代码

代码语言：javascript
复制
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个商品，加锁结果

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

其他问题

代码语言：javascript
复制
General error: 1205 Lock wait timeout exceeded; try restarting transaction

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

事务锁超时时间 innodb_lock_wait_timeout

代码语言：javascript
复制
mysql> show variables like 'innodb_lock_wait_timeout';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 50    |

+--------------------------+-------+

1 row in set (0.06 sec)

超时时间是50秒

代码语言：javascript
复制
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf


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


innodb_lock_wait_timeout=120

重启MySQL

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

代码语言：javascript
复制
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掉

代码语言：javascript
复制
kill   id

#网站#电商#mysql#事务#数据