MySQL高并发 – 更新库存

介绍

PHP+MySql 解决数据库高并发情况下防止库存多卖，超卖等情况。

核心

MySql数据库引擎：InnoDB（支持事务，表锁，行锁）

事务（Transaction）：一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

悲观锁：正如其名，具有强烈的独占和排他特性。它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

案例

首先创建两个数据表

/*创建一个库存表（发卡）*/
CREATE TABLE `kami`(
    `id` int NOT NULL AUTO_INCREMENT,
    `kami` varchar(255) NOT NULL DEFAULT '',
    `state` tinyint NOT NULL DEFAULT '0',
    PRIMARY KEY(`id`)
) ENGINE = InnoDB CHARSET=utf8mb4;
/*创建一个记录表*/
CREATE TABLE `log`(
    `id` int NOT NULL AUTO_INCREMENT,
    `kami` varchar(255) NOT NULL DEFAULT '',
    `time` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY(`id`)
) ENGINE = InnoDB CHARSET=utf8mb4;

INSERT INTO `kami` (`id`, `kami`, `state`) VALUES
(1, '8QLY0YGC2RWMT3CF6T2P8MVSN6AOOUZW', 0),
(2, 'GGWPPKF0NH3OWY0Q5W6N8BKUO6WDLLHE', 0),
(3, 'IS5NWEH2J1O59XBECD5AMONPJ2HM9848', 0),
(4, 'CD4Z4BNQESMQABWINX47JHV9QLMEZGY8', 0),
(5, '5P47CUJ34OT0582C4LJS8HOLSPSRCYN0', 0),
(6, 'O81D5VCH0YI21LRLFV8CWFNN8R3UEC0T', 0),
(7, 'F7JE5Y4VTGU7A8GLMLJ35W6252Q5RUVY', 0),
(8, '9WJZ16V6EW7NEQVVS7EU392PEM6NKR7R', 0),
(9, 'KBV2LCBQPH3RJFPODFKV513KSFPK8ACH', 0),
(10, 'HUG84W54TPWLTGTGJSCHMU9RRCEPTRN1', 0),
(11, '9SCJTLVAMRJFZFP1F44NS3G8T4B70PQ5', 0),
(12, 'SD778QKL9QZNFHYKLCT9ZKJ3ZQFN19BE', 0),
(13, 'RM9Q0HFWCFJHWGKOV9BAFO71NWVV90NR', 0),
(14, 'DXZGCSIN7EPAH8MOI362W0M208SKYE52', 0),
(15, '8444YY3ETGAXJUSWXREM4HDRCID9VQBL', 0),
(16, '9KYJB5IZST4GZMBTTFYUVXM1KMIQM6VG', 0),
(17, 'RAZOVDCJI8XL5N4S15HJYSA6BCNJWT7S', 0),
(18, 'DRDZW1FXAZ6DYWL7A6XK9IU3H2KIGFIU', 0),
(19, '78O4KBZ62U7J2SG0IY2YOXI21YGR3NBH', 0),
(20, 'EOH7X0USNLV6IJ0FJI52KKU9V5F2Y1S4', 0),
(21, '9CQ4YXQ22BOJQY72025IHXNSSYVBXP3V', 0),
(22, 'JOBEY5PHU25EDNQ9REM7Q618HPQ1QX55', 0),
(23, 'T2NDQDESQW7BAAY9SXO0IK5U52QJ7KA4', 0),
(24, '6SI2VY2A29YV9KLU1AUQOH0PC1BB6HPU', 0),
(25, 'H70HALSZBB71KPOJ78Z613Q8FBDZ2LOR', 0),
(26, '6WM7S9SVEO7UH1QCTCPMDMDIIQWPLBIY', 0),
(27, 'JE2UYZLZLA0BZV7SAWJY2DLRDRAOTN40', 0),
(28, 'UTQZBU8346ZRONUBT2AM2MZD64OD39BF', 0),
(29, 'OT4ZX4Z31Z7PW2HEMKZRFJZI4ZCNFD57', 0),
(30, 'JRRAU2L2MR2M201VZHH0ULP0W3IMYINL', 0),
(31, '6NQWVZ1PAVDJ5K05Y17VTWZRHI6SUC3X', 0),
(32, 'AIDLU22BUDVM75WC1ZTTPACHK4A1X582', 0),
(33, 'WE8ULBTE3QAHMT5XUYVBNGGZEMVKONJK', 0),
(34, '2JN43S1G0X4DJKCUGJZUC657WEVHTGAE', 0),
(35, '8UYOZYF4GQ4OLFB0ODJ2X8DCQ22G0I4E', 0),
(36, '97QVD9YXUPGLV6G1N888NLZ5ZOP4F3CY', 0),
(37, 'VT4EUGXSEUHA6I40GALKSS3VAIAHXQUP', 0),
(38, 'SZOIVDC0UF2P2NYVUZWOL1OUCJ7D7VYL', 0),
(39, 'O4PQPSBDPQHKG7DAVG2HJ01FR2S63NYU', 0),
(40, 'DNMNIOLUAWVGPK5FU76J80U63E30ZU21', 0),
(41, 'TJ45FG5XGZJPTSXT4ATI1Z2X6KDIP8KD', 0),
(42, 'AF2CXTH1F1LOKDTNUEXCWQ468URQP8PY', 0),
(43, 'OA6MOBQZ56GDXKY321WOK9L5OC8TCVLU', 0),
(44, 'A78B34HLTRKZN6N6RLZ25G6A4K8KJ0LH', 0),
(45, 'G79MA6RZTFAU53JDSVR9KSEGHEW0IB3B', 0),
(46, 'W79NRBTILLGBRSIOUY43FCWOERHE8J9T', 0),
(47, '7RBRB59BOSVK7AQ3TE2EBPL1QL6YN6KV', 0);

