文档章节

MySQL中SELECT+UPDATE并发更新问题

优雅先生
 优雅先生
发布于 2014/05/11 23:27
字数 1434
阅读 5.2K
收藏 89

3 月,跳不动了?>>>

问题背景:

假设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 1 MONTH)
            ELSE DATE_ADD(end_at, INTERVAL 1 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...FOR UPDATE获得:

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

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

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

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

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

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

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


参考资料:

innodb locking reads

MVCC在分布式系统中的应用

© 著作权归作者所有

优雅先生
粉丝 368
博文 36
码字总数 46290
作品 0
浦东
技术主管
私信 提问
加载中

评论(17)

优雅先生
优雅先生 博主

引用来自“stefanzhlg”的评论

总结的棒,原来兄弟也在浦东。13
嗯,嘿嘿,缘分!
stefanzhlg
stefanzhlg
总结的棒,原来兄弟也在浦东。13
优雅先生
优雅先生 博主

引用来自“范思哲思考者”的评论

我是小白,问楼主一个问题,数据库对业务逻辑的处理不是越少越好嘛?这样写sql对数据库的损耗和代码处理哪一个更好一些呢?
对的,一般不推荐在SQL中处理太多业务逻辑,比如时间换算、格式转换等。但也有例外,比如一些批处理任务用到的存储过程SQL可能包含较多的业务逻辑。但一般的高并发OLTP类系统都是强调SQL越短小精悍越好的,可以把高并发的场景想象成交通高峰期的高速公路,而大SQL就是卡车,高速公路不允许卡车上路是为了减少交通拥堵的概率。
范思哲思考者
我是小白,问楼主一个问题,数据库对业务逻辑的处理不是越少越好嘛?这样写sql对数据库的损耗和代码处理哪一个更好一些呢?
优雅先生
优雅先生 博主

引用来自“泡不烂的凉粉”的评论

俺不想打击楼主,
直接更新数据,用函数找出end_at和当前时间比较的差值,取大数字
大概样子 end_at = date_add(IF(now()>end,now(),end),interval x day);
简单明了。

引用来自“ManInDream”的评论

用一条比较复杂的SQL解决的最大弊端在于将业务杂糅在了SQL中,要知道在一个典型的后端系统中,业务层下面有DAL数据访问层,而DAL才跟具体的SQL打交道。而这里相当于业务直接穿透DAL被放到了DAL下面的SQL。不利于维护,而且大SQL也是不推荐的。

引用来自“泡不烂的凉粉”的评论

如同我说过的, 既然选定了数据库,就别什么跨平台了。 我的观点就是直接用 update 更新, 至于 CASE 子句, 感觉没有直接更新直观。 实现目的并非一种, 换作是我, 我会选用直接更新的方式。
见仁见智吧!
没手机的不配发言
没手机的不配发言

引用来自“泡不烂的凉粉”的评论

俺不想打击楼主,
直接更新数据,用函数找出end_at和当前时间比较的差值,取大数字
大概样子 end_at = date_add(IF(now()>end,now(),end),interval x day);
简单明了。

引用来自“ManInDream”的评论

用一条比较复杂的SQL解决的最大弊端在于将业务杂糅在了SQL中,要知道在一个典型的后端系统中,业务层下面有DAL数据访问层,而DAL才跟具体的SQL打交道。而这里相当于业务直接穿透DAL被放到了DAL下面的SQL。不利于维护,而且大SQL也是不推荐的。
如同我说过的, 既然选定了数据库,就别什么跨平台了。 我的观点就是直接用 update 更新, 至于 CASE 子句, 感觉没有直接更新直观。 实现目的并非一种, 换作是我, 我会选用直接更新的方式。
优雅先生
优雅先生 博主

引用来自“泡不烂的凉粉”的评论

俺不想打击楼主,
直接更新数据,用函数找出end_at和当前时间比较的差值,取大数字
大概样子 end_at = date_add(IF(now()>end,now(),end),interval x day);
简单明了。
用一条比较复杂的SQL解决的最大弊端在于将业务杂糅在了SQL中,要知道在一个典型的后端系统中,业务层下面有DAL数据访问层,而DAL才跟具体的SQL打交道。而这里相当于业务直接穿透DAL被放到了DAL下面的SQL。不利于维护,而且大SQL也是不推荐的。
优雅先生
优雅先生 博主

引用来自“泡不烂的凉粉”的评论

俺不想打击楼主,
直接更新数据,用函数找出end_at和当前时间比较的差值,取大数字
大概样子 end_at = date_add(IF(now()>end,now(),end),interval x day);
简单明了。
跟我第一种方案有很大差别?
没手机的不配发言
没手机的不配发言
俺不想打击楼主,
直接更新数据,用函数找出end_at和当前时间比较的差值,取大数字
大概样子 end_at = date_add(IF(now()>end,now(),end),interval x day);
简单明了。
甘薯
甘薯
写的很不错,
我的方案是加一个锁标志,
类似于乐观锁,
如果能成功获得锁,那么就进行下面的操作,
否则就退出。
Mysql 先SELECT 后UPDATE 问题

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

汤汤圆圆
2016/06/29
266
0
MySQL中SELECT+UPDATE处理并发更新问题解决方案分享

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

fzxu_05
2015/12/30
285
0
高并发架构系列:Redis缓存和MySQL数据一致性方案详解

一、需求起因 在高并发的业务场景下,数据库大多数情况都是用户并发访问最薄弱的环节。所以,就需要使用redis做一个缓冲操作,让请求先访问到redis,而不是直接访问MySQL等数据库。 这个业务...

架构之旅
2018/12/17
0
0
Redis 缓存和 MySQL 数据如何实现一致性?

需求起因 缓存和数据库一致性解决方案 在高并发的业务场景下,数据库大多数情况都是用户并发访问最薄弱的环节。所以,就需要使用redis做一个缓冲操作,让请求先访问到redis,而不是直接访问M...

zqcju
2019/05/30
378
0
Redis和mysql数据怎么保持数据一致的?

需求起因 在高并发的业务场景下,数据库大多数情况都是用户并发访问最薄弱的环节。所以,就需要使用redis做一个缓冲操作,让请求先访问到redis,而不是直接访问MySQL等数据库。 这个业务场景...

瑞查德-Jack
2019/01/15
594
0

没有更多内容

加载失败,请刷新页面

加载更多

【镜像】Bravo JSP Editor

Eclipse Marketplace 的 Bravo JSP Editor 已经不可用,原 Update Site也已经被作者关闭,但是作为 WYSIWYG(所见即所得)编辑器的经典以及优秀代表,还是有很大的研究价值。最近我发现了它的...

Baytars
26分钟前
30
0
大学毕业生应聘求职和MBTI职业性格测试

收到大环境的影响,各企业也是变的很不景气,但是这也正是给了求职者一个缓冲期。根据因果论,我始终觉得好事和坏事是交织在一起的,在某种程度上影响了我们的经济,但是过后,必定很迎来新的...

蛤蟆丸子
29分钟前
29
0
图解kubernetes控制器StatefulSet核心实现原理

StatefulSet是k8s中有状态应用管理的标准实现,今天就一起来了解下其背后设计的场景与原理,从而了解其适用范围与场景 1. 基础概念 首先介绍有状态应用里面的需要考虑的一些基础的事情,然后在...

8小时
33分钟前
34
0
Android IPC数据在内核空间中的发送过程分析

在上一篇文章Android请求注册服务过程源码分析中从Java层面和C++层面分析了服务请求注册的过程,无论Java还是C++最后都是将需要发送的数据写入的Parcel容器中,然后通过Binder线程持有对象I...

天王盖地虎626
34分钟前
25
0
Mysql导入数据库

需要导入一个mysql数据库,因为导出时的数据库与本机的数据库中有同名schema,所以想导入时创建一个不同的数据库名称,这样导入的数据就不影响原来本机上的数据了,具体操作如下: 1:mysql...

whoisliang
39分钟前
16
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部