关于Mysql乐观锁

williamQian3年前 (2022-05-08)Mysql10210

> 程序开发中，经常碰到的一个问题就是如何避免超卖，例如优惠券的库存是10张，那肯定要避免最后送出的数量超过10。

例如有个发放优惠券的活动，两个用户同时向服务器发送了领取优惠券的请求，这两个请求我们就命名为请求1和请求2。服务器处理这两个请求之前，数据表如下所示，还剩一张优惠券。

| id  | name  | total（总数量）  | issued (已发放数量)
| ------------ | ------------ | ------------ | ------------ |
|  1 | 洗剪吹优惠券  | 10  | 9 |

### 1. 错误的示范

```mysql
//从数据库里取出优惠券，where条件是已发放数量小于总数量
select * from coupons where issued<total where id=1;
//如果结果为空，说明没有库存了
//如果有结果，则执行完发放操作后，已发数量+1
update coupons set issued=issued+1 where id=1
```

请求1和请求2同时读取数据库，都取到了记录，再更新，issued变成了11，导致超卖。超卖的原因就是因为，并发请求读到了相同的数据。

### 2. 使用乐观锁解决问题
> 说乐观锁之前，先说下悲观锁，悲观锁之所以悲观，是因为“悲观”地先锁住数据，等数据处理完成了，再释放锁。这样可以解决问题，但是效率不高。乐观锁不是先锁数据，而是更新的时候判断条件是否满足。示例如下：

首先，我们给coupons表增加个version字段，默认值0

| id  | name  | total（总数量）  | issued (已发放数量) | version
| ------------ | ------------ | ------------ | ------------ | ------------ |
|  1 | 洗剪吹优惠券  | 10  | 9 | 0

sql语句
```mysql
//从数据库里取出优惠券，where条件是已发放数量小于总数量
select * from coupons where issued<total where id=1;
//如果结果为空，说明没有库存了
//如果有结果，因为请求1和请求2同时读取的数据库，读到的version值都是0，更新语句如下
update coupons set issued=issued+1,version=version+1 where id=1 and version=0
```
和错误示范的区别在于，更新的时候version递增1，而且限制了verion=当前读出的记录的version值，因为update的where条件字段有索引，会触发行锁，请求1和请求2只能依次更新，比如请求1已经更新完成，这时候version=1，请求2再去更新的时候，version=0不满足条件，也就更新失败了，所以同时有多个请求时，永远只有一条记录更新成功。

>  总结：给数据表增加version(版本号)字段，初始为0，更新的时候version递增1，并且where条件限制version=当前version，这样就能购保证同一个版本的行记录，不可能同时被两个请求修改。每个请求在更新的时候，如果更新成功，说明issued的值从读出到更新成功这一刻一直没变（如果issued变了，version肯定也变了，version变了就不会更新成功）。错误示范的sql之所以不能保证并发，就是因为更新的时候可能issued已经被别的并发请求先+1了，已经和总数量相等了，但是更新语句还是执行了，最终造成了超卖。

### 3. 更宽松的乐观锁
>还是上面的例子，假如还剩两个未发放，采用version的方法，会发生什么呢？

| id  | name  | total（总数量）  | issued (已发放数量) | version
| ------------ | ------------ | ------------ | ------------ | ------------ |
|  1 | 洗剪吹优惠券  | 10  | 8 | 0

可想而知，请求1和请求2，还是只有1个请求可以更新成功，即使还剩2个。所以版本号的方案虽然能避免超卖，但是会减少系统吞吐量，明明还有库存，但是确可能会失败。所以可以用更宽松的方式处理：
```
update coupons set issued=issued+1 where id=1 and issued<total
```
还是利用了update行锁的特性，update只能排队一个个来，而且限制条件比较宽松，只要issued<total就能更新成功，就算请求1和请求2读到的行记录是一样的，只要update的时候满足条件就能成功update。相比version的方案，这种方法更简单，吞吐量更高。

扫描二维码推送至手机访问。

本文链接：http://blog.bigfoot.xin/post/14.html

标签: Mysql

分享给朋友：

返回列表

上一篇：Mysql Join 操作，on和where中的过滤条件有什么区别？

没有最新的文章了...

“关于Mysql乐观锁” 的相关文章

Mysql 事务中update操作会锁表吗？

这篇文章是关于Mysql事务内执行update，触发锁的分析。创建表table_test，表内填充如下数据： id count name 1 0 foo 2 0 bar 1. 当条件字段没有加索引，会触发表级锁一开始name字段...

Mysql Join 操作，on和where中的过滤条件有什么区别？

有如下2张表 users：用户表 id name city_id 1 张三 1 2 李四 2 3 王五 null cities：城市表 id name 1 深圳 2 广州...

日暮酒醒

大家都在搜

关于Mysql乐观锁

“关于Mysql乐观锁” 的相关文章

Mysql 事务中update操作会锁表吗？

Mysql Join 操作，on和where中的过滤条件有什么区别？

发表评论

Powered By Z-BlogPHP. Theme by TOYEAN.

日暮酒醒

大家都在搜

关于Mysql乐观锁

“关于Mysql乐观锁” 的相关文章

Mysql 事务中update操作会锁表吗？

Mysql Join 操作，on和where中的过滤条件有什么区别？

发表评论取消回复

Powered By Z-BlogPHP. Theme by TOYEAN.

发表评论