您的位置：首页 > 数据库 > mysql  >  MySQL中SELECT+UPDATE处理并发更新问题解决方案分享

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

 更新时间：2014年5月31日 01:01  点击：3266

问题背景：

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

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

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


此外，乐观锁和悲观锁的选择一般是这样的（参考了文末第二篇资料）：

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

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

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

[!--infotagslink--]

相关文章

  • Mybatis Plus select 实现只查询部分字段

    这篇文章主要介绍了Mybatis Plus select 实现只查询部分字段的操作，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-09-01

  • Photoshop火龙变冰龙制作教程分享

    今天小编在这里就来给Photoshop的这一款软件的使用者们来说下火龙变冰龙的制作教程，各位想知道具体的制作步骤的使用者们，那么下面就快来跟着小编一起看看制作教程吧。...2016-09-14

  • Mysql select语句设置默认值的方法

    1.在没有设置默认值的情况下： 复制代码 代码如下:SELECT userinfo.id, user_name, role, adm_regionid, region_name , create_timeFROM userinfoLEFT JOIN region ON userinfo.adm_regionid = region.id 结果：...2014-05-31

  • vue 监听 Treeselect 选择项的改变操作

    这篇文章主要介绍了vue 监听 Treeselect 选择项的改变操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-09-01

  • Illustrator渐变网格工具绘制可爱的卡通小猪教程分享

    今天小编在这里就来给Illustrator的这一款软件的使用者们来说一说渐变网格工具绘制可爱的卡通小猪的教程，各位想知道具体制作方法的使用者们，那么下面就快来跟着小编一...2016-09-14

  • Photoshop功夫熊猫电影海报制作步骤分享

    不知不觉功夫熊猫这部电影已经出到3了，今天小编在这里要教大家的是用Photoshop制作功夫熊猫3的海报，各位想知道制作方法的，那么下面就来跟着小编一起看看吧。 给各...2016-09-14

  • vue Treeselect下拉树只能选择第N级元素实现代码

    这篇文章主要介绍了vue Treeselect下拉树只能选择第N级元素实现代码，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-09-01

  • Vue select 绑定动态变量的实例讲解

    这篇文章主要介绍了Vue select 绑定动态变量的实例讲解，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-10-23

  • vue treeselect获取当前选中项的label实例

    这篇文章主要介绍了vue treeselect获取当前选中项的label实例，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-09-01

  • photoshop日系小清新通透人像调色教程分享

    今天小编在这里就来给photoshop的这一款软件的使用者们来说一说日系小清新通透人像的调色教程，各位想知道具体的调色步骤的使用者们，那么下面就快来跟着小编一起看一看...2016-09-14

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

    问题背景： 假设MySQL数据库有一张会员表vip_member（InnoDB表），结构如下： 当一个会员想续买会员（只能续买1个月、3个月或6个月）时，必须满足以下业务要求： &#8226;如果end_at早于当前时间，则设置start_at为当前时间，end_at为当前时...2014-05-31

  • php威盾解密的例子分享

    下面来给大家分享两个关于php威盾解密的例子，一个是批量解密一个是超级算法的解密都非常的好，大家有举的进入参考。 例子，批量解密 代码如下 复制代码 ...2016-11-25

  • PHP mysql与mysqli事务使用说明 分享

    mysqli封装了诸如事务等一些高级操作，同时封装了DB操作过程中的很多可用的方法。应用比较多的地方是 mysqli的事务。...2013-10-02

  • jQuery为动态生成的select元素添加事件的方法

    下面小编就为大家带来一篇jQuery为动态生成的select元素添加事件的方法。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧...2016-09-01

  • javascript获取select标签选中的值

    这篇文章主要介绍javascript获取select标签选中的值方法,比较实用，需要的朋友可以参考下。...2016-06-12

  • Mybatis执行update失败的解决

    这篇文章主要介绍了Mybatis执行update失败的解决方案，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教...2021-09-01

  • php实例分享之html转为rtf格式

    核心代码： 复制代码 代码如下:<!--?php$html2RTFCom = new COM("HTML2RTF.Converter");$html2RTFCom--->PreserveImages = true;$html2RTFCom->PageNumbers = 1;$html2RTFCom->PageNumbersAlignH = 1;$html2RTFCom->Pa...2014-06-07

  • 解决Mybatis中mapper.xml文件update,delete及insert返回值问题

    这篇文章主要介绍了解决Mybatis中mapper.xml文件update,delete及insert返回值问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-11-23

  • 美图秀秀制作卡通全家福教程分享

    今天小编在这里就来给美图秀秀的这一款软件的使用者们来说下制作卡通全家福的教程，各位想知道具体制作方法的使用者们，那么下面就快来跟着小编一起看一看教程吧。 ...2016-09-14

  • 微信小程序实现canvas分享朋友圈海报

    这篇文章主要为大家详细介绍了微信小程序实现canvas分享朋友圈海报，文中示例代码介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2020-06-21