假设MySQL数据库有一张会员表vip_member(InnoDB表),结构如下(uid,start_at,end_at,updated_at,active_status)
当一个会员想续买会员(只能续买1个月、3个月或6个月)时,必须满足以下业务要求:
- 如果end_at早于当前时间,则设置start_at为当前时间,end_at为当前时间加上续买的月数
- 如果end_at等于或晚于当前时间,则设置end_at=end_at+续买的月数
- 续买后active_status必须为1(即被激活)
1、问题分析:
对于上面这种情况,我们一般会先SELECT查出这条记录,然后根据查出记录的end_at再UPDATEstart_at和end_at,伪代码如下(为uid是1001的会员续1个月):
vipMember = SELECT * FROM vip_member WHERE uid=1001LIMIT 1 # 查uid为1001的会员
if vipMember.end_at < NOW():
UPDATEvip_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个月)。
2、解决方案:
1)我想到的第一种方案是把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;
2)事务,即用一个事务来包裹上面的SELECT+UPDATE操作:
那么是否包上事务就万事大吉了呢?显然不是。因为如果同时有两个事务都分别SELECT到相同的vip_member记录,那么一样的会发生数据覆盖问题。那有什么办法可以解决呢?难道要设置事务隔离级别为SERIALIZABLE,考虑到性能不现实。
我们知道InnoDB支持行锁。查看MySQL官方文档(innodblocking reads)了解到InnoDB在读取行数据时可以加两种锁:读共享锁和写独占锁。
读共享锁是通过下面这样的SQL获得的:
SELECT * FROMparent WHERE NAME = 'Jones'LOCK IN SHARE MODE;
如果事务A获得了先获得了读共享锁,那么事务B之后仍然可以读取加了读共享锁的行数据,但必须等事务Acommit或者roll back之后才可以更新或者删除加了读共享锁的行数据。
写独占锁是通过SELECT...FORUPDATE获得:
SELECT counter_field FROM child_codesFOR UPDATE;
如果事务A先获得了某行的写独占锁,那么事务B就必须等待事务Acommit或者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
3)第三种方案:乐观锁,类CAS机制
第二种加锁方案是一种悲观锁机制。而且SELECT...FORUPDATE方式也不太常用,联想到CAS实现的乐观锁机制,于是我想到了第三种解决方案:乐观锁。
具体来说也挺简单,首先SELECTSQL不作任何修改,然后在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则表明存在并发更新,那么只需要重试一下就好了。