mysql商品库存扣减问题总结

Wesley13
• 阅读 801

文章讨论内容

秒杀类的问题一直都是web领域比较热点的问题,一个超高并发的网站需要考虑从产品、前端优化、站点部署及后端服务等等所有环节进行考虑。mysql所能抗住的写压力是一定的,高并发的web站点,你需要在数据持久化之前控制好压力,而不是把所有的请求都落到数据服务这一层。今天我不在这篇文章里讨论秒杀整体设计的问题(我也没这个资格),我们讨论的是如何在流速已经得到控制的情况下,如何利用mysql更安全、高效的解决这个问题。

从网上可以看到各种各样的实现方案,现在针对这些方案及其优缺点和理解误区进行讨论。

常见写法安全性及效率分析

假设我们的商品表的schema是下面这样的:

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

设置为字段无符号解决 

num = select available from goods where id = xx ;
if(num > 0){
   affectRows = udpate goods set available = available - 1 where id = xx ;
   if(affectRows == 1){
       return ok ;
   }else{
       return fatal ;
   }
}

这种做法大家的想法是我们将库存字段设置成无符号类型,这样当库存字段在sql执行时候被置为负数的时候mysql就会报错,那么affectRow就会是0或者可以捕获到这个异常,从而实现并发下的数据安全。

解法释义

实际上这段代码是危险的,因为在不同版本的mysql和配置下,这段代码的表现完全不同。具体的情况会出现3种不同的结果:

  • 1.代码正常运行,执行update的时候报错
  • 2.代码最终执行结果出现了 -1
  • 3.最终update操作之后,available变成了一个很大的数目

为什么会出现这三种情况呢?

我想在学习开始学习计算机的时候都讲过计算机的加减法计算方法。

思考一下,无符号2 减去 无符号3 在计算机中的运算是什么样的?
2 - 3 = 2 + (-3)
假设我们的计算机是4位的,2的补码表示:0010,-3的补码表示为1011
那么加和的结果是
0010
1011 + 
------
1111 =
1111解释为有符号数是多少呢? -1
1111解释为无符号数是多少呢? 15

所以呢? 

如果mysql不做任何处理的话,你的无符号数减法的结果不会报错,最终你算出来的库存还是一个非常大的值(可怕)。
但是幸运的是mysql 后来的版本帮你做了这件事情(具体哪个版本我也不清楚),所以如果是mysql做了无符号检测的话,如果减出的结果是负值,会报错,这是大多数人期待的结果。
-1这种情况是需要你设置一下sqlmode的,这也是会出现的情况。

解法总结

  • 这个办法很多人用的时候没问题,那只能说明可能是机缘巧合,但是对于业务代码而言,不能靠碰运气,需要消除不确定性、缩小迁移成本。
  • 如果你想采用这种办法,辛苦你把你们msyql相应的版本及配置搞清楚,确定无符号在你所在的版本会出现什么结果。

select for update

解法释义

读取时候就开始加排他锁也是网上常见的办法之一,具体实现如下:

begin tran ;num = select avaliable from goods where id = xxx for update;if (num >= 0){   affectNum = udpate goods set available = available - 1 where id = xx ; commit ; return affectNum ;}else{ rollback ;}

该解法在用户读取的时候对相应的数据加排他锁,保证自己在更新的时候该行的数据不会被别的进程更改.所有写请求及排他锁加锁都会被阻塞。 

想想这样的情况,A进程执行过程中,出现死机的情况导致commit/rollback请求没有被发送到mysqlserver,那么所有请求都会锁等待。

解法总结

  • 低流量可以采用这种办法来保证数据的安全性

  • 性能低下,平均需要发送4次mysql请求,同时会造成所有同类请求锁等待。

    常见问题

  • select for udpate 需要在显式的指定在事务代码块执行,不然不会起作用。很多网友都理所当然的人为select for update直接就可以加排他锁

  • 排他锁的释放是在rollback/commit 动作完成才会释放,不是在update操作之后。mysql innodb执行两段锁协议,加锁阶段只加锁,解锁阶段只解锁。

