文档章节

MySQL中SELECT+UPDATE处理并发更新问题解决方案分享

fzxu_05
 fzxu_05
发布于 2015/12/30 15:45
字数 1354
阅读 259
收藏 13

问题背景:

假设MySQL数据库有一张会员表vip_member(InnoDB表),结构如下:


当一个会员想续买会员(只能续买1个月、3个月或6个月)时,必须满足以下业务要求:

•如果end_at早于当前时间,则设置start_at为当前时间,end_at为当前时间加上续买的月数

•如果end_at等于或晚于当前时间,则设置end_at=end_at+续买的月数

•续买后active_status必须为1(即被激活)

问题分析:

对于上面这种情况,我们一般会先SELECT查出这条记录,然后根据查出记录的end_at再UPDATE start_at和end_at,伪代码如下(为uid是1001的会员续1个月):

代码如下:

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员
if vipMember.end_at < NOW():
  
 UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1
 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001
else:
   UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001

假如同时有两个线程执行上面的代码,很显然存在“数据覆盖”问题(即一个是续1个月,一个续2个月,但最终可能只续了2个月,而不是加起来的3个月)。

解决方案:

A、我想到的第一种方案是把SELECT和UPDATE合成一条SQL,如下:

代码如下:

UPDATE vip_member 
SET 
   start_at = CASE
              WHEN end_at < NOW() 
                 THEN NOW()
              ELSE start_at
              END,
   end_at = CASE
            WHEN end_at < NOW()
               THEN DATE_ADD(NOW(), INTERVAL #duration:INTEGER# MONTH)
            ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH)
            END,
   active_status=1,
   updated_at=NOW()
WHERE uid=#uid:BIGINT#
LIMIT 1;

    So easy!

B、第二种方案:事务,即用一个事务来包裹上面的SELECT+UPDATE操作。

    那么是否包上事务就万事大吉了呢?

    显然不是。因为如果同时有两个事务都分别SELECT到相同的vip_member记录,那么一样的会发生数据覆盖问题。那有什么办法可以解决呢?难道要设置事务隔离级别为SERIALIZABLE,考虑到性能不现实。

    我们知道InnoDB支持行锁。查看MySQL官方文档(innodb locking reads)了解到InnoDB在读取行数据时可以加两种锁:读共享锁和写独占锁。

    读共享锁是通过下面这样的SQL获得的:

代码如下:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

    如果事务A获得了先获得了读共享锁,那么事务B之后仍然可以读取加了读共享锁的行数据,但必须等事务A commit或者roll back之后才可以更新或者删除加了读共享锁的行数据。代码如下:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

   如果事务A先获得了某行的写共享锁,那么事务B就必须等待事务A commit或者roll back之后才可以访问行数据。

   显然要解决会员状态更新问题,不能加读共享锁,只能加写共享锁,即将前面的SQL改写成如下:

 代码如下:

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 FOR UPDATE # 查uid为1001的会员
if vipMember.end_at < NOW():
  
 UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1
 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001
else:
   UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001

    另外这里特别提醒下:UPDATE/DELETE SQL尽量带上WHERE条件并在WHERE条件中设定索引过滤条件,否则会锁表,性能可想而知有多差了。

C、第三种方案:乐观锁,类CAS机制

    第二种加锁方案是一种悲观锁机制。而且SELECT...FOR UPDATE方式也不太常用,联想到CAS实现的乐观锁机制,于是我想到了第三种解决方案:乐观锁。

    具体来说也挺简单,首先SELECT SQL不作任何修改,然后在UPDATE SQL的WHERE条件中加上SELECT出来的vip_memer的end_at条件。如下:

代码如下:

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员
cur_end_at = vipMember.end_at
if vipMember.end_at < NOW():
  
 UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1
 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND 
end_at=cur_end_at
else:
   UPDATE vip_member SET 
end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, 
updated_at=NOW() WHERE uid=1001 AND end_at=cur_end_at

    这样可以根据UPDATE返回值来判断是否更新成功,如果返回值是0则表明存在并发更新,那么只需要重试一下就好了。

方案比较:

三种方案各自优劣也许众说纷纭,只说说我自己的看法:

