MySQL 扣减库存的笔记

Posted on Edited on In

一个思考

电商网站有一个场景是：用户购买一个商品，库存会做扣减。如果用 MySQL 做商品存储，会遇到什么坑，该怎么避免。

简化的商品表如下。

1
2
3
4
5
6
CREATE TABLE `goods` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(256) NOT NULL DEFAULT '' COMMENT '商品名称',
  `available` int(11)  NOT NULL DEFAULT '0' COMMENT '库存剩余量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

在阅读网上的解决方法之前，脑海会迸发一个解决方法是：

1
UPDATE goods SET available = available - 1 WHERE id = xxx

如果是这么简单的话，那就没有必要讨论了。实际上在并发请求的业务场景下，有可能出现 available 变量变成负数。

虽然我们可以将 available 变量设计成 int unsigned 字段来避免，但是在不同的 MySQL 版本下，会出现 available 溢出成为一个非常大的数。

解决方法

  • 悲观锁的方案：SELECT FOR UPDATE

当我们查询 goods 信息后就把当前的数据锁定，直到我们修改完毕后再解锁。不过 FOR UPDATE 仅适用于 InnoDB，且必须在事务区块中才能生效。

1
2
3
4
5
6
7
8
9
set autocommit = 0; // 设置MySQL为非autocommit模式：
begin trans;// 开始事务
select avaliable from goods where id = xxx for update; //1.查询出商品信息
if (avaliable >= 0) {
   affectNum = udpate goods set available = available - 1 where id = xx ;
   commit ; // 4.提交事务
} else {
   rollback ;
}

弊端：
悲观锁是排他锁，会阻塞其他写请求，如果上述代码有异常，导致 Commit/Rollback 没有执行，就会造成所有请求都在等待锁。

  • 乐观锁的方案

乐观锁假设数据一般情况下不会冲突，在数据提交更新的时候，才会对检测数据的冲突与，如果发现冲突了，则让返回用户错误的信息，让用户决定。
通常做法是记录加一个 Version 字段。读取记录时，连同 Version 一同读出。数据每次更新时候，Version + 1， 更新时判断记录的当前版本与之前取出来的 Version 值比对，
如果当前版本号与第一次取出来的 Version 值相等，则予以更新，否则认为是过期数据。

1
2
3
4
select avaiable ,version from goods where id=xxx limit 1; // 查询version
if (available > 0) {
	update goods set avaiable=avaiable-1 where id =xx and version= <刚查出来的 Version>;
}

弊端： 这样虽然保证安全，但是需要执行2次 SQL

  • Update 时增加 available 查询条件
1
udpate goods set available = available - 1 where id = xx and available - 1 >= 0 ;

Innodb 的 Update 语句，对于 id 是主键索引的情况下会执行行锁。该语句在 MySQL中是先读后更新，串行且原子的。单条语句，实际上也是一个事务。这个方法效率更快，且安全。