采用事务,先查后写再查,确保没问题

解法释义

这时候的available设置为有符号类型,解决方案一的问题

begin tran ;
num = select available from goods where id = xx ;
if(num > 0){
   //实际需要关心这里的返回值,这里不考虑
   udpate goods set available = available - 1 where id = xx ;
   num_afterupdate = select available from goods where id = xx ;
   if(num_afterupdate < 0 ){
       rollback ;
   }else{
       commit ;
   }
}

这种解法区分于第一种的办法在于,加了事务、available类型更改、采用了更新后确认的形式,尝试解决问题。 

我们都知道数据库的事务隔离级别有4种:
RU,RC,RR,Serializable。
我们常见的innodb中RR模式是可以保证可重复读,意思是在同一个事务内部,多次读取的结果是一致的。那么最后一次的读取对于RR隔离级别实际上是无效的。
RC模式下,这个代码是可用的,每次请求可以确保自己的进程不会超发。

解法总结

  • RR、RC模式下结果不一致.RR下不可保证安全、RC可以。
  • 性能不高,一次业务请求到mysql的转化为 1 : 5。
  • 这种解法就像老奶奶锁门,总是不放心自己到底锁了没有,走了几步再回来看看,实际上有些时候是徒劳。

update语句增加available查询条件

解法释义

udpate goods set available = available - 1 where id = xx and available - 1 >= 0 ;

大家有的另一个误区是单条语句不是事务,实际上单条sql也是一个事务。
问题的关键就集中在怎么证明这句的安全性的。
我们都知道update操作对于id为主键索引的情况下,是会对数据加行锁。
其实update操作在mysql内部也是一个先查后改的过程,这个过程如果是原子的,那么可以保证update语句是串行的,那我们就来看一下update语句在mysql内部的执行过程。
mysql商品库存扣减问题总结

那么对于上面这个语句,一样遵循两段锁协议。
update执行的过程,会去查询满足条件的行并加锁,这个加锁是innodb做的,那么就可以保证别的事务必须等到该事务执行完了之后才能获得锁,此时拿到最新数据。

解法总结

  • 语句安全、效率最优(我的认知里)

采用设置库存而不是扣减库存

这几天我把类似的文章几乎翻了一遍,唯一看到批评我的上一条做法的是我的那个做法是不具备幂等性的。

  • 所谓幂等性就是,同一个用户对同一连接的访问不会产生副作用。比如上一条的方案,如果记录用户的操作和扣减库存不是原子操作的话,就有可能出现的问题是,库存扣减成功了,但是用户记录失败了,那么用户重复请求,就会出现多次减库存的问题。

那么他们的解法是这样的,采用设置而不是扣减,代码如下:

num_old = select available from goods where id = xx and available >= 1 ;num_new = num_old - 1 ;update goods set num=num_new where id=xx and num=num_old ;

这段代码也是安全的,采用的是乐观所的理念来完成的操作。

总结

  • 上面的做法,最后两个是相对安全的,但是你的库存字段还是要设置为无符号,关于是否幂等,要看结合请求看,不是单个扣减块代码。
  • 较真是一个学习的过程,只有较真才能把这些概念搞清楚。如果你需要完全弄懂这些内容,可能你需要对mysql锁、事务、mvcc这些概念都做一下预习。
  • 感谢工作过程中小伙伴们的努力,让我们把问题追查的更清楚。

引用

当然这个过程中我也是看了一些比较经典的mysql书籍,也推荐你看一下:

1.《高性能MySQL(第3版)》       https://u.jd.com/DyajuZ

2.《MySQL技术内幕:InnoDB存储引擎(第2版)》  https://u.jd.com/BsVLNm

mysql商品库存扣减问题总结 mysql商品库存扣减问题总结

有什么问题都可以扫码一起交流,这篇文章很早之前在自己搭建的一个博客地址上写的,因为coidng.me 的域名不再使用了,所以无法维护。

mysql商品库存扣减问题总结

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
2星期前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
6个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这