•第一种方案利用一条比较复杂的SQL解决问题,不利于维护,因为把具体业务糅在SQL里了,以后修改业务时不但需要读懂这条SQL,还很有可能会修改成更复杂的SQL

•第二种方案写独占锁,可以解决问题,但不常用

•第三种方案应该是比较中庸的解决方案,并且甚至可以不加事务,也是我个人推荐的方案


此外,乐观锁和悲观锁的选择一般是这样的(参考了文末第二篇资料):

•如果对读的响应度要求非常高,比如证券交易系统,那么适合用乐观锁,因为悲观锁会阻塞读

•如果读远多于写,那么也适合用乐观锁,因为用悲观锁会导致大量读被少量的写阻塞

•如果写操作频繁并且冲突比例很高,那么适合用悲观写独占锁


© 著作权归作者所有

共有 人打赏支持
fzxu_05
粉丝 43
博文 165
码字总数 84201
作品 0
朝阳
程序员
MySQL中SELECT+UPDATE并发更新问题

问题背景: 假设MySQL数据库有一张会员表vipmember(InnoDB表),结构如下: 当一个会员想续买会员(只能续买1个月、3个月或6个月)时,必须满足以下业务要求: 如果endat早于当前时间,则设...

优雅先生
2014/05/11
0
17
Mysql 先SELECT 后UPDATE 问题

最近做一个统计;需要把一个字段(存放数据是json)里某一个数字加1然后在修改该字段;当时就那么一写最后发现该数据和明细对不上;其实这个应该是并发引起的,先select 在update 这样写其实会...

汤汤圆圆
2016/06/29
55
0
如何解决秒杀的性能问题和超卖的讨论

如何解决秒杀的性能问题和超卖的讨论 最近业务试水电商,接了一个秒杀的活。之前经常看到淘宝的同行们讨论秒杀,讨论电商,这次终于轮到我们自己理论结合实际一次了。 ps:进入正文前先说一点...

fdhay
2016/09/08
71
0
如何解决秒杀的性能问题和超卖的讨论

最近业务试水电商,接了一个秒杀的活。之前经常看到淘宝的同行们讨论秒杀,讨论电商,这次终于轮到我们自己理论结合实际一次了。   ps:进入正文前先说一点个人感受,之前看淘宝的ppt感觉都...

真爱2015
2016/08/15
73
1
关于秒杀的系统架构优化思路

解决方案1: 将存库从MySQL前移到Redis中,所有的写操作放到内存中,由于Redis中不存在锁故不会出现互相等待,并且由于Redis的写性能和读性能都远高于MySQL,这就解决了高并发下的性能问题。...

萧小蚁
2016/12/20
50
0

没有更多内容

加载失败,请刷新页面

加载更多

学习设计模式——命令模式

参考博客 1. 认识命令模式 1. 定义:将一个请求封装成为一个对象,从而可以用不同的请求对客户进行参数化,对请求排队或记录请求日志,并支持可撤销操作。 2. 组织结构: Commond:定义命令的...

江左煤郎
14分钟前
0
0
字典树收集(非线程安全,后续做线程安全改进)

将500W个单词放进一个数据结构进行存储,然后进行快速比对,判断一个单词是不是这个500W单词之中的;来了一个单词前缀,给出500w个单词中有多少个单词是该前缀. 1、这个需求首先需要设计好数据结...

算法之名
昨天
6
0
GRASP设计模式

此文参考了这篇博客,建议读者阅读原文。 面向对象(Object-Oriented,OO)是当下软件开发的主流方法。在OO分析与设计中,我们首先从问题领域中抽象出领域模型,在领域模型中以适当的粒度归纳...

克虏伯
昨天
0
0
Coding and Paper Letter(四十)

资源整理。 1 Coding: 1.Tomislav Hengl撰写的非官方作者指南:Michael Gould•Wouter Gerritsma。 UnofficialGuide4Authors 2.R语言包rwrfhydro,社区贡献的工具箱,用于管理,分析和可视化...

胖胖雕
昨天
0
0
JAVA 内存回收

参考:https://www.cnblogs.com/leesf456/p/5218594.html 1,JMV 中哪些可以作为 GC Root? 1. 虚拟机栈(栈帧中的局部变量区,也叫做局部变量表)中引用的对象。 2. 方法区中的类静态属性引...

Carlyle_Lee
昨天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部