MySQL高并发下读取脏数据问题的解决方案

最新推荐文章于 2024-09-22 21:41:43 发布
最新推荐文章于 2024-09-22 21:41:43 发布
阅读量1.2w 收藏 7
点赞数 1
分类专栏： MySql PHP
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yeshencat/article/details/74356985
版权
PHP 同时被 2 个专栏收录
12 篇文章 0 订阅
订阅专栏
MySql
3 篇文章 0 订阅
订阅专栏

MySQL 在遇到高并发读写的情况下会读到脏数据，如何理解？举一个简单的栗子：
用户A、用户B、用户C 抢购一件商品，每人限购一件，商品库存为10，假如 A、B、C 并发情况下则同时得到 库存为 10，同时对库存10进行操作，看下相关示意图：

这里写图片描述
用户A 操作: 库存 10 -1 =9，对应的 SQL: update table set goods_number=goods_number-1;
用户B 操作: 库存 10 -1 =9，对应的 SQL: update table set goods_number=goods_number-1;
用户C 操作: 库存 10 -1 =9，对应的 SQL: update table set goods_number=goods_number-1;
最后得到的结果库存还剩下9，可想而知这个结果是错误的，这里我们只讨论MySQL 去解决这个问题，其他的什么文件锁、悲/乐观锁、redis 单线程解决方案、各种单线程队列等，不是讨论的范畴，请自行科普。