错误情况展示

<?php
//PDO连接数据库省略
//取一条库存
$res = $pdo->query("SELECT * FROM `kami` WHERE `state` = 0 LIMIT 0,1")->fetchALL();
//更新库存已使用
$pdo->exec("UPDATE `kami` SET `state`='1' WHERE `id`={$res[0]['id']}");
//插入记录
$time = time();
$pdo->exec("INSERT INTO `log`(`kami`, `time`) VALUES ('{$res[0]['kami']}','{$time}')");

这里我手动进行一个一个访问发现没问题，如果有多个请求同时进行访问会造成什么？

使用Apache自带的ab.exe（Apache目录/bin/ab.exe）工具进行高并发请求压力测试

cmd运行ab.exe -c 20 -n 20 http://127.0.0.1/test.php -n (访问总次数) -c (并发数)

访问20次 并发数20 看看结果是什么

库存只发了14条卡密，发卡记录则存在很多重复记录。

解决高并发问题

开启事务 -> 取库存的时候开启行锁 -> 提交事务

$pdo->beginTransaction();

SELECT ... FOR UPDATE

$pdo->commit();

这里需要注意的是主要代码就是SQL查询语句加上FOR UPDATE（悲观锁）

由于我们使用的state没有索引实际使用的表级锁并不是行锁

<?php
//PDO连接数据库省略
//开启事务
$pdo->beginTransaction();
//取一条库存
$res = $pdo->query("SELECT * FROM `kami` WHERE `state` = 0 LIMIT 0,1 FOR UPDATE")->fetchALL();
//更新库存已使用
$pdo->exec("UPDATE `kami` SET `state`='1' WHERE `id`={$res[0]['id']}");
//插入记录
$time = time();
$pdo->exec("INSERT INTO `log`(`kami`, `time`) VALUES ('{$res[0]['kami']}','{$time}')");
//提交事务
$pdo->commit();

再来访问20次 并发数20 看看结果是什么

这下就不会存在多卖，超卖等情况了

解读

MySQL中的for update 仅适用于InnoDB（因为是只有此引擎才有行级锁），并且必须开启事务，在begin与commit之间才生效。for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以对这行读取但是不能写入或更新，只能等该事务Rollback, Commit, Lost connection…