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
•第二种方案写独占锁,可以解决问题,但不常用
•第三种方案应该是比较中庸的解决方案,并且甚至可以不加事务,也是我个人推荐的方案
此外,乐观锁和悲观锁的选择一般是这样的(参考了文末第二篇资料):
•如果对读的响应度要求非常高,比如证券交易系统,那么适合用乐观锁,因为悲观锁会阻塞读
•如果读远多于写,那么也适合用乐观锁,因为用悲观锁会导致大量读被少量的写阻塞
•如果写操作频繁并且冲突比例很高,那么适合用悲观写独占锁