下面我贴一份儿日志看看类似的情况：
表结构：
goods_x 表:
CREATE TABLEgoods_x(
goods_idint(10) unsigned NOT NULL AUTO_INCREMENT,
goods_namevarchar(120) NOT NULL DEFAULT '',
goods_numberint(11) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (goods_id),
KEYgoods_number(goods_number)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

order 表：

 CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_sn` varchar(64) NOT NULL COMMENT '订单号',
  `goods_id` int(10) unsigned NOT NULL COMMENT '商品ID',
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `buy_number` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '购买商品数量',
  `created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '下单时间',
  `updated_at` int(10) unsigned NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`,`order_sn`),
  KEY `id` (`id`),
  KEY `goods_id` (`goods_id`),
  KEY `user_id` (`user_id`),
  KEY `order_sn` (`order_sn`)
) ENGINE=MyISAM AUTO_INCREMENT=11094 DEFAULT CHARSET=utf8

代码清单：

<?php
/**
 * 订单超发 ab模拟
 * @filename index1.php
 * @touch date
 * @author kevin<askyiwang@gmail.com>
 * @license http://www.zend.com/license/3_0.txt   PHP License 3.0
 * @version 1.0.0
 *
 */
header('Content-Type:text/html; charset=UTF-8');
$requet_log_file = './request.log';
$sell_log_file = './sell.log';
$request_time = 0;
if(file_exists($requet_log_file)) {
    $request_time = file_get_contents($requet_log_file);
}
$request_time += 1;
file_put_contents($requet_log_file, $request_time);

/**
 * 数据库连接
 *
 **/
$host = '127.0.0.1';
$port = '3306';
$dbname = 'test';
$user = 'root';
$db_password = '8ea352a33a';
$charset = 'UTF8';
try {
    $db = new \PDO('mysql:host='.$host.';port='.$port.';dbname='.$dbname.'', $user, $db_password, [\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$charset.'']);
} catch (\PDOException $e) {
    die("Connect Error Infomation:" . $e->getMessage());
}

/**
 * 生成订单号,假定是优化的
 *
 **/
function orderSn(\PDO $db) {
    $has_been = true;
    $order_sn = date('Ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
    while($has_been) {
        $sql = 'SELECT order_sn FROM `order` where order_sn=:order_sn';
        $sth = $db->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
        $sth->execute([':order_sn' => $order_sn]);
        $data = $sth->fetch();
        if(empty($data)) {
            $has_been = false;
        } else {
            $order_sn = date('Ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
        }
    }
    return $order_sn;
}

// 假定前端提交的数据
$uid = rand(1,10000);
$goods_id = 1;//rand(1,1000);
$buy_number = 1;


// 查询商品信息
$sql = 'SELECT goods_id, goods_number FROM `goods_x` where goods_id=1';
$res = $db->query($sql)->fetch();
if(!empty($res) && (($res['goods_number'] - $buy_number) > 0)) {

    //减少库存
    $down_sql = 'UPDATE `goods_x` SET goods_number=:goods_number where goods_id=:goods_id';
    $sth_goods = $db->prepare($down_sql);
    $sth_goods->bindValue(":goods_number", ($res['goods_number']-$buy_number));
    $sth_goods->bindParam(":goods_id", $goods_id);
    $r = $sth_goods->execute();
    if($r) {
        $str = '当前请求:'.$request_time.'减去库存成功,当前库存容量:'.($res['goods_number']-$buy_number).PHP_EOL;
        file_put_contents($sell_log_file, $str, FILE_APPEND);
    } else {
        $str = '当前请求:'.$request_time.'减去库存失败,当前库存容量:'.$res['goods_number'].PHP_EOL;
        file_put_contents($sell_log_file, $str, FILE_APPEND);
    }

    //插入订单表
    $order_sn = orderSn($db);
    $sql_query = "INSERT INTO `order`(order_sn,goods_id,user_id,buy_number,created_at,updated_at) VALUES(:order_sn,:goods_id,:user_id,:buy_number,:created_at,:updated_at);";
    $sth = $db->prepare($sql_query);
    $sth->bindParam(":order_sn", $order_sn);
    $sth->bindParam(":goods_id", $goods_id);
    $sth->bindParam(":user_id", $uid);
    $sth->bindParam(":buy_number", $buy_number);
    $sth->bindValue(":created_at", time());
    $sth->bindValue(":updated_at", time());
    $status = $sth->execute();
    $last_id = $db->lastInsertId();
    if($status) {
        echo '插入订单表成功，ID:'.$last_id.PHP_EOL;
    } else{
        echo '插入订单表失'.PHP_EOL;
        exit();
    }
}

AB 压测模拟结果：
这里写图片描述

如何优化解决这个问题呢？我们将引入乐观锁的事情，CAS(Compare And Set)来降低读写锁冲突。常见的版本通过版本比较来实现乐观锁，当然也很简单新增version 字段来保证自己本次操作是独立的，操作完并更新version得到new_version，当并发时候别人拿到之前version则操作失败，简单看看示意图：
这里写图片描述

如图所示：当发生并发时候，Client-1、Client-2、Client-3，获取到的 goods_number 都为10，version 都为 0，当Client -1~3任意一个用户操作，则version 将会增加1，这样一来其他人就会更新失败 。

代码清单：

<?php
/** 
 * 订单超发模拟 ab
 * @filename index.php
 * @touch date
 * @author kevin<askyiwang@gmail.com>
 * @license http://www.zend.com/license/3_0.txt   PHP License 3.0
 * @version 1.0.0
 * 准备几张表：
 * goods_x  商品表
 * order    订单表
 *
 */
header('Content-Type:text/html; charset=UTF-8');
$requet_log_file = './request.log';
$sell_log_file = './sell.log';
$request_time = 0;
if(file_exists($requet_log_file)) {
    $request_time = file_get_contents($requet_log_file);
}
$request_time += 1;
file_put_contents($requet_log_file, $request_time);

/**
 * 数据库连接
 *
 **/
$host = '127.0.0.1';
$port = '3306';
$dbname = 'test';
$user = 'root';
$db_password = '8ea352a33a';
$charset = 'UTF8';
try {
    $db = new \PDO('mysql:host='.$host.';port='.$port.';dbname='.$dbname.'', $user, $db_password, [\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$charset.'']);
} catch (\PDOException $e) {
    die("Connect Error Infomation:" . $e->getMessage());
}

/**
 * 生成订单号,假定是优化的
 *
 **/
function orderSn(\PDO $db) {
    $has_been = true;
    $order_sn = date('Ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
    while($has_been) {
        $sql = 'SELECT order_sn FROM `order` where order_sn=:order_sn';
        $sth = $db->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
        $sth->execute([':order_sn' => $order_sn]);
        $data = $sth->fetch();
        if(empty($data)) {
            $has_been = false;
        } else {
            $order_sn = date('Ymd').substr(implode(NULL, array_map('ord', str_split(substr(uniqid(), 7, 13), 1))), 0, 8);
        }
    }
    return $order_sn;
}

// 假定前端提交的数据
$uid = rand(1,10000);
$goods_id = 1;//rand(1,1000);
$buy_number = 1;


// 查询商品信息
$sql = 'SELECT goods_id, goods_number,version FROM `goods_x` where goods_id=1';
$res = $db->query($sql)->fetch();
try{
    if(!empty($res) && (($res['goods_number'] - $buy_number) > 0)) {
        $db->beginTransaction();
        //减少库存
        $down_sql = 'UPDATE `goods_x` SET goods_number=:goods_number,version=:new_version where goods_id=:goods_id and version=:version';
        $sth_goods = $db->prepare($down_sql);
        $sth_goods->bindValue(":goods_number", ($res['goods_number']-$buy_number));
        $sth_goods->bindValue(":new_version", ($res['version']+1));
        $sth_goods->bindParam(":goods_id", $goods_id);
        $sth_goods->bindParam(":version", $res['version']);
        $r = $sth_goods->execute();
        if($r) {
            $str = '操作版本:'.$res['version'].'减去库存成功,当前库存容量:'.($res['goods_number']-$buy_number).PHP_EOL;
            file_put_contents($sell_log_file, $str, FILE_APPEND);
        } else {
            $str = '操作版本:'.$res['version'].'减去库存失败,当前库存容量:'.$res['goods_number'].PHP_EOL;
            file_put_contents($sell_log_file, $str, FILE_APPEND);
            throw new \PDOException($str);//那个错误抛出异常
        }

        //插入订单表
        $order_sn = orderSn($db);
        $sql_query = "INSERT INTO `order`(order_sn,goods_id,user_id,buy_number,created_at,updated_at) VALUES(:order_sn,:goods_id,:user_id,:buy_number,:created_at,:updated_at);";
        $sth = $db->prepare($sql_query);
        $sth->bindParam(":order_sn", $order_sn);
        $sth->bindParam(":goods_id", $goods_id);
        $sth->bindParam(":user_id", $uid);
        $sth->bindParam(":buy_number", $buy_number);
        $sth->bindValue(":created_at", time());
        $sth->bindValue(":updated_at", time());
        $status = $sth->execute();
        $last_id = $db->lastInsertId();
        if($status) {
            echo '插入订单表成功，ID:'.$last_id.PHP_EOL;
        } else{
            echo '插入订单表失'.PHP_EOL;
            throw new \PDOException('插入订单表失');//那个错误抛出异常
        }
        $db->commit();
    }
} catch (PDOException $e){
        echo $e->getMessage();
        $pdo->rollback();
}

详细日志（库存1000）
这里写图片描述

总结：
读取到脏数据的解决办法，尽量串行化操作。

深夜的猫
关注
专栏目录
解决数据库高并发数据脏读问题-全面理解数据库事务ACID特点
延瓒
05-01 2740
一、什么是事务   MySQL事务只要勇于处理操作量大，复杂度高的数据。 比如：在管理系统中删除一个人员的基本资料，也要删除人员的相关信息，文件，邮箱等等，这些数据库的操作语句就构成了一个事务。一个事务是一个只包含所有读/写操作成功的集合。叫做事务 一个事务的本质上有四个特点：ACID，当然ACID为简称如下： A：Atomicity原子性 C：Consistency一致性 I ： Isol...
Java并发编程5 —— 并发脏读问题
扎瓦菜鸟
09-16 1291
两个线程同时修改共享数据容易产生线程安全问题。如果是在数据库中，一条数据由很多个字段组成，假设一个线程要去修改其中几个字段的值，与此同时有另一个线程想要读取这条数据，那么会产生什么问题呢。 下面的代码中，Student类有学号id、姓名name、专业名dprt三个属性。某同学申请转专业，需要修改他的学号和专业名。修改学号和专业名需要1秒钟完成，同时有另一个线程在查他的信息，查询速度很快。 i...
mysql 并发 脏读_数据库并发导致的脏读 不可重复读 幻读的处理方法
weixin_39775428的博客
02-27 249
修正2020-04-23 之前是刚参加工作的理解 很多问题,现在回来重新梳理事物的特性原子性表示一个最小的逻辑单元,要么都执行 要么都不执行一致性事物处理前与处理后的状态的要是一致的(a账户有200元 b账户有300元 共计500元 a账户给b账户转账100元。事物处理后2个账户总额也为500元)隔离性每个事物都有自己的数据空间，使事物的处理结果不会被别的事物所影响持久性事物提交 数据就永久...
Mysql是如何解决并发问题的？（脏读、幻读、不可重复读）
最新发布
weixin_50971083的博客
09-22 846
什么是脏读?脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交(commit)到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。什么是不可重复读?
mysql 终端模拟_mysql客户端模拟脏读、幻读和可重复读
weixin_35999669的博客
02-02 139
如果操作一下时报出错误：在数据库中执行 SET GLOBAL BINLOG_FORMAT = mixed;执行后可通过SELECT * FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = ‘BINLOG_FORMAT’ ;进行查看。通过客户端选择可用数据库：use db_test(模拟数据库)以下由客户端进行模拟：1.脏读(...
MySQL的事物基本要素及四种事务隔离级别
记录，是一种习惯；坚持，是一种品质。
09-29 298
一、事务的基本要素（ACID） 　　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。 　　2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。 　　 3、隔离性（Isolation）：同一时间，只允许一个事务请求同.
MySQL 数据库如何解决高并发问题
09-08
MySQL 数据库在面对高并发场景时，需要采取一系列优化措施以确保系统稳定性和性能。以下是一些关键的优化策略： 1. **短距离**：优化数据传输路径，减少数据库访问延迟。 - **页面静态化**：对于不常变化的页面，...
Mysql事务并发问题解决方案
12-14
尽管悲观锁可以确保数据一致性，但其带来的问题是高并发环境下可能导致大量事务阻塞，降低系统性能。 为了解决阻塞问题，乐观锁是一种可选策略。乐观锁假设并发事务不会冲突，只有在更新数据时才检查是否发生冲突。...
MySQL并发更新数据时的处理方法
12-16
本文主要探讨了在并发环境中MySQL如何处理UPDATE语句，以及两种常见的解决方案：通过事务显式加锁和使用乐观锁机制。 首先，我们澄清一个概念：UPDATE语句并不总是全程加锁。例如，在SQL语句`UPDATE table1 SET num...
mysql账户余额高并发_MySQL高并发事务问题及解决方案
weixin_34185396的博客
02-06 1146
事务的概念事务 可以理解为一个 独立的工作单元, 在这个独立的工作单元中, 有一组操作; 放在事务(独立工作单元)中的多个操作, 要么全部执行成功, 要么全部执行失败。不免俗套, 这还是通过最经典的银行转账应用来解释一下假设有两个角色 'Iron Man'(余额500), 'Wolverine'(余额15), 现在 Iron Man 通过该银行应用给 Wolverine 转账100元, 那么本次转...
脏读数据解决方案
李卫康的博客
06-30 7886
数据库：create table account (id int primary key auto_increment,name varchar(20),money double);insert into account values(null,'a',100);insert into account values(null,'b',100);1.演示脏读开两个客户端的窗口，A客户端的级别是数据库...
丢失的修改、不可重复读、读脏数据、幻影读
u010398493的专栏
07-27 1282
常见并发一致性问题包括：丢失的修改、不可重复读、读脏数据、幻影读（幻影读在一些资料中往往与不可重复读归为一类）。丢失修改下面先来看一个例子，说明并发操作带来的数据的不一致性问题。考虑飞机订票系统中的一个活动序列:甲售票点（甲事务）读出某航班的机票余额A,设A=16.乙售票点（乙事务）读出同一航班的机票余额A,也为16.甲售票点卖出一张机票,修改余额A←A-1.所以A为15,把A写回数据库.乙售票点
mysql数据库脏字段清洗_数据库的脏数据问题
weixin_31485197的博客
01-27 1581
脏读dirty reads：当事务读取还未被提交的数据时，就会发生这种事件。举例来说：Transaction 1 修改了一行数据，然后 Transaction 2 在 Transaction 1 还未提交修改操作之前读取了被修改的行。如果 Transaction 1 回滚了修改操作，那么 Transaction 2 读取的数据就可以看作是...
mysql频繁读取脏数据_大并发情况下mysql 如何解决脏数据
weixin_36314117的博客
01-18 296
package mainimport "fmt"import "database/sql"import _ "github.com/go-sql-driver/mysql"import "time"func main() {ch := make(chan int, 10)go searchsql(ch)go searchsql(ch)go searchsql(ch)time.Sleep(1 * t...
抽奖活动的高可用、高并发优化
weixin_33736832的博客
05-13 2751
这几年工作中做过不少营销活动，这里以抽奖活动为例，讨论一下如何设计出一个高可用、高并发的营销系统。 高可用、高并发架构的核心是分流和限流。系统架构时，应根据每一种营销活动的场景与特性，制定不同的分流、限流方案。 一业务 在开始进行架构讨论前，我们的简单描述一下业务，以方便我们有针对性的进行讨论。 1. 业务需求 公司希望拉更...
mysql中模拟实现脏读，不可重复读和幻读及其解决方案
yxg520s的博客
01-11 2244
1 脏读 1.1 说明 在两个事务中，一个事务读到了另一个事务未提交的数据（即事务A读取到事务B修改数据，当事务B未提交数据到数据库或出错时，事务A再进行读取发现数据已修改。 1.2 mysql命令行模拟脏读 1.2.1 在 “读未提交” 的隔离级别下测试 创建数据库test以及表rooms SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for roo
MySQL并发导致的脏数据分析
wang_keng的博客
08-31 7669
记录一下一个并发导致的脏数据问题（基于MySQL）。问题描述（银行操作员例子）：比如A、B操作员同时读取一余额为1000元的账户，A操作员为该账户增加100元，B操作员同时为该账户扣除50元，A先提交，B后提交。最后实际账户余额为1000-50=950元，但本该为1000+100-50 = 1050 。 首先分析下如何会导致问题的出现： 我们来些一个伪代码表示下A操作员操作执行的过程：
【数据库并发操作带来的问题 - - - 丢失修改、不可重复读、读脏数据】
阿年的博客
04-27 2547
数据库并发操作带来的问题：丢失修改、不可重复读、读脏数据
数据库---并发控制及其带来的问题丢失修改,读脏数据,不可重复读及解决(程序员级)
技术就是个纸老虎---为之，则难者亦易矣；不为，则易者亦难矣。
10-02 2029
 
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值