关闭

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

标签: mysql
304人阅读 评论(0) 收藏 举报
分类:

这篇文章主要介绍了MySQL中SELECT+UPDATE处理并发更新问题解决方案分享,需要的朋友可以参考下

问题背景:

假设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

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

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


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

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

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

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

http://www.jb51.net/article/50103.htm
0
0
查看评论

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

这篇文章主要介绍了MySQL中SELECT+UPDATE处理并发更新问题解决方案分享,需要的朋友可以参考下。 问题背景 假设MySQL数据库有一张会员表vip_member(InnoDB表),结构如下: 当一个会员想续买会员(只能续买1个月、3个月或6个月)时,必须满足以下...
  • chunxiaqiudong5
  • chunxiaqiudong5
  • 2015-07-22 09:22
  • 189

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

这篇文章主要介绍了MySQL中SELECT+UPDATE处理并发更新问题解决方案分享,需要的朋友可以参考下 问题背景: 假设MySQL数据库有一张会员表vip_member(InnoDB表),结构如下:   当一个...
  • yongche_shi
  • yongche_shi
  • 2015-06-02 15:54
  • 379

mysql性能优化(五) mysql中SELECT+UPDATE处理并发更新问题解决方案

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

Mysql-select+update并发更新问题

问题描述:  并发事务场景下存在数据更新被覆盖的问题,具体场景如下图所示:事务隔离级别是read-committed,事务1和事务2同时更新同一行数据,假设事务1先获取锁执行了更新操作,事务2后执行,数据库中subid的最终值为32,事务1更新的值被覆盖。 原因:  事务隔离级别是read-com...
  • yangguosb
  • yangguosb
  • 2017-10-11 11:06
  • 157

SELECT+UPDATE处理并发更新

http://www.phpxs.com/post/3498/
  • buyueliuying
  • buyueliuying
  • 2016-09-26 16:44
  • 394

mysql 将select查询的值赋给update set语句中

表tenant_oper结构: create table `tenant_oper` ( `id` int(11) not null auto_increment, `oper_name` varchar(255) not null, `oper_code` varchar(255)...
  • u013498057
  • u013498057
  • 2015-04-08 10:45
  • 2674

MySQL中SELECT+UPDATE并发更新问题

假设MySQL数据库有一张会员表vip_member(InnoDB表),结构如下(uid,start_at,end_at,updated_at,active_status)   当一个会员想续买会员(只能续买1个月、3个月或6个月)时,必须满足以下业务要求: 如果end_at早于...
  • liuxiao723846
  • liuxiao723846
  • 2015-10-09 17:34
  • 743

mysql处理高并发-保证数据准确性

问题描述: 压力高并发情况下  执行update方法 得到结果非预期结果   例如: 多个线程执行 充值金额方法          1、先查询出目前金额信息a       &#...
  • lu930124
  • lu930124
  • 2017-05-26 18:53
  • 1686

[MySQL] 并发更新性能测试

MySQL 版本:5.7.9 表结构:CREATE TABLE `award` ( `id` int(11) NOT NULL AUTO_INCREMENT, `award` varchar(255) DEFAULT NULL, `count` int(11) NOT NULL COM...
  • u012099869
  • u012099869
  • 2016-10-13 18:34
  • 2566

[MYSQL]如何并发查询并更新

【场景】多个应用实例,不约而同地从数据库查询哪些任务未被执行,然后更新这些任务的状态字段为“正在执行”。 (简述,多个消费者争领任务,同时需要宣布哪个任务被自己领取,而不至于让多个消费者重复领取同一个任务)   方案一:SELECT FOR UPDATE(糟糕的方案╮(╯...
  • dujianxiong
  • dujianxiong
  • 2017-02-03 19:46
  • 2740
    个人资料
    • 访问:703263次
    • 积分:7268
    • 等级:
    • 排名:第3670名
    • 原创:86篇
    • 转载:280篇
    • 译文:0篇
    • 评论:52条
    博客专栏
    文章分类
    最新评论