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),
KEY
goods_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)
这里写图片描述

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值