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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 26 4

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章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 。

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

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

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

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

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

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

最后此篇关于MySQL中SELECT+UPDATE处理并发更新问题解决方案分享的文章就讲到这里了,如果你想了解更多关于MySQL中SELECT+UPDATE处理并发更新问题解决方案分享的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

26 4 0

• visual-studio-2010 - Visual Studio 2010 Professional 解决方案/项目是否与 Visual Studio 2010 Premium 解决方案/项目 100% 兼容？

  我只是想知道要安装哪个版本的 Visual Studio 2010(专业版或高级版)提示升级项目.. 项目包括:asp.net mvc、数据库和silverlight。 最佳答案 通常，由不同版本的相

• [解决方案]几种通过iproute2来打通不同节点间容器网络的方式

  几种通过 iproute2 来打通不同节点间容器网络的方式 几种通过 iproute2 来打通不同节点间容器网络的方式 host-gw ipip vxlan 背景 之前由于需

• 【解决方案】基于数据库驱动的自定义TypeHandler处理器

  目录 前言 1、TypeHandler 简介 1.1转换步骤 1.2转换规则 2、JSON 转换 3、枚举转换 4、文章小结

• 【解决方案】Java互联网项目中常见的Redis缓存应用场景

  目录 前言 1、常见 key-value 2、时效性强 3、计数器相关 4、高实时性 5、排行榜系列 6、文章小结 前言 在笔者 3 年的

• 【解决方案】Java互联网项目中消息通知系统的设计与实现（下）

  目录 前言 四、技术选型 五、后端接口设计 5.1业务系统接口 5.2App 端接口 六、关键逻辑实现 6.1Red

• 【解决方案】Java互联网项目中消息通知系统的设计与实现（上）

  目录 前言 一、需求分析 1.1发送通知 1.2撤回通知 1.3通知消息数 1.4通知消息列表 二、数据模型设计

• 【解决方案】多租户技术架构设计入门（一）

  目录 前言 一、多租户的概念 二、隔离模式 2.1独立数据库模式 2.2共享数据库独立数据架构 2.3共享数据库共享数据架构

• 【解决方案】MySQL中的死锁问题还能这样解决（文末送书）

  导读： 虽然锁在一定程度上能够解决并发问题，但稍有不慎，就可能造成死锁。本文介绍死锁的产生及处理。 死锁的产生和预防 发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条

• javascript - 获取波斯月的最后一天 + 解决方案

  在浏览网页后，我找不到任何功能来执行此操作，我有可行的个人解决方案。也许它对某人有用。 **使用 Moment 插件转换日期。***moment(currentPersianDate).clone()

• 检测数字手写的 OCR 解决方案？

  是否有一种解决方案可以很好地处理数字(1-10)手写？我试过tesseract，但我得到的只是垃圾。 理想情况下是 OSS，但商业也可以。 最佳答案 OpenCV 现在带有手写数字识别 OCR 示例。

• multithreading - Delphi死锁解释/解决方案

  在服务器应用程序上，我们有以下内容:一个称为 JobManager 的单例类。另一个类，Scheduler，不断检查是否需要向 JobManager 添加任何类型的作业。 当需要这样做时，调度程序会执

• javascript - 用于在应用程序中处理和捕获错误的工具/解决方案

  关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。 想改进这个问题？将问题更新为 on-topic对于堆栈溢出。 5年前关闭。 Improve this qu

• r - 解决方案。有代理时如何install_github

  当您尝试从 GitHub 存储库安装某些 R 包时 install_github('rWBclimate', 'ropensci') 如果您遇到以下错误: Installing github repo

• WPF 字体模糊问题-解决方案

  问题在以下链接中进行了描述和演示: Paul Stovell WPF: Blurry Text Rendering www.gamedev.net forum Microsoft Connect: W

• 用于科学记数格式格式化的 R 解决方案

  我正在寻找一种解决方案，使用标准格式 a × 10 b 在科学记数法下格式化 R 中的数字。一些同行评审的科学期刊都要求这样做，并且手动修改图表可能会变得乏味。 下面是 R 标准“E 表示法”的示例，

• java - 如何从另一个java应用程序内部启动资源jar - 解决方案

  已编辑解决方案(如下...) 我有一个启动画面，它被打包到它自己的 jar 中。它有效。 我可以通过以下方式从另一个 java 应用程序内部调用 Splash.jar: Desktop.getDesk

• 用于创建门户的 .NET 解决方案

  什么是创建像 PageFlakes 或 iGoogle 这样的门户网站的好框架/包？ ？我们希望创建一个为员工提供 HR 服务的员工/HR 门户，但我们也需要一种足够灵活的产品，以便我们可以使用它来为

• 用于科学记数格式格式化的 R 解决方案

  我正在寻找一种解决方案，使用标准格式 a × 10 b 在科学记数法下格式化 R 中的数字。一些同行评审的科学期刊都要求这样做，并且手动修改图表可能会变得乏味。 下面是 R 标准“E 表示法”的示例，

• search - 解决方案+遗传

  如何将 solr 与 heritrix 集成？ 我想使用 heritrix 归档一个站点，然后使用 solr 在本地索引和搜索该文件。 谢谢 最佳答案 使用 Solr 进行索引的问题在于它是一个纯文本

• jquery - 全日历工作时间 [解决方案]

  完整日历不包含工作时间功能选项(在任何一天的议程 View 中选择第一行和最后一行 - 例如公司不工作)。我做到了类似的事情: viewDisplay: function(view){