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

这篇文章主要介绍了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个月):

[php]  view plain copy
  1. vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员  
  2.  if vipMember.end_at < NOW():  
  3.      UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001  
  4.  else:  
  5.      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

如下:

[php]  view plain copy
  1. UPDATE vip_member   
  2. SET   
  3.     start_at = CASE  
  4.         WHEN end_at < NOW()   
  5.             THEN NOW()  
  6.         ELSE start_at  
  7.         END,  
  8.     end_at = CASE  
  9.         WHEN end_at < NOW()  
  10.             THEN DATE_ADD(NOW(), INTERVAL #duration:INTEGER# MONTH)  
  11.         ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH)  
  12.         END,  
  13.    active_status=1,  
  14.    updated_at=NOW()  
  15. WHERE uid=#uid:BIGINT#  
  16. LIMIT 1;  

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

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

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

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

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

[php]  view plain copy
  1. SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;  

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

[php]  view plain copy
  1. SELECT counter_field FROM child_codes FOR UPDATE;  
  2. UPDATE child_codes SET counter_field = counter_field + 1;  

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

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

[php]  view plain copy
  1. vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 FOR UPDATE # 查uid为1001的会员  
  2. if vipMember.end_at < NOW():  
  3.     UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001  
  4. else:  
  5.     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条件。如下:

[php]  view plain copy
  1. vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid为1001的会员  
  2. cur_end_at = vipMember.end_at  
  3. if vipMember.end_at < NOW():  
  4.     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  
  5. else:  
  6.     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

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

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

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

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

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

如果写操作频繁并且冲突比例很高,那么适合用悲观写独占锁
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值