MySQL中SELECT+UPDATE并发更新问题

清疚 2021-06-24 14:34 489阅读 0赞

假设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则表明存在并发更新，那么只需要重试一下就好了。